Home » RDBMS Server » Performance Tuning » How to better write this query to improved performance
How to better write this query to improved performance [message #128615] Tue, 19 July 2005 16:02 Go to next message
sumang24
Messages: 10
Registered: July 2005
Junior Member
I need some inputs on how to better write this query to improve performance.

select count(*)
 as nCount from A , 
 	 B , 
	 C
WHERE A.COL1 = B.COL1 AND
 	A.COl2 <> 'COM' AND
 	B.COL2 = C.COL1 AND
 	B.COl3 IS NULL AND
 	B.COL4 = 'TEST'


This is the query plan:


Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	51  	 	      	             	 
  SORT AGGREGATE		1  	37  	 	 	      	             	 
    HASH JOIN		48 K	1 M	51  	 	      	             	 
      TABLE ACCESS FULL	A 68 K	998 K	32  	 	      	             	 
      NESTED LOOPS		98 K	2 M	5  	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	B	142 K	2 M	4  	 	      	             	 
          INDEX SKIP SCAN	XIF37B	142 K	 	6  	 	      	             	 
        INDEX UNIQUE SCAN	XPKC	1  	5  

Re: How to better write this query to improved performance [message #130107 is a reply to message #128615] Thu, 28 July 2005 03:41 Go to previous message
oraclejo
Messages: 50
Registered: July 2005
Location: Ammar
Member
hello

if the sizes of the TABLES are large

find yourself a way to get rid of the Nestep Loop operation in favour of another HASH join.

Speed up the Hash join by allocating more Hash_area_size.

Ammar Sajdi

www.e-ammar.com

In my website, inside the download section, you will find a good tuning document

Previous Topic: monitoring the instance specialy shared pool
Next Topic: Regarding the sizing of disk for a database
Goto Forum:
  


Current Time: Fri May 27 17:06:39 CDT 2022