> Oracle ver 7.3.4.5
>
> 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' )
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 1 1.74 1.74 8062 8068 2
> 1
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 3 1.74 1.74 8062 8068 2
> 1
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: RULE
> 1 NESTED LOOPS
> 368573 TABLE ACCESS (FULL) OF 'TAX_DED_AT_SOURCE_TABLE'
> 1 TABLE ACCESS (BY ROWID) OF 'SERVICE_OUTLET_TABLE'
> 1 INDEX (UNIQUE SCAN) OF 'IDX_SERVICE_OUTLET_TABLE' (UNIQUE)
>
> NOTE SOL is the Synonym for SERVICE_OUTLET_TABLE Table which Contains
> Masterdata of about 50
> rows only
>
> Qs. 1 Why is Index Scan of TAX_DED_AT_SOURCE_TABLE Table NOT Happening ?
> Qs. 2 Am i correct in inferring from the Above That
> TAX_DED_AT_SOURCE_TABLE is the Driving
> Table because it Appears First in the Execution Plan when Reading
> Downwards ?
>
>
> CASE 2 - After Creation of Index on SERVICE_OUTLET_TABLE (SOL) table on
> field (br_code field), for the SAME WHERE Clause Statement , INDEX Scan of
> TAX_DED_AT_SOURCE_TABLE(TDS) Table is Done
>
> UPDATE TDS SET TDS.SHORTFALL_AMT = 0
> WHERE
> TDS.SOL_ID IN ( SELECT SOL_ID FROM SOL WHERE SOL.BR_CODE = :1 ) AND
> TDS.CUST_ID = :2 AND TDS.TRAN_DATE >= TO_DATE( :3 , 'DD-MM-YYYY
> HH24:MI:SS' )
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 1 0.00 0.00 0 7 1
> 1
> Fetch 0 0.00 0.00 0 0 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 2 0.00 0.00 0 7 1
> 1
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 UPDATE STATEMENT GOAL: RULE
> 1 NESTED LOOPS
> 1 TABLE ACCESS (BY ROWID) OF 'SERVICE_OUTLET_TABLE'
> 2 INDEX (RANGE SCAN) OF 'IDX_SOL_BR_CODE' (NON-UNIQUE)
> 2 INDEX (RANGE SCAN) OF 'IDX_TAX_DED_AT_SOURCE_TABLE' (UNIQUE)
>
> NOTE - Also seemingly SERVICE_OUTLET_TABLE(SOL) Table Now Becomes the
> Driving Table
>
> Qs. How does Creation of an index on The Table which is Part of the Nested
> Query Cause the Execution plan to Change as Shown ?
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: VIVEK_SHARMA
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).
