Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Time series deletion performance

Time series deletion performance

From: <peter_at_alum.wpi.edu>
Date: Fri, 12 Oct 2007 08:53:37 -0700
Message-ID: <1192204417.914771.321560@t8g2000prg.googlegroups.com>


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 Received on Fri Oct 12 2007 - 10:53:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US