Re: Tuning Oracle SQL

I think what you need is 5 indexes:

t1.col1
t1.col2
t2.col1
t2.col2
t1.col3

Make sure you size the indexes properly:

create index i_stuff on stuff_tbl (col1) tablespace mytblspace storage (initial 1M next 100K);

and the base table, too!

Check the Oracle Tuning books for specifics on sizing.

Use Explain Plan to benchmark the various possibilities.

enjoy!

steve chapman

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
>
> 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 Tue Dec 10 1996 - 00:00:00 CST