Re: Database Archive
- From: "zhu chao" <chao_ping@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 13 Apr 2004 09:58:03 +0800
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@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
