re-write the query for better performance [message #620775] |
Wed, 06 August 2014 00:52 |
mohan1760
Messages: 59 Registered: June 2008
|
Member |
|
|
Hi,
How can I re-write the below query in order to reduce its cost.
SELECT Count(1)
FROM (SELECT cod_acct_no
FROM ts
WHERE cod_acct_no IN (SELECT a.cod_acct_no
FROM td a
WHERE a.cod_dep_stat IN ( 1, 5 )
GROUP BY a.cod_acct_no
HAVING Count(*) = (SELECT Count(*)
FROM td b
WHERE
a.cod_acct_no = b.cod_acct_no
GROUP BY cod_acct_no)));
[Updated on: Wed, 06 August 2014 00:59] Report message to a moderator
|
|
|
|
Re: re-write the query for better performance [message #620782 is a reply to message #620777] |
Wed, 06 August 2014 01:36 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Looks like you are after cod_acct_no in TD where all rows have cod_dep_stat of 1 or 5
SELECT COUNT(*)
FROM ts
WHERE cod_acct_no IN (
SELECT a.cod_acct_no
FROM td a
GROUP BY a.cod_acct_no
HAVING COUNT( CASE WHEN a.cod_dep_stat IN (1,5) THEN 1 ELSE NULL END ) > 0
AND COUNT( CASE WHEN a.cod_dep_stat IN (1,5) THEN NULL ELSE 1 END ) = 0
If you are guaranteed to have every cod_acct_no in TD, then the following may be functionally equivalent and probably faster
SELECT COUNT(*)
FROM ts
WHERE cod_acct_no IS NOT NULL
AND cod_acct_no NOT IN (
SELECT a.cod_acct_no
FROM td a
WHERE a.cod_acct_no IS NOT NULL
AND a.cod_dep_stat IN (1,5)
Ross Leishman
|
|
|
|
Re: re-write the query for better performance [message #620786 is a reply to message #620785] |
Wed, 06 August 2014 02:38 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Oh well, I was hoping for something in the vicinity of 45,000, but there you go </sarc>
There was typo in the 2nd query
SELECT COUNT(*)
FROM ts
WHERE cod_acct_no IS NOT NULL
AND cod_acct_no NOT IN (
SELECT a.cod_acct_no
FROM td a
WHERE a.cod_acct_no IS NOT NULL
AND a.cod_dep_stat NOT IN (1,5))
Ross Leishman
P.S. The cost number is not really very helpful, except when comparing two plans for the same SQL on the same database and the same time. When the SQL, the database, or the time of execution is different, then differences in cost can be misleading in terms of determining which one is faster. The best way to determine which one is faster is to run them, but even then you have to watch out for differences in caching. By all means, use Cost as an *indicator*, but if you trust it over your own observations then you are effectively saying that Oracle is infallible.
|
|
|
Re: re-write the query for better performance [message #620792 is a reply to message #620785] |
Wed, 06 August 2014 04:08 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
mohan1760 wrote on Wed, 06 August 2014 12:54
First query works as per my requirement with cost 46522.
Please post the execution plan of original query and the one which Ross suggested. Follow the link Michel posted above.
We don't know your tables and the indexes involved, execution plan will reveal a lot of things.
|
|
|
|