Help with this query

Hi,

I'm having a hard time with this query:

select timeSnd, timeCnx, subidSnd subid, channel, pos from (select a.time TimeSnd, b.time TimeCnx, a.subid subIdSnd, b.subid subIdCnx, b.channel, row_number() over (partition by a.time, a.subid, a.toemail order by b.time desc) pos from (select time, subid,toemail from eventsSnd where time between to_date('01/09/2003','DD/MM/YYYY') and to_date('01/09/2003 23:59:59','DD/MM/YYYY HH24:MI:SS') a, EventsCnx b where substr(b.subid,-8)=substr(a.subid,-8) and b.time < a.time and b.time+1/24 > a.time) where pos=1

The tables are EventsCnx and EventsSnd. To know from which channel a message has been sent, I have to check the EventsCnx table.

For each row in EventsSend, I have to find the row in EventsCnx whose time is immedietaly inferior and within and hour, and that has the same subid column (in fact, the same last eight numbers).

This is taking way too much time, and I'm really needing help. There are 7'5 million connections, and about 150 000 messages sent. For one particular day (1/9/2003), there are 53000 connections, and just 1280 messages, but this is needing about five minutes, so I believe something's really wrong with my request

Is there any other way to do this query? Where should I use indexes?

I'm using Oracle9i.

Thanks for your answers! Received on Thu Nov 27 2003 - 03:02:22 CST