tuning sql query to use index

From: Suhen Pather <Suhen.Pather_at_strandbags.com.au>
Date: Wed, 11 Jul 2001 00:25:35 -0700
Message-ID: <F001.003468C5.20010710234543@fatcity.com>

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: Suhen.Pather_at_strandbags.com.au

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: ListGuru_at_fatcity.com (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).

Received on Wed Jul 11 2001 - 02:25:35 CDT