Re: am I reading this SQL correctly?
- From: genegurevich@xxxxxxxxxxxx
- To: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- Date: Thu, 20 Sep 2007 13:55:54 -0500
Nigel,
You are right on point 1. The SQL will go through ALL the calls. My usage
of the "current call" was very misleading.
The users do want to go through all the calls. Analytical functions are a
good idea. I'll look into that.
What I wonder also is whether this SQL is kind of a Cartesian join query in
its current design. Not a pure CJ
because we do have a join between two tables, but with a need to join each
row of table 'a' to each row
of table 'b'. If that it the case, then on a 3.9 mil rows table, this SQL
is going to run for a long time. Does
this make any sense?
thank you
Gene Gurevich
Nigel Thomas
<nigel_cl_thomas@
yahoo.com> To
genegurevich@xxxxxxxxxxxx, oracle-l
09/20/2007 11:13 <oracle-l@xxxxxxxxxxxxx>
AM cc
Subject
Re: am I reading this SQL
correctly?
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:
