| Perfoemnace Tunning [message #386503] |
Sun, 15 February 2009 23:01  |
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
|
Member |
|
|
Hi To All,
Please suggest me changes in the query so that I can change total cost for the given query.Please check in the attached file for the query and the explain plan.
SELECT loanid "Loan Id",
loanno "Loan No",
to_date(next_due_dt, 'DD/MM/RRRR') "Next DueDate"
FROM lmt_loan_dtl lld,
(SELECT lrs_1.due_date AS next_due_dt, lrs_1.loanid AS rs_loanid
FROM (SELECT lrs.loanid,
lrs.due_date,
row_number() over(PARTITION BY loanid ORDER BY loanid, due_date ASC) AS row_number
FROM lmt_repay_schedule lrs
WHERE lrs.due_date >=
(Select c.effectivedate
from company c
WHERE c.companyid = lrs.companyid)
AND lrs.status <> 'X') lrs_1
WHERE row_number = 1)
WHERE lld.loanid = rs_loanid(+)
AND lld.loanid NOT IN (SELECT ltd.loanid
FROM lmt_tran_dtl ltd, lmt_tran_hdr lth
WHERE ltd.tranhdrid = lth.tranhdrid
AND lth.cifid = lld.cifid
and lth.status = 'M')
and lld.cg_loan_status not in
(fnccgid('LOANSTATUS', 'X', 5000),
fnccgid('LOANSTATUS', 'W', 5000),
fnccgid('LOANSTATUS', 'F', 5000),
fnccgid('LOANSTATUS', 'RESTCLOS', 5000))
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Perfoemnace Tunning [message #386857 is a reply to message #386503] |
Tue, 17 February 2009 03:14  |
sukhijank
Messages: 5
Registered: February 2009
|
Junior Member |
|
|
@sr_orcl
Can not make much sense with the given information; ; but you can try:
AND NOT EXISTS (
SELECT 1
FROM lmt_tran_dtl ltd, lmt_tran_hdr lth
WHERE ltd.tranhdrid = lth.tranhdrid
AND lth.cifid = lld.cifid
AND lth.status = 'M'
AND ltd.loanid = lld.loanid)
instead of
AND lld.loanid NOT IN (SELECT ltd.loanid
FROM lmt_tran_dtl ltd, lmt_tran_hdr lth
WHERE ltd.tranhdrid = lth.tranhdrid
AND lth.cifid = lld.cifid
AND lth.status = 'M')
Regards,
Naresh
|
|
|
|
|
|
| Re: Perfoemnace Tunning [message #386910 is a reply to message #386503] |
Tue, 17 February 2009 04:30 |
sukhijank
Messages: 5
Registered: February 2009
|
Junior Member |
|
|
Hi Michel,
Yes, you are right. I am aware of the nuances of NOT IN and NOT EXISTS. Both may not give same results only in the situation when inner query gives NULL values.
But, for the given scenario, where the author is already using NOT IN for excluding certain set of loanids, I presume that the inner query will not yield NULL values, otherwise the query submitted by the author would have been wrong at the first place itself.
Regards,
Naresh
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
