Re: Mythical space savings of IOTs

  • From: Steve Rospo <Steve.Rospo@xxxxxxxxxxx>
  • To: Steve Rospo <Steve.Rospo@xxxxxxxxxxx>
  • Date: Mon, 11 Jul 2005 08:04:30 -0700 (PDT)


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@xxxxxxxxxxx           (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.


--
//www.freelists.org/webpage/oracle-l

Other related posts: