RE: Add more index or not ?

From: Oak, Sameer <Sameer.Oak_at_CWUSA.COM>
Date: Wed, 26 Apr 2000 15:24:03 -0400
Message-Id: <10479.104222@fatcity.com>

Colin

Thanks for replying.

I remember someone asking me this question in interview.

Two cases on diff databases

Table with index on only one column say c1. where clause is := where c1 = 'something' (Returning 100 records , total 100000 records)
Table with index on two columns say c1,c2 where clause is := where c1 = 'something' (Returning 100 records , total 100000 records)

The question was in which case the retrieval is faster (Assuming rest all identical for indexes)

I answered case 1. Is it correct ??
Or in both cases there is really no difference how oracle traverse thro' index & fetches required rows???

Sameer

-----Original Message-----
From: shawc_at_willis.com [mailto:shawc_at_willis.com] Sent: Wednesday, April 26, 2000 11:53 AM To: Multiple recipients of list ORACLE-L Subject: Re: Add more index or not ?

> 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:

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----
Reply-To: "Oak, Sameer" <Sameer.Oak_at_CWUSA.COM> To: "oracle list" <oracle_at_telelists.com> 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.

-- 
Author: 
  INET: shawc_at_willis.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Wed Apr 26 2000 - 14:24:03 CDT