Home » SQL & PL/SQL » SQL & PL/SQL » re-write the query for better performance (10g)
re-write the query for better performance [message #620775] Wed, 06 August 2014 00:52 Go to next message
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 #620777 is a reply to message #620775] Wed, 06 August 2014 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Re: re-write the query for better performance [message #620782 is a reply to message #620777] Wed, 06 August 2014 01:36 Go to previous messageGo to next message
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 #620785 is a reply to message #620782] Wed, 06 August 2014 02:24 Go to previous messageGo to next message
mohan1760
Messages: 59
Registered: June 2008
Member
Thanks a lot Ross..

First query works as per my requirement with cost 46522.
second query returns incorrect no. of rows.


Re: re-write the query for better performance [message #620786 is a reply to message #620785] Wed, 06 August 2014 02:38 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: re-write the query for better performance [message #620797 is a reply to message #620792] Wed, 06 August 2014 04:20 Go to previous message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Knowing the relationship between the tables would also help a lot.
Previous Topic: How to make logic
Next Topic: Several updates vs one update
Goto Forum:
  


Current Time: Thu Apr 25 21:46:07 CDT 2024