Help with long SQL Query

Hi, this is a repost from a question yesterday on c.d.oracle. It seems it hasn't made it out from my local news server (I don't see it in google):

==

I have a query that takes way too long to execute, and not being a DBA, I'm not quite aware of all the possibilities I may have to improve it within Oracle. Still in development phase, we can change tablespaces, design, the query itself...

The idea: the events YY (sending a message) are posterior to the XX ones (connection). However, when sending a message, I have to go back (1 hour at most) to the last connection that has the same subid (user id), and get the channel from which it was sent.

So far, this is what I have:

select a.time TimeYY, b.time TimeXX, a.subid subIdYY, 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 eventsYY) a, EventsXX b where b.subid=a.subid and b.time < a.time and b.time+1/24 > a.time ) where pos= 1

With the EventsYY (messages) table having 50,000 records, and the EventsXX(connections) having around 6,000,000 , this is taking way too long to execute.

I guess that for every one of the 50000 records, Oracle scans the connection table to find the good value. You may imagine how slow this is...

I have tried to implement a trigger to get the channel on every insert, but it slows the insertions too much.

What could I do to improve performance?
- Is an index in both subid columns worthy?

• And in the time column? I think that this may not be useful, as there are lots of different values and the table with the index may be too big to look up.
• Creating range-partitioned tablespaces (with the) subid may help?
• Any other way to do this query?
• Oracle parameters that may affect performance?

Thanks for any help or pointers. Received on Fri Jun 18 2004 - 03:44:55 CDT