Re: Indexes on a Select statement

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 Received on Mon Jul 30 2001 - 21:54:20 CEST