RE: Add more index or not ?

From: <shawc_at_willis.com>
Date: Thu, 27 Apr 2000 12:41:00 +0100
Message-Id: <10480.104275@fatcity.com>

Sameer:

I'm going to agree with George that it's a poorly worded question but my answer would be slightly different (given "reasonable" column sizes) in that the retrieval would be the same. It's a given that they must be "duplicates allowed" indices because each has an equality clause and yet returns > 1 row. The rowids for all 100 rows in both cases would more than likely be contained in one leaf block and therefore the access path and logical cost for both indices would be the same. Obviously, the physical cost would be influenced by buffering but I'm not taking that into account. So in both cases the retrieval would be to go through the B-tree from the root to the (one) relevant leaf block and then retrieve the 100 rows by rowid. And even if the larger index was in (say) two leaf blocks, the I/O cost would normally not be any different because both blocks would have been read with one disk read. I'm also assuming that the 100 rows are similarly placed because obviously if they are physically adjacent in one database and not in the other then the total I/Os would be different.

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 ?

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

--
Author:
  INET: George.Brennan_at_warnermusic.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

Received on Thu Apr 27 2000 - 06:41:00 CDT