Re: Indexes on a Select statement

"Ghost in the House" <GhostHouse_at_excite.com> wrote in message news:55kdktgkkkr7tod6n91efgr7oq9ikc8app_at_4ax.com...
> 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
> +------------+------------+

First of all : PLEASE don't crosspost. You're really not getting a bigger audience, as most people responding monitor all three groups. Hence you're only wasting bandwith.

I would be very reluctant to create indexes on *both* col1, col2 *and* col3,col4,col5, as this -especially in RBO- increases the chance of the optimizer choosing wrong indexes, and it will force your inserts and updates to use more resources.
In your specific statement preference has to be for the where clause, as that is supposed to limit the result set, and the order by only orders the result set.
One of the reasons the Cost Based Optimizer is so fond of sorts instead of using indexes, is that usually an in memory order by uses less resources than reading an index.
Assuming your resultset is small, I would avoid indexing col3, col4, col5 as the rule based optimizer most likely *will* use them, disregarding the cost associated. Better tune your sorts.

Final note: Using rule based optimizer is not recommended,as CBO has much more functionality. Also 8.1.5 has been desupported last year. You may soon find yourself in a dead ally.

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Sat Jul 21 2001 - 16:19:16 CDT