Re: am I reading this SQL correctly?

From: Nigel Thomas <nigel_cl_thomas_at_yahoo.com>
Date: Thu, 20 Sep 2007 09:13:33 -0700 (PDT)
Message-ID: <888447.42589.qm@web58712.mail.re1.yahoo.com>

Gene

How is the "current call" identified? - this query will report ANY calls that overlap (where call A starts during call B) - even if both calls are way in the past.
It could be restated as a.CALL_START_TS between B.CALL_START_TS and B.CALL_END_TS - which could be a range scan (in a nested loop) but B has no predicates. Still unpleasant!
Why doesn't it check for B starting during A
Why doesn't it check for B completely overlapping A (B starts before and finishes after A) and vice versa

You have identified:
A: ---- | -- | ---- | ----
B:---- | ------ | ---- | ----

but not other variants eg

A:----    | ------  | ----                
B:  ----  |   ---   |     ----

(view in a monotype font or this will look silly)

5) In a procedural loop (within a pipeline function) I think you could do this in a single pass (ordered by CALL_START_TS) start with an empty in memory collection of calls (start time, end time, overlap count) for all records
- read a record

increment overlap count for all collected calls that overlap with this one
register its start time and end time (into the collection in memory) (with the total overlap count just calculated)
report (eg by emitting a row from the pipelined function) all calls in the collection with an end time prior to the current record's start (don't include records with no overlaps)
and remove those records from the register At the end of the loop, emit all remaining records with overlap count > 0 (possibly grouping by call_start_ts - as several calls could start simultaneously I presume)

Having described that, is it possible to do the same with a analytic functions (eg WINDOW)?

HTH Regards Nigel

Original Message ---- From: "genegurevich_at_discover.com" genegurevich_at_discover.com

I am trying to tune the following SQL (in 10.2.0.3)

SELECT a.CALL_STRT_TS, count(b.Call_STRT_TS)

  from IVR.IVC_IVR_CMS_CALL  a, IVR.IVC_IVR_CMS_CALL
  where a.CALL_STRT_TS >= b.CALL_STRT_TS
    and a.CALL_STRT_TS <= b.CALL_END_TS

group by a.CALL_STRT_TS;

The goal of this is to count the number of calls that overlap timewise with a current call.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 20 2007 - 11:13:33 CDT