Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Indexes on a Select statement

Re: Indexes on a Select statement

From: Daniel A. Morgan <Daniel.Morgan_at_attws.com>
Date: Tue, 31 Jul 2001 11:17:17 -0700
Message-ID: <3B66F62D.54231B09@attws.com>

Will wrote:

> "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?

Clear. And you may be right about the ORDER BY though I'd like to see a confirmation from Tom Kyte or someone with knowledge of Oracle internals. But I can not agree that "most queries hit the primary key". Most queries may incorporate the primary key but that is only a starting point.

Daniel A. Morgan Received on Tue Jul 31 2001 - 13:17:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US