Re: Database Archive

From: zhu chao <chao_ping_at_vip.163.com>
Date: Tue, 13 Apr 2004 09:58:03 +0800
Message-ID: <009d01c420fa$d35e4520$2552fc0a@corp.ebay.com>

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.

Regards
Zhu Chao

Original Message ----- From: <tim_at_sagelogix.com> To: <oracle-l_at_freelists.org> Sent: Tuesday, April 13, 2004 5:23 AM Subject: Re: Database Archive

> This is exactly the topic that Jeff Maresh wrote about in his "Managing
the Data Lifecycle" paper, available online at "http://www.EvDBT.com/papers.htm". I think it's going to be in SELECT magazine soon, too...
>
> There is no need to take data "offline" for archival, at least in Oracle.
The benefits discussed in the article can become immediately available when range-partitioning by date along with the storage cost benefits of near-line storage. Migration of data from one storage media to another can occur with little or no interruption to user access.
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
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 - 20:55:13 CDT