Re: Optimizing SQL with Full Table Scan

You don't need the distinct clause. Using the distinct clause is causing Oracle to compare each row with every other row to make sure there are not repeats. If there are 8 million records that is a lot of unnecessary IO. (after all you have an in so who cares if there are repeats.) This might speed it up a lot.
Jim
"Tom Urbanowicz" <tsu_at_landacorp.com> 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 Wed Nov 21 2001 - 00:23:13 CET