Hi,
I have a query which is using MERGE JOIN CARTESIAN (see step 4 and 11 in execution plan bellow) and i believe this is the culprit and query is never returning. Stats are up to date on all tables. This query involves two remote databases which are accessed using VIEWs from local database. Is there any hint which i can use here to avoid merge join Cartesian and test. Thanks. Also pasting query if any one can have any idea.
Select distinct
substr(Revenue.FKDAT,5,2) || '/' || substr(Revenue.FKDAT,7,2) || '/' || substr(Revenue.FKDAT,1,4) INVOICE_DATE
, Revenue.VBELN INVOICE_NR
, Revenue.ARKTX CUSTDEVICE
, (case Revenue.ZU_LOTID
when null then hstg_partinfo_cust_dw.CUSTPARTOUT
when '' then hstg_partinfo_cust_dw.CUSTPARTOUT
Else
(case decode(trim(zodss1b2.biztype),'TNR-E330-BAKE','TNR','')
when 'TNR' then
(case when (select CUSTPARTOUT from hstg_partinfo_cust_dw Where lotid = 'U' || substr(Revenue.ZU_LOTID,2,7) || '.' || substr(Revenue.ZU_LOTID,9) and stage = 'T998-TFIN' and rownum = 1)
is null then (select CUSTPARTOUT from hstg_partinfo_cust_dw Where lotid = (select parentid from hstg_partinfo_cust_dw where lotid = 'U' || substr(Revenue.ZU_LOTID,2,7) || '.' || substr(Revenue.ZU_LOTID,9) and stage = 'E330-BAKE' and rownum = 1) and stage = 'T998-TFIN' and rownum = 1)
else (select CUSTPARTOUT from hstg_partinfo_cust_dw Where lotid = 'U' || substr(Revenue.ZU_LOTID,2,7) || '.' || substr(Revenue.ZU_LOTID,9) and stage = 'T998-TFIN' and rownum = 1)
end)
else hstg_partinfo_cust_dw.CUSTPARTOUT
end)
end) CUSTPARTOUT
, Revenue.PONUM DO_NR
, Revenue.BSTKD PO_NR
, Revenue.FKIMG GD_QTY
, Revenue.zkbetr GD_PRICE
, Revenue.NETWR GD_AMT
, '' REJ_QTY
, '' REJ_PRICE
, '' REJ_AMT
, Revenue.ZU_LOTID LOTTYPE
,(case Revenue.ZU_LOTID
when null then substr(Revenue.ZU_PRTID,1,instr(Revenue.ZU_PRTID,'.') -1)
when '' then substr(Revenue.ZU_PRTID,1,instr(Revenue.ZU_PRTID,'.') -1)
Else
(case decode(trim(zodss1b2.biztype),'TNR-E330-BAKE','TNR','')
when 'TNR' then
(case when (select substr(PARTID,1,instr(PARTID,'.') -1) from hstg_partinfo_cust_dw Where lotid = 'U' || substr(Revenue.ZU_LOTID,2,7) || '.' || substr(Revenue.ZU_LOTID,9) and stage = 'T998-TFIN' and rownum = 1)
is null then (select substr(PARTID,1,instr(PARTID,'.') -1) from hstg_partinfo_cust_dw Where lotid = (select parentid from hstg_partinfo_cust_dw where lotid = 'U' || substr(Revenue.ZU_LOTID,2,7) || '.' || substr(Revenue.ZU_LOTID,9) and stage = 'E330-BAKE' and rownum = 1) and stage = 'T998-TFIN' and rownum = 1)
else (select substr(PARTID,1,instr(PARTID,'.') -1) from hstg_partinfo_cust_dw Where lotid = 'U' || substr(Revenue.ZU_LOTID,2,7) || '.' || substr(Revenue.ZU_LOTID,9) and stage = 'T998-TFIN' and rownum = 1)
end)
else substr(Revenue.ZU_PRTID,1,instr(Revenue.ZU_PRTID,'.') -1)
end)
end) PARTID
, '' TESTER
, substr(zodss1b2.BSTDK,5,2) || '/' || substr(zodss1b2.BSTDK,7,2) || '/' || substr(zodss1b2.BSTDK,1,4) DO_DATE
, hstg_partinfo_cust_dw.package package
, hstg_partinfo_cust_dw.pincount pincount
, (select c.catg07 from ops$ods_adm.catg c where c.partid=revenue.zu_prtid) as package_size
, (select c.catg09 from ops$ods_adm.catg c where c.partid=revenue.zu_prtid) as wafer_size
, decode(trim(zodss1b2.biztype),'TNR-E330-BAKE','TNR','') BIZTYPE
--from revenue, zodss1b2,dim_partinfo
from revenue, zodss1b2,hstg_partinfo_cust_dw
where Revenue.Remarks <> 'CANCELLED'
and Revenue.FKART in ('ZUF2')
and Revenue.KUNNR in ('BC1001','BC1002','BC1003')
and Revenue.FKDAT like '201008%'
and Revenue.FKIMG > 0
and Revenue.kpein > 0
and Revenue.ZU_LOTID = zodss1b2.charg
and Revenue.MATNR = zodss1b2.matnr
--and Revenue.ZU_PRTID = dim_partinfo.partid
and hstg_partinfo_cust_dw.lotid='U' || substr(Revenue.ZU_LOTID,2,7) || '.' || substr(Revenue.ZU_LOTID,9)
Union all
Select distinct
substr(Revenue.FKDAT,5,2) || '/' || substr(Revenue.FKDAT,7,2) || '/' || substr(Revenue.FKDAT,1,4) INVOICE_DATE
, Revenue.VBELN INVOICE_NR
, Revenue.ARKTX CUSTDEVICE
, hstg_partinfo_cust_dw.CUSTPARTOUT CUSTPARTOUT
, Revenue.PONUM DO_NR
, Revenue.BSTKD PO_NR
, Revenue.FKIMG GD_QTY
, Revenue.zkbetr GD_PRICE
, Revenue.NETWR GD_AMT
, '' REJ_QTY
, '' REJ_PRICE
, '' REJ_AMT
, Revenue.ZU_LOTID LOTTYPE
, substr(Revenue.ZU_PRTID,1,instr(Revenue.ZU_PRTID,'.') -1) PARTID
, '' TESTER
, substr(zodsscrap.BSTDK,5,2) || '/' || substr(zodsscrap.BSTDK,7,2) || '/' || substr(zodsscrap.BSTDK,1,4) DO_DATE
, hstg_partinfo_cust_dw.package package
, hstg_partinfo_cust_dw.pincount pincount
, (select c.catg07 from ops$ods_adm.catg c where c.partid=revenue.zu_prtid) as package_size
, (select c.catg09 from ops$ods_adm.catg c where c.partid=revenue.zu_prtid) as wafer_size
, ''
--from revenue, zodsscrap,dim_partinfo
from revenue, zodsscrap,hstg_partinfo_cust_dw
where Revenue.Remarks <> 'CANCELLED'
and Revenue.FKART in ('ZUF2')
and Revenue.KUNNR in ('BC1001','BC1002','BC1003')
and Revenue.FKDAT like '201008%'
and Revenue.FKIMG > 0
and Revenue.kpein > 0
and Revenue.ZU_LOTID = zodsscrap.charg
--and Revenue.ZU_PRTID = dim_partinfo.partid
and hstg_partinfo_cust_dw.lotid='U' || substr(Revenue.ZU_LOTID,2,7) || '.' || substr(Revenue.ZU_LOTID,9);
hstg_partinfo_cust_dw is a view which is on remote database USGDWDBP and is queried using synonym in the local database.
revenue is also a view which is in another remote database which is queried using synonym in local database.
ODS Plan
-------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Inst |IN-OUT|
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8532 | 999 | | |
| 1 | UNION-ALL | | | | | | |
| 2 | SORT UNIQUE | | 1 | 4277 | 985 | | |
| 3 | NESTED LOOPS | | 1 | 4277 | 983 | | |
| 4 | MERGE JOIN CARTESIAN| | 1 | 4107 | 980 | | |
| 5 | REMOTE | | 1 | 4076 | 2 | USGDW~ | R->S |
| 6 | BUFFER SORT | | 918K| 27M| 978 | | |
| 7 | TABLE ACCESS FULL | ZODSS1B2 | 918K| 27M| 978 | | |
| 8 | REMOTE | | 1 | 170 | 3 | SAPP0~ | R->S |
| 9 | SORT UNIQUE | | 1 | 4255 | 14 | | |
| 10 | NESTED LOOPS | | 1 | 4255 | 12 | | |
| 11 | MERGE JOIN CARTESIAN| | 1 | 4096 | 9 | | |
| 12 | REMOTE | | 1 | 4076 | 2 | USGDW~ | R->S |
| 13 | BUFFER SORT | | 1755 | 35100 | 7 | | |
| 14 | TABLE ACCESS FULL | ZODSSCRAP | 1755 | 35100 | 7 | | |
| 15 | REMOTE | | 1 | 159 | 3 | SAPP0~ | R->S |
---------------------------------------------------------------------------------------
Thanks in advance
Salman