Home » RDBMS Server » Performance Tuning » urgent .. i need help
urgent .. i need help [message #184321] Wed, 26 July 2006 02:38 Go to next message
juliee_jitu
Messages: 8
Registered: July 2006
Junior Member
plz help me to tune this query

SELECT   10000 AS parentindicator, NULL AS companyname, templatetype, seqnum,
         NAME, description, beneficiaryname, trxtype, companyid AS companyid,
         user_id, accountid, lastupdatedate
    FROM templates
   WHERE accountid IS NULL AND companyid = 20020423000007 AND templatetype IN ('2')
UNION ALL
SELECT   (SELECT COUNT (*)
            FROM company com
           WHERE a.accountid IS NOT NULL
             AND a.accountid = b.bankaccountid
             AND b.companyid = com.parentcompanyid) AS parentindicator,
         c.companyname AS companyname, templatetype, seqnum, NAME,
         description, beneficiaryname, trxtype, a.companyid AS companyid,
         user_id, accountid, lastupdatedate
    FROM templates a, bankaccount b, company c
   WHERE a.accountid IS NOT NULL
     AND a.accountid = b.bankaccountid
     AND b.companyid = c.companyid
     AND a.companyid = 20020423000007
     AND templatetype IN ('2')
ORDER BY parentindicator DESC, companyname, lastupdatedate DESC




explain plan
-----------


Explain Plan
==============
Operation				Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
-----------------------------------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=CHOOSE			3 K	 	1560  	 	      	             	 
  SORT ORDER BY						3 K	309 K	1476  	 	      	             	 
    UNION-ALL		  	 	 	 	      	             	 
      TABLE ACCESS FULL			TEMPLATES	3 K	305 K	716  	 	      	             	 
      NESTED LOOPS					22  	3 K	760  	 	      	             	 
        NESTED LOOPS					22  	2 K	738  	 	      	             	 
          TABLE ACCESS FULL		TEMPLATES	22  	2 K	716  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	BANKACCOUNT	1  	16  	1  	 	      	             	 
            INDEX UNIQUE SCAN		PK_BANKACCOUNT	1  	 	 	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	COMPANY		1  	32  	1  	 	      	             	 
          INDEX UNIQUE SCAN		PK_COMPANY	1  	 	 	 	      	             	 

 	 	      	             	 
Re: urgent .. i need help [message #184337 is a reply to message #184321] Wed, 26 July 2006 03:52 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
first, go through the steps here http://www.orafaq.com/forum/t/51267/68488/

Jim
Re: urgent .. i need help [message #184595 is a reply to message #184337] Thu, 27 July 2006 03:20 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Look at http://www.orafaq.com/forum/t/66847/89573/
Previous Topic: how to get a query into sharable pool ??
Next Topic: what is BITMAP CONVERSION TO ROWIDS in Plan?
Goto Forum:
  


Current Time: Thu Dec 08 14:26:44 CST 2016

Total time taken to generate the page: 0.21993 seconds