| 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
Just a though....but you could enforce the time predicate with row-level
security. Users that didn't need the old data wouldn't even have to know it
was there.
chris
-----Original Message-----
From: Tim Gorman [mailto:tim_at_sagelogix.com]
Sent: Monday, April 12, 2004 9:19 PM
To: oracle-l_at_freelists.org
Subject: 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 Tue Apr 13 2004 - 08:15:04 CDT
 |
 |