Home » RDBMS Server » Performance Tuning » Finding it impossible to tune this query
Finding it impossible to tune this query [message #152054] Tue, 20 December 2005 14:09 Go to next message
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
Re: Finding it impossible to tune this query [message #152056 is a reply to message #152054] Tue, 20 December 2005 14:22 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
This is a joke, right?
Re: Finding it impossible to tune this query [message #152057 is a reply to message #152054] Tue, 20 December 2005 14:37 Go to previous messageGo to next message
hututu
Messages: 2
Registered: December 2005
Junior Member
It is not a joke.
Please help me find a solution

Thx
Re: Finding it impossible to tune this query [message #152101 is a reply to message #152054] Wed, 21 December 2005 02:05 Go to previous message
ChrisJr
Messages: 17
Registered: August 2005
Junior Member
The solution is to read the "Performance Tuning Sticky"
Previous Topic: Solaris vs. Windows performance difference
Next Topic: ORA-00604: error occurred at recursive SQL level 1 ORA-04031
Goto Forum:
  


Current Time: Tue Dec 06 21:39:56 CST 2022