1

I am trying to build a query that gives me the hierarchical history of events for a given child event_id. The events are related to each other by some from_id and to_id and the order is determined by the event_Date_time.

I don't have the option to use CTE since my oracle version doesn't support it.

Below is the query I used.

SELECT event_id, event_Date_time
    FROM event_table
    WHERE CONNECT_BY_ISLEAF=1
    START WITH event_id IN (?)
    CONNECT BY NOCYCLE (prior to_id =from_id 
                        OR prior from_id=to_id 
                        OR prior to_id=to_id 
                        OR prior from_id=from_id ) 
                        AND prior event_date_time >event_date_time;

This query seems to give me what i want but too many redundant values. Using distinct or group by or min (level) seems to significantly slow the query.

Sample Data:

EVENT_ID FROM_ID TO_ID   EVENT_DATE_TIME"
917722  1928015 1742227 21-FEB-14 03.40.00.520107000 PM
917723  1928016 1742225 21-FEB-14 03.42.00.520107000 PM
918061  1742225 1928016 21-FEB-14 03.42.41.580594000 PM
918067  1742225 1928016 21-FEB-14 03.43.40.144774000 PM
918072  1742225 1928016 21-FEB-14 03.44.05.217756000 PM
918076  1928016 1029932 21-FEB-14 04.27.00.043562000 PM
918414  1029932 1928016 21-FEB-14 04.27.20.555416000 PM
918421  1029932 1928016 21-FEB-14 04.27.34.758173000 PM
918426  1029932 1928016 21-FEB-14 04.27.42.878832000 PM
918430  1928016 621923  21-FEB-14 05.38.45.065755000 PM
918767  621923  1928016 21-FEB-14 05.38.53.652969000 PM
918775  621923  1928016 21-FEB-14 05.38.59.369496000 PM
918780  621923  1928016 21-FEB-14 05.39.02.198472000 PM
919723  621923  1442007 24-FEB-14 05.18.59.385583000 PM
922413  1442007 621923  24-FEB-14 05.20.03.905621000 PM
922424  1442007 621923  24-FEB-14 05.21.02.184918000 PM
922430  1442007 621923  24-FEB-14 05.40.37.925462000 PM
927458  621923  1442007 24-FEB-14 05.41.40.345433000 PM
927466  621923  1442007 24-FEB-14 05.41.43.708275000 PM
929154  621923  1442007 24-FEB-14 05.42.20.224308000 PM

Given the above data and query if i search for the historical events related to event_id 922413, I am expecting to see

EVENT_ID EVENT_DATE_TIME"
917723  21-FEB-14 03.42.00.520107000 PM
918061  21-FEB-14 03.42.41.580594000 PM
918067  21-FEB-14 03.43.40.144774000 PM
918072  21-FEB-14 03.44.05.217756000 PM
918076  21-FEB-14 04.27.00.043562000 PM
918414  21-FEB-14 04.27.20.555416000 PM
918421  21-FEB-14 04.27.34.758173000 PM
918426  21-FEB-14 04.27.42.878832000 PM
918430  21-FEB-14 05.38.45.065755000 PM
918767  21-FEB-14 05.38.53.652969000 PM
918775  21-FEB-14 05.38.59.369496000 PM
918780  21-FEB-14 05.39.02.198472000 PM
919723  24-FEB-14 05.18.59.385583000 PM

I also tried the recursive call from the java code instead of using the connect by query which gave me much better performance but i had to restrict the number of recursive calls and this doesn't seem a good idea. I am wondering if there is any other way to achieve the same result with better performance.

I appreciate any suggestion.

  • Did you try?: SELECT DISTINCT * FROM (your query) d; – ypercubeᵀᴹ Oct 6 '14 at 21:36
  • Thanks for the replay ypercube. Yes I tried that. The performance will be very bad if i use distinct or group by. – Lina Oct 6 '14 at 21:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.