| Finding it impossible to tune this query [message #152054] |
Tue, 20 December 2005 14:09  |
hututu
Messages: 2
Registered: December 2005
|
Junior Member |
|
|
Can you guys help me tune this query:
SELECT distinct statements.accountNumber,
statements.billDate,
statements.dueDate,
statements.amountDue,
decode(nvl(chks.payment_id,0),0,0,1) as isCheck,
decode(nvl(ccards.payment_id,0),0,0,1) as isCCard
FROM (
SELECT accountNumber, billId,billDate,dueDate, amountDue FROM (
SELECT accts.name as accountNumber,
z_doc_id as billId,
to_date("StatementDate",'MM/dd/yy') as billDate,
"DueDate" as dueDate,
max(to_date("StatementDate",'MM/dd/yy')) OVER (partition by accts.name ) as maxdate ,
row_number() OVER (partition by accts.name,"StatementDate" order by z_doc_date desc, z_ivn desc ) as daterow ,
"TotalAmtDueAmt" as amountDue
FROM (SELECT distinct a.name, a.description
FROM EDX_HIER_HNODE n1
JOIN EDX_BSL_AMF_BACCOUNT a
ON a.name=n1.linktargetid
JOIN (SELECT HN.PATH tp
FROM EDX_HIER_HNODE HN, EDX_HIER_NODE_USER NU,
EDX_HIER_USER_REF NR, EDX_HIER_HIERARCHY H ,
EDX_HIER_HTYPE t
WHERE HN.DELETEDAT=0 AND HN.NODEID = NU.NODEID
AND NU.USERID = NR.ID AND NR.EXTERNALID = :1
and t.code in ('BILLING','BUSINESS')
and h.htype = t.id
and hn.hierarchyid = h.id
UNION
SELECT HN.PATH||'.%'
FROM EDX_HIER_HNODE HN, EDX_HIER_NODE_USER NU,
EDX_HIER_USER_REF NR, EDX_HIER_HIERARCHY H ,
EDX_HIER_HTYPE t
WHERE HN.DELETEDAT=0 AND HN.NODEID = NU.NODEID
AND NU.USERID = NR.ID AND NR.EXTERNALID = :2
AND t.code in ('BILLING','BUSINESS')
AND h.htype = t.id
AND hn.hierarchyid = h.id
) pathlist
ON N1.path like pathlist.tp
WHERE n1.deletedAt=0
AND n1.linktargettype = 'ACCOUNT') accts
LEFT OUTER JOIN I_10_V a
ON accts.name = a.z_primary
and z_context is null
AND Z_DOC_DATE > add_months(sysdate,-1*:3)
LEFT OUTER JOIN DDN_VOLUMES ddns
ON ddns.ddn_vol_num = a.z_ivn
and ddns.date_accepted is not null
and ddns.date_rejected is null
and ddns.date_expired is null
)
WHERE billDate = maxdate and daterow = 1
) statements
LEFT OUTER JOIN CHECK_PAYMENTS chks
ON statements.billId = chks.bill_id
and chks.status in (8,6)
LEFT OUTER JOIN CREDITCARD_PAYMENTS ccards
ON statements.billId = ccards.bill_id
and ccards.status in (8,6)
Thx
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
