Mark.Schubert_at_MyRealBox.com (Mark Schubert) wrote in message news:<9c9c6d17.0311121456.2088e8eb_at_posting.google.com>...
> Mark.Schubert_at_MyRealBox.com (Mark Schubert) wrote in message news:<9c9c6d17.0311120811.4cffb13f_at_posting.google.com>...
> > This worked in less than 30 seconds!!!! And covers a 24 hour period!
>
> I lied. Well, it's true, but the 24 hour period had no data! It was
> using a date of 3 NOV - 4 NOV and there is not data there anymore
> because we keep only 6-8 days worth. So when I tried it on a current
> 24 hour period covering 11 NOV, it failed with rollback segment too
> small. Then I tried your suggestion with another - combined them and
> was able to scan a 60 minute (1 hour) period in 24 seconds. Still a
> little slow.
>
> THANKS again anyway! I appreciate your time!
>
> Mark.
I think the reason you get the rollback segment too small problem is
because your database is designed for small short update transactions
while this query is a long running read-only transaction, in the
latter case Oracle doesn't extend the rollback segment (or you may not
have enough space in the rollback tablespace). First try increasing
the initial extent size of the rollback segment to I don't know what,
maybe a few hundred Meg.
In my experience the hash hint is normally the best when joining large
amounts of data, I always avoid indexes in this case. I have seen it
reduce a day+ long query to 20min. But if you are restricted to small
rollback segment space then you may be restricted to using indexes.
Received on Wed Nov 12 2003 - 21:08:49 CST
