Optimizing SQL with Full Table Scan

From: Tom Urbanowicz <tsu_at_landacorp.com>
Date: 20 Nov 2001 15:18:56 -0800
Message-ID: <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-

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:18:56 CET