Re: reduce table fragmentation by design
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- Date: Wed, 26 Jan 2011 09:21:04 +0100
Yes, there was a suggestion using IOT.
But I am not sure if it is gonna help because the table is made up of 11
columns and only two are indexed (surrogate key and the timestamp), the rest
would be in the overflow segment. How can IOT mitigate the problem in this
case if we still have to query the overflow segment? Sorry my ignorance with
IOTs.
Thanks
--
LSC
On Wed, Jan 26, 2011 at 9:05 AM, Niall Litchfield <
niall.litchfield@xxxxxxxxx> wrote:
> 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:
