One issue that Nigel hasn't addressed is that you will want to account for a call overlapping with itself. That might be done with something along the lines of: a.ROWID != b.ROWID On 9/20/07, Nigel Thomas <nigel_cl_thomas@xxxxxxxxx> wrote: > > Gene > > 1) 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. > 2) 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! > 3) Why doesn't it check for B starting during A > 4) 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@xxxxxxxxxxxx" genegurevich@xxxxxxxxxxxx > > 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. > -- > //www.freelists.org/webpage/oracle-l > > > -- Rumpi Gravenstein