I need some help with Sql Tuning.
I have a query that returns information from many tables (let's say 5 for the example). 2 of that tables are big tables (20 million rows each one). I usually write 'where' clauses putting first the join conditions and after them, the table specific conditions, but now I'm not sure that's the best way to do it. Example:
from tab1 t1,tab2 t2,tab3 t3, tab4 t4, tab5 t5
where t1.col_a = t2.col_a
and t1.col_a = t5.col_a
and t1.col_c = t4.col_c
and t2.col_b = t3.col_b
and t2.col_c = t4.col_c
and t1.col_a = 'xxx'
and t1.col_b = 'uuuu'
and t2.col_b = 'yyyy'
and t4.col_x = 999;
Could be better to write the specific conditions (like t1.col_a = 'xxx') for the big tables and then the join conditions ?
What is the best way to write a query like that ?
If I've created an index on col_a,col_b,col_c of table_1, how must I construct the where clause so that Oracle use that index when executing the query ?
Do you know some place in the web where I can get some documentation on sql tuning (best if free :-))
I'm using Oracle 8i
Ok, that's all,
Sorry if my english is not so good.
Thanks in advance.
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!