Re: am I reading this SQL correctly?
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: genegurevich@xxxxxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 Sep 2007 09:13:33 -0700 (PDT)
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
Other related posts:
