Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!news.tiscali.de!newsfeed.freenet.de!newspeer1.nwr.nac.net!border2.nntp.dca.giganews.com!border1.nntp.dca.giganews.com!nntp.giganews.com!local01.nntp.dca.giganews.com!nntp.bt.com!news.bt.com.POSTED!not-for-mail
NNTP-Posting-Date: Thu, 05 Apr 2007 14:52:45 -0500
From: "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk>
Newsgroups: comp.databases.oracle.server
References: <1175783243.167337.195580@n59g2000hsh.googlegroups.com>
Subject: Re: Working with huge tables of chronological data
Date: Thu, 5 Apr 2007 20:50:44 +0100
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
Message-ID: <UsKdnffMhZIQzojbnZ2dnUVZ8q6unZ2d@bt.com>
Lines: 92
NNTP-Posting-Host: 86.131.3.195
X-AuthenticatedUsername: NoAuthUser
X-Trace: sv3-7x9cLu8EGXpvsPxToqOk1xl0wJweAYOMQ/N3WoRez1IWPnMJooWrDjyuoKbMjYcWurtPKNLuq/JGcIH!40MC9J1w/AIADT1+nZZiCPS4NzXLag3rBKahlMIJqEgQ+Oygbzx/Oxki2AaoNxWjgijsPtLOFQ==
X-Complaints-To: abuse@btinternet.com
X-DMCA-Complaints-To: abuse@btinternet.com
X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers
X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly
X-Postfilter: 1.3.34
Xref: news.f.de.plusline.net comp.databases.oracle.server:195821


"John" <acide.ascorbique@gmail.com> wrote in message 
news:1175783243.167337.195580@n59g2000hsh.googlegroups.com...
> Hi,
>
> I'm trying to figure out an efficient way to search for the non
> existence of events in chronological data with SQL. The goal (detailed
> below) seems pretty simple but so far it looks like it's tricky to do
> with Oracle. Here's my problem:
>
> I'm working with 2 simple but huge tables each recording a different
> kind of event associated with a timestamp. For instance:
>
> Table A
> (03:50pm, A1)
> (03:55pm, A2)
> (03:58pm, A3)
>
> Table B
> (03:51pm, B1)
> (04:00pm, B2)
>
> I'm looking for all the chronological sequences (Ax, Ay) where no B
> event is present between Ax and Ay. In this example, the result would
> be (A2, A3).
>
> I've been searching actively for an efficient solution for this
> problem and I couldn't find any fast enough. Do you have any idea?
>
> Thanks a lot,
>
> John
>


Interesting problem.

The data size and available resources are likely
to make a big difference when testing solutions
for feasibility.

Here's a possibility, start with:

select 'A' flag, event, timestamp from tableA
union all
select 'B', event, timestamp from tableB

Option a)
Order by timestamp. Open a pl/sql cursor
on the result set and walk the data one row
at a time, reporting rows when the current
and previous rows are 'A' rows.


Option b)
Use the analytic lag(,1) function

select
    flag, event, prior_event, timestamp, prior_timestamp
from (
select
    flag, lag(flag,1) over (order by timestamp) prior_flag,
    event, lag(event,1) over (order by timestamp) prior_event
    timestamp, lag(timestamp,1) over (order by timestamp) prior_timestamp
from
    (the union all query)
where
        flag = prior_flag
and    flag = 'A'  -- if you just want A's without a B in between.
;

I may have some errors in the analtyic code, but I hope
there's enough there to give you the right idea.

Either option will lead to a massive sort operation on
all your data.



-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html