RE: reduce table fragmentation by design
- From: "Stephens, Chris" <Chris.Stephens@xxxxxxx>
- To: "exriscer@xxxxxxxxx" <exriscer@xxxxxxxxx>, Oracle Mailinglist <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 25 Jan 2011 14:41:11 -0600
Why not just roll your own weekly partitions?
A table for each week + UNION ALL view + some PL/SQL to truncate oldest weekly
table + possibly some modifications to either the existing loading procedures.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of LS Cheng
Sent: Tuesday, January 25, 2011 2:10 PM
To: Oracle Mailinglist
Subject: reduce table fragmentation by design
Hi
We have a database which receives statistics data every 5 minutes and this data
goes to a heap table, not partitioned because this is Standard Edition 10.2.0.4.
The table has around 50 million of rows, most data are inserted using insert
into select........., around 80%, the rest 20% plain insert into... values....
The table is formed by a surrogate key, a timestamp and several columns which
stores numbers, every week or so there is a purge process whcih retains 45 days
data, the column used to purge is the timestamp.
The problem is after sometime the table is so fragmentated that to read 10000
rows through 10000 index scans each row is stored in different data blocks. So
basically for each 10000 scan 10000 blocks must be read (plus the index scan
but that is little, only 60 blocks or so). The way to relieve this performance
problem is rebuild the table and the indexes.
I wonder if following would help to reduce fragmentation, in the insert...
select statement add an order by timestamp clause so all rows are inserted in
order, since the purge process is based on timestamp this will remove rows
sequentially (located in same extent).
I have some other ideas such as shrink table compact space once a couple of
times a day and shrink the table once 2 days, then every month rebuild the
table once using alter table move.
I am also thiking to move the table to manually segment space management with
old freelists and pctused.
Thanks
--
LSC
CONFIDENTIALITY NOTICE:
This message is intended for the use of the individual or entity to which it is
addressed and may contain information that is privileged, confidential and
exempt from disclosure under applicable law. If the reader of this message is
not the intended recipient or the employee or agent responsible for delivering
this message to the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this communication is strictly
prohibited. If you have received this communication in error, please notify us
immediately by email reply.
Other related posts:
