| rewrite sql query [message #609596] |
Mon, 10 March 2014 03:47  |
mohan1760
Messages: 59
Registered: June 2008
|
Member |
|
|
Hi,
How can i re-write the below query in order to reduce its cost.
SELECT *
FROM A
WHERE cod_acct_no = var_l_cod_acct_no
AND cod_dep_no in (var_l_cod_dep_no,0)
AND ctr_inst_no = (SELECT max(ctr_inst_no)
FROM A
WHERE cod_acct_no = var_l_cod_acct_no
AND cod_dep_no in (var_l_cod_dep_no, 0)
AND cod_payout_type = P
AND flg_mnt_status = A
)
AND cod_payout_type = P
AND flg_mnt_status = A;
|
|
|
|
| Re: rewrite sql query [message #609598 is a reply to message #609596] |
Mon, 10 March 2014 03:50  |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Since you didn't give enough information for a more qualified answer:
Add a "and 1=2" at the end. That will definitely bring the cost down.
|
|
|
|
|
|
|
|
| Re: rewrite sql query [message #609892 is a reply to message #609596] |
Thu, 13 March 2014 13:00 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Your query is quite inefficient. You should use analytic DENSE_RANK, not subquery to get MAX:
WITH T AS (
SELECT A.*,
dense_rank() over(order by ctr_inst_no desc) rnk
FROM A
WHERE cod_acct_no = var_l_cod_acct_no
AND cod_dep_no in (var_l_cod_dep_no, 0)
AND cod_payout_type = P
AND flg_mnt_status = A
)
SELECT *
FROM T
WHERE rnk = 1
/
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
