Presumably you have tried placing function based indexes on the 'subid'
fields and even the 'date' portion of the 'a.time' field?
"Joe Smith" <nospam_at_nospam.com> wrote in message
news:bq4eim$dbc$1_at_news-reader5.wanadoo.fr...
> 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 - 10:25:31 CST
