Re: Optimizing SQL with Full Table Scan

Hi

You can try to change your IN clause for a EXIST or NOT EXIST clause it could be more efficient.

its a good idea to try PL/SQL too

hth

Chris

tsu_at_landacorp.com (Tom Urbanowicz) wrote in message news:<6d63e629.0111201518.4b6f4028_at_posting.google.com>...
> The following UPDATE statement affects one of our largest tables with
> a few million records. As it stands, this SQL can take 8 hours to
> execute. Can anyone recommend how to optimize this? Is there a better
> approach with PL/SQL?. . .
>
> Much thanks-
> Tom
>
> The statement is:
> --------------------
> UPDATE PEV_INDIV_VISIT
> SET PEV_VISIT_30_YN = 'Y', PEV_VISIT_DAYS = 0
> WHERE PEV_ID IN (
> SELECT
> DISTINCT PEV_A.PEV_ID
> FROM
> PEV_INDIV_VISIT PEV_A ,
> PEV_INDIV_VISIT PEV_B ,
> PCL_INDIV_CLASS PCL_INDIV_CLASS_A ,
> PCL_INDIV_CLASS PCL_INDIV_CLASS_B
> WHERE
> ( PEV_A.PEV_PMI_ID = PEV_B.PEV_PMI_ID )
> AND ( PEV_A.PEV_DATE_ENTER >= PEV_B.PEV_DATE_EXIT )
> AND ( PEV_A.PEV_DATE_ENTER - PEV_B.PEV_DATE_EXIT = 0 )
> AND ( PEV_A.PEV_PCL_CODE = PCL_INDIV_CLASS_A.PCL_CODE )
> AND ( PEV_B.PEV_PCL_CODE = PCL_INDIV_CLASS_B.PCL_CODE )
> AND ( PCL_INDIV_CLASS_A.PCL_EPISODE_TYPE = 'INP' )
> AND ( PCL_INDIV_CLASS_B.PCL_EPISODE_TYPE = 'INP')
> AND ( PEV_A.PEV_DATE_ENTER >= (SELECT MAX (PEV_C.PEV_DATE_EXIT)
> FROM PEV_INDIV_VISIT PEV_C
> WHERE PEV_C.PEV_PMI_ID = PEV_A.PEV_PMI_ID
> AND PEV_C.PEV_DATE_EXIT < PEV_A.PEV_DATE_ENTER
> )
> )
> )
> ;
>
> Explain Plan is:
> --------------------
> 1 UPDATE STATEMENT optimizer=RULE
> 2 UPDATE PEV_INDIV_VISIT
> 3 NESTED LOOPS
> 4 VIEW VW_NSO_1
> 5 SORT UNIQUE
> 6 FILTER
> 7 NESTED LOOPS
> 8 NESTED LOOPS
> 9 NESTED LOOPS
> 10 TABLE ACCESS FULL PEV_INDIV_VISIT
> 10 TABLE ACCESS BY INDEX ROWID PCL_INDIV_CLASS
> 11 INDEX UNIQUE SCAN PCL_PRIMARY_KEY
> 9 TABLE ACCESS BY INDEX ROWID PEV_INDIV_VISIT
> 10 INDEX RANGE SCAN PEV_INDEX_01
> 8 TABLE ACCESS BY INDEX ROWID PCL_INDIV_CLASS
> 9 INDEX UNIQUE SCAN PCL_PRIMARY_KEY
> 7 SORT AGGREGATE
> 8 TABLE ACCESS BY INDEX ROWID PEV_INDIV_VISIT
> 9 INDEX RANGE SCAN PEV_INDEX_01
> 4 INDEX UNIQUE SCAN PEV_PRIMARY_KEY
Received on Thu Nov 22 2001 - 22:10:45 CET