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).
