RE: Execution Plan Qs. of SQL Script ?

Kevin wrote - "It is no surprise that the index you mention cannot be used"

Qs. WHY ?
( Why is Index IDX_TAX_DED_AT_SOURCE_TABLE on the Table in the Main Query NOT being used
in the Absence of the Index on the Table of the Sub-Query ? )

Or am i missing the Obvious ?

> -----Original Message-----
> From: Kevin.Little_at_blueshieldca.com [SMTP:Kevin.Little_at_blueshieldca.com]
> Sent: Tuesday, March 27, 2001 3:59 AM
> To: VIVEK_SHARMA_at_infy.com; ORACLE-L_at_fatcity.com; oracledba_at_lazydba.com
> Subject: RE: Execution Plan Qs. of SQL Script ?
>
>
> > > CASE 1 - Following query Going for FULL table scan of
> > > TAX_DED_AT_SOURCE_TABLE(Synonym=TDS) Table inspite of the presence of
> > the
> > > index IDX_TAX_DED_AT_SOURCE_TABLE defined on Fileds(sol_id, cust_id,
> > > tds_cert_num, tran_date)
> > >
> > >
> > > SELECT SOL_ID,CUST_ID FROM TDS
> > > WHERE TDS.SOL_ID IN ( SELECT SOL_ID FROM SOL WHERE SOL.BR_CODE =
> > '033'
> > > )
> > > AND TDS.CUST_ID = '033000013'
> > > AND TDS.TRAN_DATE >= TO_DATE( '01-04-2000 00:00:00' , 'DD-MM-YYYY
> > > HH24:MI:SS' )
> >
> It is no surprise that the index you mention cannot be used.
>
> 1) You need to think of the subquery as a seperate query, and
> produce an index that will optimize the subquery. That is why the index
> on
> SOL.BR_CODE works well.
>
> 2) Your index is otherwise overspecified in relation to your WHERE
> clause... your query does not include the index column TDS_CERT_NUM and
> any
> following column, so you are unable to use the TRAN_DATE component of the
> index as well. You could either reverse the order of the 3rd and 4th
> column
> (which may impact some other queries' performance... check on v$sqlarea
> for
> similar queries), or create another index which just has the 3 columns you
> are referencing.
>
> Kevin Little

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: VIVEK_SHARMA
  INET: VIVEK_SHARMA_at_infy.com

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