Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to rewrite inefficient correlated subquery .

698658Oct 13 2010 — edited Oct 13 2010
Hi,
I'm on 9.2.0.8 and got query like this:
SELECT   trw.id, trw.data, trw.godzina_od, trw.godzina_do,
         trw.liczba_wnioskow
    FROM  trw
      WHERE
      trw.psl_kta_id = 12791592
     AND   
          (SELECT COUNT (*) 
              FROM  zgl,  zdn
             WHERE zgl.audyt_st = '1'
               AND zgl.id = zdn.zgl_id
               AND zdn.trw_id = trw.id
            ) < trw.liczba_wnioskow
            ORDER BY trw.data, trw.godzina_od
/



Plan
SELECT STATEMENT  CHOOSECost: 38  Bytes: 240  Cardinality: 10  						
	10 SORT ORDER BY  Cost: 8  Bytes: 240  Cardinality: 10  					
		9 FILTER  				
			2 TABLE ACCESS BY INDEX ROWID TRW Cost: 2  Bytes: 240  Cardinality: 10  			
				1 INDEX RANGE SCAN NON-UNIQUE TRW_PSL_KTA_FK Cost: 1  Cardinality: 201  		
			8 SORT AGGREGATE  Bytes: 12  Cardinality: 1  			
				7 NESTED LOOPS  Cost: 3  Bytes: 12  Cardinality: 1  		
					4 TABLE ACCESS BY INDEX ROWID ZDN Cost: 2  Bytes: 7  Cardinality: 1  	
						3 INDEX FULL SCAN NON-UNIQUE ZDN_ZGL_FK Cost: 1  Cardinality: 30  
					6 TABLE ACCESS BY INDEX ROWID ZGL Cost: 1  Bytes: 5  Cardinality: 1  	
						5 INDEX UNIQUE SCAN UNIQUE ZGL_PK Cardinality: 1  
All three tables are small (TRW = 7MB , ZDN 4MB , ZGL 0,5 MB) but Oracle needs 356844 buffer gets to return 216 rows .
Any ideas how to rewrite that subquery are much appreciated .
Regards
GregG
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 10 2010
Added on Oct 13 2010
2 comments
202 views