Re: Indexes on a Select statement

"Daniel A. Morgan" <Daniel.Morgan_at_attws.com> wrote in message news:<3B65BB6C.AB529EF1_at_attws.com>...
> Ghost in the House wrote:
>
> > Hi,
> >
> > This is probably a very basic question for the gurus out there, but
> > I'm new to Oracle and its nuances so I would appreciate some help.
> > I'm trying to optimize some SQL by adding the appropriate index(es).
> > I've read through the documentation on performance tuning and it's
> > making more sense but I still have some questions.
> >
> > I'm running Oracle 8.1.5 and I've got an SQL statement which is taking
> > some time to run. It's a basic select statement on one table, no
> > joins, a where clause on two fields on the table, and an ORDER BY on
> > three fields on the table.
> >
> > eg.
> >
> > SELECT *
> > FROM DAT_TABLE
> > WHERE COL1 = 'CRITERIA 1' AND COL2 = 'CRITERIA 2'
> > ORDER BY COL3, COL4, COL5
> >
> > From what I read, both where and order by clauses make use of indexes.
> > My question is, do I create an index on COL3,COL4,COL5 or an index on
> > COL1, COL2? or both? Which one will Oracle choose during the query
> > process? Which one would make more difference? Also, I understand
> > that if I create an index to optimise the ORDER BY, the index has to
> > in the same order as the sort(ie. the leading portion), and I assume
> > that the order doesn't make a difference to WHERE clauses. But what
> > if I create an index on COL3, COL4 only. Will Oracle use it properly?
> > From my experience and explain plans, it does use it and performance
> > improves but I just want to make sure. Only because the Oracle
> > documentation mentions that queries that have ORDER BY clauses that
> > match the leading portion of an index will use the index(eg. an order
> > by clause of COL3, COL4 will use an index of COL3, COL4,COL5) but no
> > mention of queries that have ORDER BY clauses that have more columns
> > than an index(eg. an ORDER BY clause of COL3,COL4, COL5 and an index
> > of COL3,COL4)
> >
> > Any help and corrections to my understanding would be appreciated.
> > Thanks.
> >
> > BTW, due to application constraints, I believe that we have to use
> > rule-based optimisation and we can't use cost-based optimisations.
> > Also, we can't really change the SQL, only add appropriate indexes
> > +------------+------------+
> > EMAIL:GhostHouse_at_Excite.com
> > +------------+------------+
>
> Just on columns 1 and 2 or a single index with both fields in the same
> order they will be used in the WHERE clause.
>
> I may be corrected on this one but I can not see how Oracle could use an
> index for a ORDER BY as that is not done on the data in the table ... it
> is done on the result set.
>
> Daniel A. Morgan

You probably want a composite index on COL1, COL2, COL3, COL4, and COL5. That's one index comprised of 5 columns. You may also need to modify your sort to include COL's 1 and 2. So it would look like this...

create index your-index on your-table (col1, col2, col3, col4, col5);

select *
from your-table
where col1 = :col1
 and col2 = :col2
order by col1, col2, col3, col4, col5;

If you do it that way, Oracle will use the index and won't sort the result.

If your-index was just on col1 + col2, Oracle would use the index, but then it would sort the result for the order by on col3, col4, col5. That isn't a big deal if the result doesn't contain a lot of data (100,000+ rows). If you've got a lot of data being returned, create the 5 column index. If you don't add col1 and col2 to the order by, Oracle will *still* sort the result.

If it looked like this:

create index your-index on your-table (col3, col4, col5);

select *
from your-table
where col1 = :col1
 and col2 = :col2
order by col3, col4, col5;

Oracle will use the index to retrieve the data sorted, then it will apply the where clause to determine if each row is supposed to be there. If you are returning a small percentage of the table, you will waste a lot of time because Oracle will read the index table, then the data table, then throw away most of the rows. It would have been faster to do a full table scan on the data table, throwing away most of the rows, then performing a sort.

Also, the order of the where clause doesn't matter. The order of the order-by clause does. In some situations, the order of the from clause matters, but in my opinion, it makes more sense to order the statement so that it reads well instead. SQL is supposed to be a language, not assembly code. Besides, if the database is designed properly, you don't create many indexes for performance, and you don't structure queries with particular indexes in mind. Most queries hit the primary key, so there is very little need to create extra indexes.

Clear? Received on Tue Jul 31 2001 - 09:16:49 CDT