| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Database Archive
Zhu,
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!
-Tim
on 4/12/04 7:58 PM, zhu chao at chao_ping_at_vip.163.com 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.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon Apr 12 2004 - 21:14:28 CDT
 |
 |