Mark D Powell wrote:
> "Joe Smith" <nospam_at_nospam.com> wrote in message news:<caua25$ks0$1_at_news-reader2.wanadoo.fr>...
>
>>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.
>
>
> Jow, without an explain plan it is hard to say what will help so run
> an explain plan on each query involved. For each plan try to
> determine if Oracle is driving on the right table and that the plan
> looks like what you think it ought to be doing. Try to test any area
> of doubt.
>
> Also couldn't "from (select time, subid,toemail from eventsYY) a" just
> be "from eventsXY a"?
>
> HTH -- Mark D Powell --
Add it factors like statistics (were they run?), the lack of
optimizer hints, goodness knows what was done about indexes, how
the database was constructed, what type of server the database is
running on, and probably a few factors that I can't even think of
this late at night, almost anything could be going wrong.
Received on Fri Jun 18 2004 - 20:46:31 CDT
