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).