We are executing 2 PL/SQL statements on our Oracle 10g server and we are getting some interesting results. The first piece of SQL takes over 1 minute to run, the second piece of SQL takes about 0.1 seconds. We have repeated this on different days to ensure it's not database usage, the only difference is the the order of the table listed in the FROM statement.
select sum(T69330.STOCK_QTY) as c1,
T13269.COMP_DIV as c2,
T13269.STY_RET_TYPE as c3
DW_TIME T14149 ,DW_PRODUCTS T13269 ,DW_DAILY_STOCK_AND_MSS T69330
where ( T13269.PKID = T69330.PRODUCT_ID
and T13269.COMP_DIV = 'CARAVANNING'
and T14149.CAL_DATE = TO_DATE('2012-03-19' , 'YYYY-MM-DD')
and T14149.PKID = T69330.TIME_ID )
group by T13269.COMP_DIV, T13269.STY_RET_TYPE
select sum(a.STOCK_QTY) as c1,
p.COMP_DIV as c2 ,
p.STY_RET_TYPE as c3
from dw_daily_stock_and_mss a, dw_time t, dw_products p
where ( p.pkid = a.product_id
and p.COMP_DIV = 'CARAVANNING'
and T.CAL_DATE = TO_DATE('2012-03-19' , 'YYYY-MM-DD')
and t.pkid = a.time_id )
group by p.COMP_DIV , p.STY_RET_TYPE
The reason that there are some dodgy alias names in the first example is that the SQL is being generated by a rather large blue chip BI solution. This was taking too long to run so we examined it and then tested it to find the bottleneck. This was how we discovered that moving the FROM statement around speeded up the query.
My question therefore is why?
Any help greatly appreciated.