(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.