Re: reduce table fragmentation by design
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: tim@xxxxxxxxx
- Date: Wed, 26 Jan 2011 08:58:52 +0100
Thanks!
I am not sure if we can change the entire table logic in the short term
though, this is a third party developed package and although they are
willing to make changes to implement this they might take months.
So back to my question if I make insert select in a ordered fashion should
the fragmentation improve?
Thanks
On Wed, Jan 26, 2011 at 1:18 AM, Tim Gorman <tim@xxxxxxxxx> wrote:
> LSC,
>
> Attached is a SQL*Plus script with a demo test case for partitioned
> UNION-ALL views, in case you're interested in seeing how easy it is? I've
> had this script (or one like it) since Oracle7 days...
>
> Hope this helps...
>
> Tim Gorman
> consultant -> Evergreen Database Technologies, Inc.
> postal => 13087 West Cedar Drive #225, Lakewood CO 80228
> website => http://www.EvDBT.com/
> email => Tim@xxxxxxxxx
> mobile => +1-303-885-4526
> fax => +1-303-484-3608
> Lost Data? => http://www.ora600.be/ for info about DUDE...
>
>
> On 1/25/2011 1:41 PM, Stephens, Chris wrote:
>
> 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 <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:
