Re: tuning sql query to use index

From: <G.Plivna_at_itsystems.lv>
Date: Wed, 11 Jul 2001 01:09:20 -0700
Message-ID: <F001.003469A0.20010711012120@fatcity.com>

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_at_strandba        To:     Multiple recipients of list 
ORACLE-L <ORACLE-L_at_fatcity.com>       
                    gs.com.au>                    cc:                                  
                                     
                    Sent by:                      Subject:     tuning sql query to use 
index                                
                    root_at_fatcity.com                                                   
                                     
                                                                                       
                                     
                                                                                       
                                     
                    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: 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).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: G.Plivna_at_itsystems.lv

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 - 03:09:20 CDT