Re: am I reading this SQL correctly?

From: <genegurevich_at_discover.com>
Date: Thu, 20 Sep 2007 13:55:54 -0500
Message-ID: <OFC943DA89.A6A8EF9B-ON8625735C.00679147-8625735C.0067FE62@discover.com>

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_at_discover.com, oracle-l 
             09/20/2007 11:13          <oracle-l_at_freelists.org>            
             AM                                                         cc 
                                                                           
                                                                   Subject 
                                       Re: am I  reading this SQL          
                                       correctly?

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 - 13:55:54 CDT