Home » RDBMS Server » Performance Tuning » Re: Get Unique row number for list of values (split from unrelated thread by bb)
Re: Get Unique row number for list of values (split from unrelated thread by bb) [message #563827] Fri, 17 August 2012 06:23
prashanth7582
Messages: 34
Registered: October 2005
Location: Bangalore
Member
The Cost of Nested loops and Window sort in below query is quite high.

SELECT seq,CCN,ProcessorPart,root_item,comp_path,Item,comp_item,comp_item_type,
lag(comp_item_type,1,'PART') over(PARTITION BY seq ORDER BY lvl)Nxt_comp_item_type,lvl,bom_qty,
        ROUND(CASE min(abs(bom_qty)) OVER (PARTITION BY seq ORDER BY lvl)
        WHEN 0 THEN 0 ELSE 1 END * EXP (SUM (LN (nullif(abs(bom_qty),0))) OVER (PARTITION BY seq ORDER BY lvl))) Ulti_qty,
        'AMER'
        FROM
        (
        SELECT y.seq,y.CCN,y.ProcessorPart,y.root_item,y.comp_path,y.Item,y.comp_item,y.lvl,y.bom_qty,
        (
        SELECT comp_item_type
        FROM E2EC_ECAPS_BOM_TMP a
        WHERE Region= 'AMER'
        AND y.comp_item=a.comp_item
        AND a.CCN=y.CCN
        union
        SELECT item_type
        FROM E2EC_ECAPS_BOM_TMP a
        WHERE Region= 'AMER'
        AND y.comp_item=a.item
        AND a.CCN=y.CCN
        ) comp_item_type
        FROM
        (
        select Seq,CCN,root_item,root_comp_item ProcessorPart,comp_path,
        substr (t.comp_path, instr (t.comp_path, '/', -1, 2)+ 1,instr (t.comp_path, '/', -1, 1)- instr (t.comp_path, '/', -1, 2)-1) Item,
        SUBSTR(t.comp_path, instr (t.comp_path, '/', 1, x.column_value) + 1,instr (t.comp_path, '/', 1, x.column_value + 1)- instr (t.comp_path, '/', 1, x.column_value) - 1) comp_item,
        SUBSTR(t.ultimate_qty, instr (t.ultimate_qty, '*', 1, x.column_value) + 1,instr (t.ultimate_qty, '*', 1, x.column_value + 1)- instr (t.ultimate_qty, '*', 1, x.column_value) - 1) bom_qty,
        x.column_value lvl
        from ( SELECT t.root_item,t.ccn,t.seq,t.root_comp_item,'*1'||t.ultimate_qty||'*' ultimate_qty,t.comp_path||'/'||t.item||'/' comp_path
               FROM E2EC_ECAPS_MOD_DTL t
               WHERE t.Region = 'AMER') t,
               TABLE(CAST(MULTISET(select LEVEL from dual connect by level <= regexp_count (t.comp_path, '/') - 1)as sys.odcinumberlist)) x
        )y
        )


Explain Plan :

SELECT STATEMENT, GOAL = ALL_ROWS						         1007544	  60714747	3278596338
 SORT UNIQUE									         355	           4	        80
  UNION-ALL					
   PARTITION LIST SINGLE							         346	           3		60
    TABLE ACCESS FULL	ADMIN_COSTPL_OWNER	E2EC_ECAPS_BOM_TMP		         346	           3		60
   TABLE ACCESS BY GLOBAL INDEX ROWID	ADMIN_COSTPL_OWNER	E2EC_ECAPS_BOM_TMP	 7		   1		20
    INDEX RANGE SCAN	ADMIN_COSTPL_OWNER	TEST					 3		   4	 
WINDOW SORT								                 [b]1007544[/b]	60714747	3278596338
  NESTED LOOPS										 [b]201967[/b]	60714747	3278596338
   PARTITION LIST SINGLE								 68		7433		386516
    TABLE ACCESS FULL	ADMIN_COSTPL_OWNER	E2EC_ECAPS_MOD_DTL			 68		7433		386516
   COLLECTION ITERATOR SUBQUERY FETCH					
    CONNECT BY WITHOUT FILTERING					
     FAST DUAL										 2		1	


Is there a way to reduce it..

Thanks for your help in advance
Previous Topic: query recommedation
Next Topic: Same query with different Explain Plan in two Database
Goto Forum:
  


Current Time: Fri Nov 28 19:32:46 CST 2014

Total time taken to generate the page: 0.09494 seconds