Re: reduce table fragmentation by design
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: exriscer@xxxxxxxxx
- Date: Wed, 26 Jan 2011 08:05:44 +0000
So far I haven't seen anyone suggest an IOT. I'd imagine that this sort of
table would make a reasonably good candidate for an IOT (depending I guess
on whether you end up using overflow segments or not). It seems, at least at
first glance, to have enough similarities to a queue or message table to be
worth exploring.
On Wed, Jan 26, 2011 at 7:58 AM, LS Cheng <exriscer@xxxxxxxxx> wrote:
> 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/ <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.
>>
>>
>>
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
