Re: Working with huge tables of chronological data

From: John <acide.ascorbique_at_gmail.com>
Date: 11 Apr 2007 15:58:58 -0700
Message-ID: <1176332337.958585.235960@q75g2000hsh.googlegroups.com>

Thanks everyone for your answers,

The best query so far (trusting the Oracle's optimizer forecasting) is the following:

TableA is ~100 millions row and contains (timestamp, evtA) TableB is ~30 millions row and contains (timestamp, evtB)

select TA1_B.evtA, TA2.evtA
from
  (select rownum, TA1.evtA, TA1.timestamp timeA1,

      (select min(TB.timestamp)
        from tableB TB
        where TA1.timestamp < TB.timestamp) min_timeB

    from tableA TA1
  ) TA1_B,
  tableA TA2
where
  TA1_B.timeA1 < TA2.timestamp
  and (TA2.timestamp < TA1_B.min_timeB or TA1_B.min_timeB is null)   and TA1_B.evtA <> TA2.evtA;

It took ~5h to compute instead of the ~60h with the original query posted in one of my previous post (which had a left outer join this new one don't have).
It's still a bit slow for me so I'm still interested by your comments and suggestions!

John Received on Wed Apr 11 2007 - 17:58:58 CDT