Re: Time series deletion performance

On Oct 12, 10:53 am, pe..._at_alum.wpi.edu wrote:
> I'm working with a large table of time series data that contains 30
> days worth of information (100 Million+ rows). The issue is that
> every day we attempt to delete data > 30 days old and it can take up
> to 10+ hours. This is unacceptable as we have a constant load on our
> web site at all times of the day and causes a slowdown of the entire
> db.
>
> It looks similar to this:
>
> time_series_table
> -------------------------
> timestamp NUMBER(38), --Timestamp down to millisecond, yes we need
> that precision
> id1 NUMBER(38), --reference to another table id
> id2 NUMBER(38), --reference to another table id
> value VARCHAR2(500) -- Could be free text or numeric
>
> There are indexes on (id1, id2) and (timestamp). id1 has about 10,000
> unique values and id2 has about 50 unique values. The value column is
> customer dependent and could be a continuous numeric value or free
> text value depending on customer need. This table gets installed at
> various customer sites that may not have Oracle EE, so I cannot always
> depend on partitioning. Also, the table is analyzed weekly, so my
> stats are up to date. Data is inserted into this table with timestamp
> = sysdate (data is not inserted out of date order). Data is always
> deleted by oldest timestamp as well.
>
> This seems like a trivial problem if the table is stored on disk by
> timestamp (Oracle could just drop the first x blocks of the table). I
> considered storing this table as an IOT based off the timestamp, but
> timings prove that it doesn't dramatically help. Aside from creating
> a view of daily tables and dropping/creating them daily (manually
> partitioning), is there a better way to store or delete from this
> table?
>
> Thanks,
> Peter

I'm wondering why you're storing timestamp values as numbers; this is similar to what Jonathan Lewis explained regarding storing dates as strings. Oracle doesn't know that 00 comes after 59 when you store this as a number; as a date/time it would be obvious and thus more efficient. I expect Oracle 'thinks' the timestamp values should be continuous and has over-estimated the number of rows involved. Of course Jonathan can explain this better than I can, but I suspect this problem is due to how you're storing your data.

David Fitzjarrell Received on Fri Oct 12 2007 - 11:15:01 CDT