Re: Tuning Oracle SQL

Steve Chapman wrote:
>
> 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

I don't necessarily disagree with the proposed solution, but the first question you need to ask is : How many rows are in each table? If each table has a few hundred rows, an index won't really help much (and may in fact, slow things down during the insert phase). If on the other hand, the tables have millions of rows each, an index could be quite helpful, if you pick the right columns to index upon, AND your select statement does not avoid use of the index because of something that you may be doing in your where clause. I think that a trip to your DBA's office might be useful so that TOGETHER you can devise something that will help you and not provide an adverse affect to the rest of the ORACLE user community.
Jerry Received on Wed Dec 11 1996 - 00:00:00 CST