Hi
In general below is the Query processing order in oracle.
FROM -> CONNECT BY -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
Kindly clarify my queries.
In the below query. I need to fetch data for a particular batch_nbr. batch_nbr is a primary column in t2 so at most it results 1 record per batch nbr. T2 has 1 million records.
My Question are
1. Are statements 1 & 2 gives same result always ?
2. As per the query processing order join operation happens first, if so Queries 1 & 2 take 1 million records to join with table 1 and do the filter afterwards.
3. If above is true, Query 3 gives good performance as inline view executes first and returns only 1 record.
4. Can I improve Query 2 performance by moving where condition filter expression to join clause?
select * from t1, t2 where t1.col = t2.col and t2.batch_nbr = 1;
select * from t1 inner join t2 on (t1.col = t2.col) where t2.batch_nbr = 1;
select * from t1, (select * from t2 where batch_nbr = 1) tmp where t1.col = tmp.col;
Database Version: 10g & Above
Thanks for your response