RE: Database Archive
- From: Chris Stephens <ChrisStephens@xxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 13 Apr 2004 08:18:33 -0500
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@xxxxxxxxxxxxx]
Sent: Monday, April 12, 2004 9:19 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx 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: 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:
