RE: Add more index or not ?

Hmm,

The index key was col1, col2 so it could still be a unique index.

George

> -----Original Message-----
> From: <shawc_at_willis.com> [mailto:shawc_at_willis.com]
> Sent: Thursday, April 27, 2000 12:41 PM
> To: smtp_at_inl001@servers[<ORACLE-L_at_fatcity.com>];
> smtp_at_inl001@servers["Oak, Sameer" <Sameer.Oak_at_CWUSA.COM>]; George
> Brennan_at_IT@WMINTL19
> Subject: RE: Add more index or not ?
>
>
>
>
> 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
Received on Thu Apr 27 2000 - 07:34:47 CDT