Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Add more index or not ?

RE: Add more index or not ?

From: <>
Date: Fri, 28 Apr 2000 10:44:54 +0100
Message-Id: <>


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.


Please respond to

To: Multiple recipients of list ORACLE-L <> cc:

Subject: RE: Add more index or not ?

On Thu, 27 Apr 2000 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 - Work - preferred address - private

Author: Jared Still

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
Received on Fri Apr 28 2000 - 04:44:54 CDT

Original text of this message