RE: Add more index or not ?

Jared:

The reason the B-tree was smaller may have been due to how Oracle treats duplicate values in an index. Adding the PK made the index more selective and thereby take less space, IMHO.

Colin.

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:
bcc:

Subject: 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 jkstill_at_teleport.com - private

--
Author: Jared Still
  INET: jkstill_at_bcbso.com




______________________________________________________________________
The information in this email and in any attachments is confidential and may be
privileged.  If you are not the intended recipient, please destroy this message,
delete any copies held on your systems and notify the sender immediately.  You
should not retain, copy or use this email for any purpose, nor disclose all or