If You have indexed 5 columns in such an order
- SLS_PRD_CHILD
- SLS_ORG_CHILD
- SLS_YEAR
- SLS_PERIOD
- SLS_WEEK
Then you may use this index if where clause contains all 5 columns or first
4 or first 3 or first 2 or the very first column.
You cannot skip any column and use this index
You haven't SLS_ORG_CHILD column in where clause of your select statement
and thats the reason. And it seems there is no effect to use only the very
first column to filter out data in this select.
List correct me, if I'm wrong.
Gints
Suhen Pather
<Suhen.Pather@strandba To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
gs.com.au> cc:
Sent by: Subject: tuning sql query to use
index
[EMAIL PROTECTED]
2001.07.11 09:45
Please respond to
ORACLE-L
List,
I am tuning a sql query, where I would like to force the optimizer
to use the index.
However not all the columns in the where clause are indexed. (all but 1)
eg.
query
SELECT SLS_ORG_CHILD,SLS_PRD_CHILD,SLS_AMOUNT AMOUNT,SLS_UNITS UNITS
FROM SLSTH3EE
WHERE SLS_PRD_CHILD = :b1
AND SLS_YEAR = :b1
AND SLS_PERIOD = :b2
AND SLS_WEEK = :b3
/
Index in order of creation
SLSTH3EEP1 columns
- SLS_PRD_CHILD
- SLS_ORG_CHILD
- SLS_YEAR
- SLS_PERIOD
- SLS_WEEK
As you can see the column SLS_ORG_CHILD is not referenced in the where
clause.
I have used an INDEX HINT to force the optimizer to use the index however
the optimizer still favours a Full Table Scan.
There above query should account for about 5% of the total rows from the
SLSTH3EE table.
The execution plan for the above query.
Query Plan
-----------------------------------------------------------
SELECT STATEMENT [CHOOSE] Cost=5429 Rows=13 Bytes=247
TABLE ACCESS FULL SLSTH3EE [ANALYZED]
Is there a way around this in Oracle 8.1.6 without creating another index
which will just use more IO during insert and update.
Thanks and Regards
Suhen
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Suhen Pather
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
