Home » SQL & PL/SQL » SQL & PL/SQL » Explain plan output.
Explain plan output. [message #266395] Mon, 10 September 2007 13:26 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member

(Select max(a.job_sid) as job_sid,max(c.tree_id) as tree_id,max(b.id_id) as id_id,a.id_code,
    sum(decode(a.SEAS_id_FLAG_N1,'N', a.wgt_n1, a.WGT_N1)) WGT_N1,
    sum(decode(a.SEAS_id_FLAG_N2,'N', a.wgt_n2, a.WGT_N2)) WGT_N2    
    from ixrev_id_data a, id b,
         (Select job_sid,tree_id, substr(ppi_id_code,1,6) as sub_tree_id from ixrev_id_data
                    where
                    tree_id in ( select distinct tree_id from ixrev_id_data
                                 where tree_category = '42')
                    and higher_id_sid is not null)c
          where  a.tree_id = c.sub_tree_id
          and a.JOB_SID = c.job_sid
          and a.id_sid = b.id_sid
          and b.id_id not like '%SOP%'
          group by c.tree_id,a.id_code) 


DEV ENVIRONEMNT

SELECT STATEMENT ()	[NULL]
 HASH (GROUP BY)	[NULL]
  HASH JOIN (RIGHT SEMI)	[NULL]
   TABLE ACCESS (FULL)	IXREV_ID_DATA
   HASH JOIN ()	[NULL]
    HASH JOIN ()	[NULL]
     TABLE ACCESS (FULL)	ID
     TABLE ACCESS (FULL)	IXREV_ID_DATA
    TABLE ACCESS (FULL)	IXREV_ID_DATA

TEST ENVIRONMENT.


SELECT STATEMENT ()	[NULL]
 HASH (GROUP BY)	[NULL]
  HASH JOIN (RIGHT SEMI)	[NULL]
   TABLE ACCESS (FULL)	IXREV_ID_DATA
   HASH JOIN ()	[NULL]
    TABLE ACCESS (FULL)	IXREV_ID_DATA
    HASH JOIN ()	[NULL]
     TABLE ACCESS (FULL)	ID
     TABLE ACCESS (FULL)	IXREV_ID_DATA



The above query gives two different explain plans. In test environment it is very slow. I would like to know what would give the different explain plan.
Re: Explain plan output. [message #266405 is a reply to message #266395] Mon, 10 September 2007 14:44 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Possible causes:

Are the same tables in both environments analyzed/not analyzed?
Number of rows in each environment?
Previous Topic: Help with PK constraint
Next Topic: Using DATES
Goto Forum:
  


Current Time: Wed Dec 07 16:14:21 CST 2016

Total time taken to generate the page: 0.09350 seconds