RE: Add more index or not ?

Colin,

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.

more blocks may take longer.

As usual with these questions there are other questions to ask.

> The question was in which case the retrieval is faster

Retrieval of what? I'd assume that in both cases the index range scan will return the same set of key values and so will retrieve the same set of data blocks. So the retrieval time is the same, the index side will take longer depending on the indexed colum sizes but may only be measurable in miliseconds - all things being equal.

On a bad day I'd just say, 
"Sorry, but it's an ambiguous question"
I suppose the 'classic' answer is case 1, but in reality retrieval time will basically be the same as the index will return the same data keys. All things being equal they will probably be returned in the same manner too so it's only the time take to traverse the index which will be slightly longer possibly milliseconds.

On the other hand, if you want to know how indexing works :-).

And probably blow the interview :-)

regards
George.

> -----Original Message-----
> From: "Oak, Sameer" <Sameer.Oak_at_CWUSA.COM>
> [mailto:Sameer.Oak_at_CWUSA.COM]
> Sent: Wednesday, April 26, 2000 9:44 PM
> To: smtp_at_inl001@servers["Multiple recipients of list ORACLE-L"
> <ORACLE-L_at_fatcity.com>]
> Subject: RE: Add more index or not ?
>
>
> Colin
>
> Thanks for replying.
>
> I remember someone asking me this question in interview.
>
> Two cases on diff databases
>
> 1. Table with index on only one column say c1.
> where clause is := where c1 = 'something'
> (Returning 100 records , total 100000 records)
>
> 2. 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-----
> Sent: Wednesday, April 26, 2000 11:53 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
>
> > 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
> also send the HELP command for other information (like subscribing).
> --
> Author: Oak, Sameer
> INET: Sameer.Oak_at_CWUSA.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
Received on Thu Apr 27 2000 - 03:55:05 CDT