Re: Mythical space savings of IOTs

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-l