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