Use partitioened table when you can
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96524/c12parti.htm
Put one month data into one partition.
<peter_at_alum.wpi.edu> wrote in message
news:1192204417.914771.321560_at_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 Sun Oct 14 2007 - 23:48:59 CDT
