Tuning Oracle SQL

Hi

I'm after advice on tuning of Oracle SQL statements. I have some statements of the basic form:

select <columns>
from t_1, t_2
where t_1.col1 = t_2.col1
and t_1.col2 = t_2.col2
order by t_1.col1 asc, t_1.col3 asc;

I want to try and speed this up (some currently run ~45s -> 1 min), how should I best do this:

• composite index on t_1(col1, col2) // already there
• composite index on *both* t_1(col1, col2) and t_2(col1, col2)
• composite index on t_1(col1, col3)
• some combination of the above

I guess what I'm asking is:
- should indexes only go on where conditions or do they have effect for
order by as well ?
- if I have an index on both tables will that make things quicker ? I
tried this but there is always a FULL scan for one of the tables (from explain plan output). My thinking is that Oracle should be able to run down one index and cross check with the other index and only hit the tables if there is a match - where's the catch ?

Thanks
Matt Received on Thu Dec 05 1996 - 00:00:00 CST