Home » SQL & PL/SQL » SQL & PL/SQL » Help needed in query tuning
Help needed in query tuning [message #209825] Mon, 18 December 2006 00:48 Go to next message
sibgat
Messages: 45
Registered: November 2006
Location: Chennai
Member

Hi,
While i was tuning the below query, it showed me three full table scans, which i was able to dropped by using the existing index which was a varchar2 type, my question is can we use the varchar2 type index like i have used
SS1.XSS_ID > 0 AND SD1.XSS_ID > 0 AND SG1.XSG_ID > 0 in the where clause, and all the full table scans were dropped, it is correct or do i need to used index only on numeric fields. pls clarify.
Here with i am pasting my old query and explain plan and new query and latest explain plan.

SELECT 
 ST1.XSF_ID,
 ST1.SNM,
 ST1.PLN,
 SS1.XSS_ID,
 SS1.XSS_NM,
 SS1.ALN,
 SG1.XSG_ID,
 SG1.XSG_DS,
 SG1.SLN,
 SD1.XSD_ID,
 SD1.XSD_DS,
 SD1.RLN
 FROM (
 SELECT
 ST.XSF_ID,
 ST.XSF_NM SNM,
 XT.XSF_NM PLN
 FROM
 NCRSTV01 ST
 INNER JOIN
 NCRXTV01 XT
 ON ST.XSF_ID = XT.XSF_ID
 AND XT.LNG_ID = 'EN' GROUP BY ST.XSF_ID,ST.XSF_NM,XT.XSF_NM)
 ST1 LEFT OUTER JOIN (
 SELECT 
 SS.XSF_ID,
 SS.XSS_ID,
 SS.XSS_NM,
 XS.XSS_NM ALN
 FROM NCRSSV01 SS
 INNER JOIN NCRXSV01 XS
 ON SS.XSF_ID = XS.XSF_ID
 AND SS.XSS_ID = XS.XSS_ID
 AND XS.LNG_ID = 'EN'
 AND SS.XSF_ID = XS.XSF_ID GROUP BY SS.XSF_ID,SS.XSS_ID,SS.XSS_NM,XS.XSS_NM) SS1
 ON ST1.XSF_ID=SS1.XSF_ID 
 LEFT OUTER JOIN (
 SELECT
 SG.XSF_ID,
 SG.XSS_ID,
 SG.XSG_ID,
 SG.XSG_DS,
 XG.XSG_DS SLN
 FROM NCRSGV01 SG
 INNER JOIN NCRXGV01 XG
 ON SG.XSF_ID = XG.XSF_ID
 AND SG.XSS_ID = XG.XSS_ID
 AND SG.XSG_ID = XG.XSG_ID
 AND XG.LNG_ID = 'EN'
 AND SG.XSF_ID = XG.XSF_ID
 GROUP BY SG.XSF_ID,SG.XSS_ID,SG.XSG_ID,SG.XSG_DS,XG.XSG_DS
 ) SG1 ON 
 ST1.XSF_ID = SG1.XSF_ID
 AND SS1.XSS_ID = SG1.XSS_ID
 LEFT OUTER JOIN (
 SELECT
 SD.XSF_ID,
 SD.XSS_ID,
 SD.XSG_ID,
 SD.XSD_ID,
 SD.XSD_DS,
 XD.XSD_DS RLN
 FROM NCRSDV01 SD
 INNER JOIN NCRXDV01 XD
 ON SD.XSF_ID=XD.XSF_ID
 AND SD.XSS_ID = XD.XSS_ID
 AND SD.XSG_ID = XD.XSG_ID
 AND SD.XSD_ID = XD.XSD_ID
 AND XD.LNG_ID = 'EN'
 AND SD.XSF_ID = XD.XSF_ID
 GROUP BY SD.XSF_ID,SD.XSS_ID,SD.XSG_ID,SD.XSD_ID,SD.XSD_DS,XD.XSD_DS
 ) SD1 ON ST1.XSF_ID = SD1.XSF_ID
 AND SS1.XSS_ID = SD1.XSS_ID
 AND SG1.XSG_ID = SD1.XSG_ID
 WHERE  
 ST1.XSF_ID = '1'
 GROUP BY ST1.XSF_ID,ST1.SNM,ST1.PLN,SS1.XSS_ID,SS1.XSS_NM,SS1.ALN,SG1.XSG_ID,SG1.XSG_DS,SG1.SLN,SD1.XSD_ID,SD1.XSD_DS,SD1.RLN


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

SELECT STATEMENT Optimizer Mode=ALL_ROWS		1  	 	23  	 	      	             	 
  SORT GROUP BY		1  	84  	23  	 	      	             	 
    HASH JOIN OUTER		1  	84  	22  	 	      	             	 
      VIEW		1  	63  	16  	 	      	             	 
        HASH JOIN OUTER		1  	126  	16  	 	      	             	 
          VIEW		1  	76  	10  	 	      	             	 
            HASH JOIN OUTER		1  	74  	10  	 	      	             	 
              VIEW		1  	36  	2  	 	      	             	 
                SORT GROUP BY		1  	25  	2  	 	      	             	 
                  NESTED LOOPS		1  	25  	2  	 	      	             	 
                    TABLE ACCESS BY INDEX ROWID	NCCOMCODE.NCRXTT01	1  	13  	1  	 	      	             	 
                      INDEX UNIQUE SCAN	NCCOMCODE.NCNXTT01	1  	 	0  	 	      	             	 
                    TABLE ACCESS BY INDEX ROWID	NCCOMCODE.NCRSTT01	1  	12  	1  	 	      	             	 
                      INDEX UNIQUE SCAN	NCCOMCODE.NCNSTT01	1  	 	0  	 	      	             	 
              VIEW		8  	304  	7  	 	      	             	 
                SORT GROUP BY		8  	216  	7  	 	      	             	 
                  MERGE JOIN		8  	216  	6  	 	      	             	 
                    TABLE ACCESS BY INDEX ROWID	NCCOMCODE.NCRSST01	16  	240  	2  	 	      	             	 
                      INDEX FULL SCAN	NCCOMCODE.NCNSST01	16  	 	1  	 	      	             	 
                    SORT JOIN		8  	96  	4  	 	      	             	 
                      TABLE ACCESS FULL	NCCOMCODE.NCRXST01	8  	96  	3  	 	      	             	 
          VIEW		8  	400  	6  	 	      	             	 
            SORT GROUP BY		8  	256  	6  	 	      	             	 
              HASH JOIN		8  	256  	5  	 	      	             	 
                TABLE ACCESS FULL	NCCOMCODE.NCRXGT01	8  	112  	3  	 	      	             	 
                INDEX FULL SCAN	NCCOMCODE.NCNSGT01	19  	342  	1  	 	      	             	 
      VIEW		7  	147  	6  	 	      	             	 
        SORT GROUP BY		7  	224  	6  	 	      	             	 
          HASH JOIN		7  	224  	5  	 	      	             	 
            TABLE ACCESS FULL	NCCOMCODE.NCRXDT01	8  	144  	3  	 	      	             	 
            INDEX FULL SCAN	NCCOMCODE.NCNSDT01	18  	252  	1



now changed query as i mentioned above

SELECT 
 ST1.XSF_ID,
 ST1.SNM,
 ST1.PLN,
 SS1.XSS_ID,
 SS1.XSS_NM,
 SS1.ALN,
 SG1.XSG_ID,
 SG1.XSG_DS,
 SG1.SLN,
 SD1.XSD_ID,
 SD1.XSD_DS,
 SD1.RLN
 FROM (
 SELECT
 ST.XSF_ID,
 ST.XSF_NM SNM,
 XT.XSF_NM PLN
 FROM
 NCRSTV01 ST
 INNER JOIN
 NCRXTV01 XT
 ON ST.XSF_ID = XT.XSF_ID
 AND XT.LNG_ID = 'EN' GROUP BY ST.XSF_ID,ST.XSF_NM,XT.XSF_NM)
 ST1 LEFT OUTER JOIN (
 SELECT 
 SS.XSF_ID,
 SS.XSS_ID,
 SS.XSS_NM,
 XS.XSS_NM ALN
 FROM NCRSSV01 SS
 INNER JOIN NCRXSV01 XS
 ON SS.XSF_ID = XS.XSF_ID
 AND SS.XSS_ID = XS.XSS_ID
 AND XS.LNG_ID = 'EN'
 AND SS.XSF_ID = XS.XSF_ID GROUP BY SS.XSF_ID,SS.XSS_ID,SS.XSS_NM,XS.XSS_NM) SS1
 ON ST1.XSF_ID=SS1.XSF_ID 
 LEFT OUTER JOIN (
 SELECT
 SG.XSF_ID,
 SG.XSS_ID,
 SG.XSG_ID,
 SG.XSG_DS,
 XG.XSG_DS SLN
 FROM NCRSGV01 SG
 INNER JOIN NCRXGV01 XG
 ON SG.XSF_ID = XG.XSF_ID
 AND SG.XSS_ID = XG.XSS_ID
 AND SG.XSG_ID = XG.XSG_ID
 AND XG.LNG_ID = 'EN'
 AND SG.XSF_ID = XG.XSF_ID
 GROUP BY SG.XSF_ID,SG.XSS_ID,SG.XSG_ID,SG.XSG_DS,XG.XSG_DS
 ) SG1 ON 
 ST1.XSF_ID = SG1.XSF_ID
 AND SS1.XSS_ID = SG1.XSS_ID
 LEFT OUTER JOIN (
 SELECT
 SD.XSF_ID,
 SD.XSS_ID,
 SD.XSG_ID,
 SD.XSD_ID,
 SD.XSD_DS,
 XD.XSD_DS RLN
 FROM NCRSDV01 SD
 INNER JOIN NCRXDV01 XD
 ON SD.XSF_ID=XD.XSF_ID
 AND SD.XSS_ID = XD.XSS_ID
 AND SD.XSG_ID = XD.XSG_ID
 AND SD.XSD_ID = XD.XSD_ID
 AND XD.LNG_ID = 'EN'
 AND SD.XSF_ID = XD.XSF_ID
 GROUP BY SD.XSF_ID,SD.XSS_ID,SD.XSG_ID,SD.XSD_ID,SD.XSD_DS,XD.XSD_DS
 ) SD1 ON ST1.XSF_ID = SD1.XSF_ID
 AND SS1.XSS_ID = SD1.XSS_ID
 AND SG1.XSG_ID = SD1.XSG_ID
 WHERE [b]SS1.XSS_ID > 0 AND SD1.XSS_ID > 0 AND SG1.XSG_ID > 0 [/b]AND  ST1.XSF_ID = '1'
 GROUP BY ST1.XSF_ID,ST1.SNM,ST1.PLN,SS1.XSS_ID,SS1.XSS_NM,SS1.ALN,SG1.XSG_ID,SG1.XSG_DS,SG1.SLN,SD1.XSD_ID,SD1.XSD_DS,SD1.RLN


its explain plan

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

SELECT STATEMENT Optimizer Mode=ALL_ROWS		1  	 	15  	 	      	             	 
  SORT GROUP BY		1  	84  	15  	 	      	             	 
    HASH JOIN		1  	84  	14  	 	      	             	 
      HASH JOIN		1  	63  	10  	 	      	             	 
        HASH JOIN		1  	40  	7  	 	      	             	 
          VIEW		1  	20  	2  	 	      	             	 
            SORT GROUP BY		1  	25  	2  	 	      	             	 
              NESTED LOOPS		1  	25  	2  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	NCCOMCODE.NCRXTT01	1  	13  	1  	 	      	             	 
                  INDEX UNIQUE SCAN	NCCOMCODE.NCNXTT01	1  	 	0  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	NCCOMCODE.NCRSTT01	1  	12  	1  	 	      	             	 
                  INDEX UNIQUE SCAN	NCCOMCODE.NCNSTT01	1  	 	0  	 	      	             	 
          VIEW		1  	20  	4  	 	      	             	 
            SORT GROUP BY		1  	27  	4  	 	      	             	 
              NESTED LOOPS		1  	27  	3  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	NCCOMCODE.NCRSST01	1  	15  	2  	 	      	             	 
                  INDEX RANGE SCAN	NCCOMCODE.NCNSST01	1  	 	1  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	NCCOMCODE.NCRXST01	1  	12  	1  	 	      	             	 
                  INDEX UNIQUE SCAN	NCCOMCODE.NCNXST01	1  	 	0  	 	      	             	 
        VIEW		1  	23  	3  	 	      	             	 
          SORT GROUP BY		1  	32  	3  	 	      	             	 
            NESTED LOOPS		1  	32  	2  	 	      	             	 
              INDEX RANGE SCAN	NCCOMCODE.NCNSGT01	1  	18  	1  	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	NCCOMCODE.NCRXGT01	1  	14  	1  	 	      	             	 
                INDEX UNIQUE SCAN	NCCOMCODE.NCNXGT01	1  	 	0  	 	      	             	 
      VIEW		1  	21  	3  	 	      	             	 
        SORT GROUP BY		1  	32  	3  	 	      	             	 
          NESTED LOOPS		1  	32  	2  	 	      	             	 
            INDEX RANGE SCAN	NCCOMCODE.NCNSDT01	1  	14  	1  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	NCCOMCODE.NCRXDT01	1  	18  	1  	 	      	             	 
              INDEX UNIQUE SCAN	NCCOMCODE.NCNXDT01	1  	 	0


I am running this in oracle 10g with TOAD.
Thanks in advance
Re: Help needed in query tuning [message #209871 is a reply to message #209825] Mon, 18 December 2006 04:14 Go to previous messageGo to next message
CoolBuddy
Messages: 10
Registered: December 2006
Location: India
Junior Member
What u want?
u want to know whether we can user varchar2() type index?
u want to know the condition u have specified is correct or not?
u want to know what will be faster?
or u want to know something else?
Re: Help needed in query tuning [message #209881 is a reply to message #209871] Mon, 18 December 2006 04:44 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I would want that you use 'you' instead of u...
Previous Topic: avg() function
Next Topic: copy data from old table to new table.
Goto Forum:
  


Current Time: Sun Dec 04 22:52:27 CST 2016

Total time taken to generate the page: 0.04673 seconds