| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Tuning Oracle SQL
c820098_at_hp_g50.flexcab.itg.telecom.com.au (Matthew James) wrote:
>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
How about seperate indexes on t_1.col1, t_1.col2, t_2.col_1, t_2.col_2 and t_1.col3? The problem with composite indexes is that Oracle only uses them to advantage if you ask for the columns in the same order. So unless they are there for another reason (i.e., ensuring uniqueness) seperate indexes are probably more useful.
>I guess what I'm asking is:
>- should indexes only go on where conditions or do they have effect for
>order by as well ?
They have an effect on both.
>- 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 ?
What version of Oracle are you running on? The most recent versions use a COST based (as opposed to RULE based) optimizer. As a result, if your statistics are out of date the optimizer won't always do what you expect. (Note that the optimizer may decide to do a FULL scan anyway even with statistics if it thinks that would be faster).
Bruce Armstrong [TeamPS]
Source Consulting
barmstro_at_mail.gte.net
http://home1.gte.net/barmstro/
Francis of Assisi
 |
 |