Indexes on a Select statement

From: Ghost in the House <GhostHouse_at_excite.com>
Date: Sat, 21 Jul 2001 21:19:15 GMT
Message-ID: <55kdktgkkkr7tod6n91efgr7oq9ikc8app@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
+------------+------------+
Received on Sat Jul 21 2001 - 16:19:15 CDT