Re: am I reading this SQL correctly?

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_at_yahoo.com> 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_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
>
>
>

-- 
Rumpi Gravenstein

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