Re: Add more index or not ?

From: <shawc_at_willis.com>
Date: Wed, 26 Apr 2000 15:27:55 +0100
Message-Id: <10479.104188@fatcity.com>

> Both uses a hashing algorithm to convert the key value before it...

Actually, no. A B-tree (or variation thereof) uses NO hashing techniques. That would be a Hash Index. A B-tree is a sorted (or ordered) index.

Sameer:
An index on columns A and B will obviously use more storage than a index on just column A, but if you access the data by mostly column A and sometimes columns A & B, then there is NO point having both an index on columns A & B and another one on A alone. The "A" index is just un-needed overhead. Please re-post if you'd like to discuss this further.

Remember: The purpose of an index is to speed up reads and slow down writes.

Colin.

Please respond to "Rajagopal Venkataramany" <rajagopalvr_at_hotmail.com>

To: "oracle list" <oracle_at_telelists.com> cc: oracle_at_telelists.com
bcc:

Subject: Re: Add more index or not ?

Hi,

Both uses a hashing algorithm to convert the key value before it stored or used in search. Obviously the storage requirements would be more for a composite index when compared with a independent index.

Regarding the effect on performance when using a composite index (A+B) instead of a index on A alone :

It depends on the data distribution. With the combination of A+B, if we are able to hit a few set of records then this would be the best approach. You can use this in situation where both the values of A and B are known.

If the requirement is that most of the time only A is known, then it would make sense to have a independent index on just A alone.

Regards
Rajagopal Venkataramany

----Original Message Follows----
From: "Oak, Sameer" <Sameer.Oak_at_CWUSA.COM> Reply-To: "Oak, Sameer" <Sameer.Oak_at_CWUSA.COM> To: "oracle list" <oracle_at_telelists.com> Subject: Add more index or not ?
Date: Tue, 25 Apr 2000 13:11:51 -0400

Raj

I missed ur message which was posted on 4/18

How sure are u about the statement

       The composite index on A & B will give the same effect as of
       having a independent index on just A alone. (Apart from the

update/delete overhead)

What I was under impression that composit index will be technically slower since it has to read A,B,rowid. & the index only on A will have to read A,rowid.

Is this true???
Thanks
$ameer

<< snip >>

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 any part of its content to any other person.

Received on Wed Apr 26 2000 - 09:27:55 CDT