RE: Add more index or not ?

On Thu, 27 Apr 2000 George.Brennan_at_warnermusic.com wrote:

>
> Looks like a (badly) contrived question to me.
>
> Strictly speaking there will are likely to be more index blocks for case 2 due to the increased span of each key.
>

But not always.

I learned a valuable lesson about this several years ago.

We had a large table that required an index that was not terribly selective. Even so, this index was necessary for some queries. A full table scan would blow our 9 second end-to-end online response time out of the water.

When trying to create this index, I would continually run out of space. The index was much larger than I predicted it would be.

A contractor at the site had run into this before, and suggested adding the PK as another column on the index. ( The PK was a generated integer )

This didn't make a lot of sense to me at the time, but I tried it and guess what, it worked.

The resulting index was significantly smaller than the one that was created without the PK column.

This was due to the way btree works; the index with the PK in it was broader and shallower than the one without it, and took a lot less space. ( I think I go that right :)

Smaller index, faster reads.

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon
jkstill_at_bcbso.com - Work - preferred address Received on Thu Apr 27 2000 - 09:22:00 CDT