| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Mythical space savings of IOTs
As promised here's my longer explanation of what is happening. Because IOTs must maintain order there's exactly one block that any given row can reside in. Just as with B-tree indexes, when a block fills it must be split. If you're doing a right hand insert (ie always adding the row in PK order) the split will leave a full block at the "end" of the index and add a new empty block. If you're adding in just about any other order the splits will happen at random intervals along the block. This will leave some blocks almost totally full, some almost empty, but on average the blocks will be half full.
Here's some guesses: your PK is something like (id, tstamp) and you add rows in tstamp order like this:
1, 1/1/05 2, 1/1/05 3, 1/1/05 4, 1/1/05
1, 1/2/05 2, 1/2/05 3, 1/2/05 4, 1/2/05
1, 1/3/05 2, 1/3/05 3, 1/3/05 4, 1/3/05
If you put this in a non-partitioned IOT with the (id, tstamp) PK you'll be leaving a bunch of split half-empty blocks behind you.
1, 1/1/05
<- 1, 1/2/05
2, 1/1/05
<- 2, 1/2/05
3, 1/1/05
<- 3, 1/2/05
4, 1/1/05
<- 4, 1/2/05
If you put this in a date partitioned IOT with the (id, tstamp) PK you'll be doing a right hand insert in each partition.
Partition 1/1/05
1, 1/1/05 2, 1/1/05 3, 1/1/05 4, 1/1/05
Partition 1/2/05
1, 1/2/05 2, 1/2/05 3, 1/2/05 4, 1/2/05
Once your IOT is fragmented like this you can do a move and everything will be added in order and re-pack the rows in completely full (at least within pctfree of completely full). Partitioning also helps here because you can move each partition individually.
S-
On Fri, 8 Jul 2005, Steve Rospo wrote:
>
> My guess: it's the index leaf splitting causing sparse leaves. I consider
> myself the world wide expert on just this one tiny little corner of Oracle
> functionality. :-)
>
> Try your unpartitioned test again then do a MOVE on the table. My guess
> is that you'll see the IOT shrink considerably. The whys and wherefores
> will have to wait until Monday since I should have left the office 4.5
> minutes ago.
>
> S-
>
> On Fri, 8 Jul 2005, Bill Coulam wrote:
>
> > Just stumbled on my answer. Now if anyone can answer WHY this works,
> > I'd love to hear from you.
> >
> > As you recall, my 1M row test case came to 127MB with all related
> > objects included. I was only able to achieve 88MB in my IOT test. No
> > matter what I did, the index_stats view was showing only 47-50%
> > utilization of the index blocks in the IOT. Which was frustrating.
> >
> > There was one aspect of my test that wasn't honest with reality
> > though. The original table is composite partitioned, by range and then
> > by hash (legacy model; not mine). I simplified my tests with a regular
> > IOT. So I lucked out by deciding my last test would be to see what a
> > partitioned IOT would do. I expected at least 10% greater space usage.
> > Instead I got a 71% savings!
> >
> > Again the sample was built on normal heap model, but this time
> > partitioned by range on start_date and subpartitioned by hash on
> > line_id (4 subs per part). Total space of objects: 140MB.
> >
> > Then I rebuilt it using IOT partitioned by range on start_date.
> > Subparts weren't possible. Lo and behold, after analyzing the IOT,
> > index_stats shows me 93% utilization of the BTREE_SPACE. Total space
> > of IOT object: 41.25MB !!!!!
> >
> > Yee-haw!
> >
> > I just can't fathom why a normal IOT would reserve so much space in
> > the index blocks, but a partitioned IOT does not. I changed nothing
> > else about the COMPRESS, PCTTHRESHOLD, or PCTFREE.
> >
> > Go figure.
> >
> > Please.
> >
> > Thanks!
> >
> > bc
> >
>
>
-- Stephen Rospo Principal Software Architect Vallent Corporation (formerly Watchmark-Comnitel) steve.rospo_at_vallent.com (425)564-8145 This email may contain confidential information. If you received this in error, please notify the sender immediately by return email and delete this message and any attachments. Thank you. -- http://www.freelists.org/webpage/oracle-lReceived on Mon Jul 11 2005 - 10:09:07 CDT
 |
 |