Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Database Archive

Re: Database Archive

From: Tim Gorman <>
Date: Mon, 12 Apr 2004 20:18:39 -0600
Message-ID: <>


If its OK to actually remove data from a table to an offline archive store, then why isn't it OK to restrict on the partitioning key column in order to restrict the volume of data being queried?

True enough that people will forget to restrict on the partition key; human nature and all that. But if they think it through, then what they are really complaining about is having access to all the data that otherwise would have been offlined. If they do not want to query that data, then they should't query that data. If they really want to query the data that would otherwise have been archived and removed, then they should appreciate it.

Kind of like tuning the complaint being raised instead of the SQL statement?

Hope this helps!


on 4/12/04 7:58 PM, zhu chao at wrote:

> Hi, tim:
> I don't think range partition can solve the problem of bigger tables
> totally in real world. As we know, even if you do range partition, not all
> SQLs can use that partition key in the where clause, so many other SQL will
> have to scan the whole table/index , not just the useful table partition.
> For example, one table
> product( id number, description varchar2(4000), seller_id number, other_col
> col_type, status number, reg_date_dtm date, last_modify_dtm date) has 10M
> records, it is partitioned by reg_date_dtm, primary key is id.
> There is SQL like:
> select count(*) from product where seller_id = :b1 and status=:b2,
> which is very common SQL in real applications, it cannot use the partition
> prune. We used database table partition to archive old data, which greatly
> reduced the PIO on our database server. Else, we would have run out of our
> disk io capacity long time ago.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Mon Apr 12 2004 - 21:14:28 CDT

Original text of this message