Re: Tuning Oracle SQL

The order of your tables also affect performancee, especially if you have some very large ones (we do - ie. 3 million+ rows ). The larger tables should be listed first in the from and smaller ones last. Basically Oracle works backwards in the from clause and forwards in the where so always put you're most restrictive where constraints first. This will not necessarily get rid of the table scan in this case but is good practice. You should not need the index on col3 but will probably need a browse index on t2 containing col1 and col2. Make sure your browse indexes contain the most often used columns first. For example, a query which only constrains col1 could still use this index as long as col1 is the first column.

hth cdaoust_at_ns.net

Steve Chapman <schapman_at_interpath.com> wrote in article <32ADA4C7.5D4A_at_interpath.com>...
: 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 Fri Dec 13 1996 - 00:00:00 CST