Re: Time series deletion performance

On Oct 12, 3:29 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Oct 12, 2:25 pm, pe..._at_alum.wpi.edu wrote:
>
>
>
> > On Oct 12, 3:21 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > On Oct 12, 2:03 pm, pe..._at_alum.wpi.edu wrote:
>
> > > > I am storing timestamps as numbers, because that's how it was when I
> > > > inherited the db. I hadn't considered that to be an issue.
>
> > > > I'm unable to find the post you are referring to by Jonathan Lewis.
>
> > > > On the issue you refer to with 00 coming after 59, I assume the
> > > > problem you describe is with leading zeros being omitted. We are
> > > > storing the number of milliseconds since 1970 and won't be susceptable
> > > > to a leading zero issue for a couple hundred years.
>
> > > > Also, we are running on Oracle 10g.
>
> > > > Thanks,
> > > > Peter
>
> > > I wonder how you're generating those values.
>
> > > David Fitzjarrell
>
> > We are using a java application to populate the timestamp when an xml
> > message comes into the web app. Java will easily provide the number
> > of millis since epoch (jan 1, 1970). It would be innacurate to use
> > the db current_timestamp anyways, since we queue up 10,000s of
> > requests and write them to the db in bulk periodically.
>
> > -Peter- Hide quoted text -
>
> > - Show quoted text -
>
> So your 'timestamp' data may be skewed? Have you thought of
> histograms to assist the optimizer?
>
> David Fitzjarrell

I don't think our data is skewed. The timestamp is assigned based on when a random message comes in, not in when it is written to the db. The messages should, over time, be evenly distributed by timestamp. AFAIK, the histogram would only help query performance when finding the rows to delete. The query plan for my simple delete by timestamp shows that we are just accessing the timestamp column by an index range scan. I wouldn't expect anything else to perform better for row retrieval. I don't think querying for the data is the issue, I think it is with deletion performance. My guess is that I am deleting one row at a time from every block and the redistribution of data between blocks is taking time. Is there any way to verify this? Is there any way to avoid it?

Thanks,
Peter Received on Fri Oct 12 2007 - 16:10:09 CDT