Re: SQL Tuning

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Fri, 24 Jan 2003 13:45:24 -0800
Message-ID: <F001.0053A1AC.20030124134524@fatcity.com>

Hamid Alavi wrote:
>
> Hi List,
>
> Is anybody have any idea for better performance for the following query:
> I just change the OR to UNION ALL
> Appreciate any idea.
>
> SELECT a.evh_event_id
> FROM event_history_evh a
> WHERE
> (a.evh_event_id = 2 AND
> a.evh_created_date =
> (SELECT max( b.evh_created_date )
> FROM event_history_evh b
> WHERE b.evh_session_id = 1785619526 AND
> b.evh_task_list_id = a.evh_task_list_id AND
> (sysdate - b.evh_created_date )*1440 < 5 AND
> b.evh_task_list_id != 469602))
> OR-------UNIN ALL
> (a.evh_event_id = 2 AND
> a.evh_created_date =
> (SELECT max( c.evh_created_date )
> FROM event_history_evh c
> WHERE c.evh_session_id != 1785619526 AND
> c.evh_task_list_id = a.evh_task_list_id AND
> (sysdate - c.evh_created_date)*1440 < 5 AND
> c.evh_task_list_id = 469602))
>
> Hamid Alavi
>
> Office : 818-737-0526
> Cell phone : 818-416-5095
>

May I hope you were given this to tune after an especially hard week and that you didn't write it yourself ?
It's beginning to be late here and I am beginning to feel sleepy, but I think that

          (A = B and C != D) or (A != B and C = D) can be simplified into

           not (A = B and C = D) which makes the question 'OR or UNION ALL' a thing of the past.

Which brings us to :

 SELECT a.evh_event_id

         FROM event_history_evh a
         WHERE
         (a.evh_event_id = 2 AND
          a.evh_created_date =
          (SELECT max( b.evh_created_date )
           FROM event_history_evh b
           WHERE b.evh_task_list_id = a.evh_task_list_id AND
           (sysdate - b.evh_created_date )*1440 < 5 AND
           not (b.evh_session_id = 1785619526 and b.evh_task_list_id =

469602))

Now that it's a bit less hairy, it looks like it returns either '2' (possibly several ones) or nothing.
Let's further our analysis, (sysdate - blahblah) looks ugly if you have an index on evh_created_date (which would help with both the max() function and the condition).

            (sysdate - b.evh_created_date) * 1440 < 5 would probably better be written as

           sysdate - 5 / 1440 < b.evh_created_date (which I personnally understand better - created more than 5 minutes ago).
What does remain ? Hmmm, your subquery is correlated, not too good if evh_event_id is not very discriminant.
  What about :

 SELECT a.evh_event_id
 FROM event_history_evh a
 WHERE a.evh_event_id = 2 AND

       (a.evh_task_list_id, a.evh_created_date) in (SELECT b.evh_task_list,

                                                            max(
b.evh_created_date )
                                                    FROM
event_history_evh b
                                                    WHERE sysdate - 5 /
1440 < b.evh_created_date
                                                      AND not
(b.evh_session_id = 1785619526
                                                               and
b.evh_task_list_id = 469602)
                                                    group by

b.evh_task_list)
 ?

Either this or the correlated subquery,depending on volumes.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Jan 24 2003 - 15:45:24 CST