am I reading this SQL correctly?

From: <genegurevich_at_discover.com>
Date: Thu, 20 Sep 2007 10:04:18 -0500
Message-ID: <OFD3A0EF03.F60063FE-ON8625735C.0051EB9D-8625735C.0052CA27@discover.com>

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

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

Received on Thu Sep 20 2007 - 10:04:18 CDT