am I reading this SQL correctly?

  • From: genegurevich@xxxxxxxxxxxx
  • To: oracle-l@xxxxxxxxxxxxx (oracle-l)
  • Date: Thu, 20 Sep 2007 10:04:18 -0500

Hello:

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. So my developer is
joinig the table to itself counting all the calls that were in progress
when a current call started. The table has
about 3.9 mil rows. I think the issue here is that without an equ-join
oracle will have to compare each row from
the table to every other row. I do see a sort-merge in the explain plan. If
I am right, what do I do to speed up this
SQL?

thank you

Gene Gurevich


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


Other related posts: