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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
