index duplicate values, how much ?
Floyd asked whether there's a rule of thumb for the ratio of nrows to nunique values in an index, beyond which the index stops being worthwhile. An Oracle-centric reply (posted to the Informix group by mistake, and flamed for it) said there's no fixed rule but that around 17-20% selectivity the optimizer tends to favour a full table scan, and that you should test each query with explain plan. Another poster agreed ~20% is a reasonable figure for Informix too, adding that for updates/deletes you may still want the index since a sequential scan can lock the whole table - a claim another reader questioned but which went unanswered. No definitive rule or resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Is there a general rule of thumb about the ratio of nrows to nunique in an index, before it becomes more efficient to not have the index ? Thanks, floyd ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ======================== http://www.one.org/
Floyd Wellershaus wrote: > Is there a general rule of thumb about the ratio of nrows to nunique in > an index, before it becomes more efficient to not have the index ? > > Thanks, > floyd No. First of all there is no such thing as "an index" in Oracle. Do you mean B*Tree? Bitmap? Reverse Key? Descending? Partitioned? Cluster? IOT? Compressed? Essentially: What kind of index? And used in what way? By what query? In what version of Oracle? With what optimizer mode? That said, with a B*Tree index at around 17-20% the optimizer will possibly prefer a full table scan which may answer your question but will likely lead you to make bad decisions. You need to test each and every query using AUTOTRACE or Explain Plan. -- Daniel A. Morgan http://www.psoug.org damorgan@x.washington.edu (replace x with u to respond)
Floyd Wellershaus wrote: > Is there a general rule of thumb about the ratio of nrows to nunique in > an index, before it becomes more efficient to not have the index ? > > Thanks, > floyd Sorry ... I accidentally responded in the Informix usenet group when I thought I was in c.d.oracle.server. Please disregard my post ... and obnoxio ... don't both with another pedantic response as you have been kill-filed on this end for all emails. -- Daniel A. Morgan http://www.psoug.org damorgan@x.washington.edu (replace x with u to respond)
DA Morgan said: > Floyd Wellershaus wrote: >> Is there a general rule of thumb about the ratio of nrows to nunique in >> an index, before it becomes more efficient to not have the index ? >> >> Thanks, >> floyd > > No. > > First of all there is no such thing as "an index" in Oracle. Do you > mean B*Tree? Bitmap? Reverse Key? Descending? Partitioned? Cluster? > IOT? Compressed? Essentially: What kind of index? And used in what way? > By what query? In what version of Oracle? With what optimizer mode? > > That said, with a B*Tree index at around 17-20% the optimizer will > possibly prefer a full table scan which may answer your question but > will likely lead you to make bad decisions. You need to test each > and every query using AUTOTRACE or Explain Plan. That's nice, Daniel, but it's got fuck all to do with Informix. Twat. -- Bye now, Obnoxio Information within this post contains forward looking statements within the meaning of Section 27A of the Securities Act of 1933 and Section 21B of the S E C Act of 1934. Statements that involve discussions with respect to projections of future events are not statements of historical fact and may be forward looking statements. Don't rely on them to make a decision. The poster is not a reporting company registered under the Exchange Act of 1934. I have received a life peerage from Her Majesty, who is not an officer, minister or affiliate Labour party member. I intend to recover my loan now, which could cause the parliamentary majority to go down, resulting in losses for you. Today's Labour party has: an accumulated deficit and a reliance on loans from officers and affiliates to pay expenses. It is not an operating political party. The party is going to need financing to continue as a going concern. A failure to finance could cause the party to go out of business. This report shall not be construed as any kind of investment advice or solicitation. You can lose all your money by investing in this party.
DA Morgan said: > Floyd Wellershaus wrote: >> Is there a general rule of thumb about the ratio of nrows to nunique in >> an index, before it becomes more efficient to not have the index ? >> >> Thanks, >> floyd > > Sorry ... I accidentally responded in the Informix usenet group when > I thought I was in c.d.oracle.server. Please disregard my post ... > and obnoxio ... don't both with another pedantic response as you have > been kill-filed on this end for all emails. Too late. :op -- Bye now, Obnoxio Information within this post contains forward looking statements within the meaning of Section 27A of the Securities Act of 1933 and Section 21B of the S E C Act of 1934. Statements that involve discussions with respect to projections of future events are not statements of historical fact and may be forward looking statements. Don't rely on them to make a decision. The poster is not a reporting company registered under the Exchange Act of 1934. I have received a life peerage from Her Majesty, who is not an officer, minister or affiliate Labour party member. I intend to recover my loan now, which could cause the parliamentary majority to go down, resulting in losses for you. Today's Labour party has: an accumulated deficit and a reliance on loans from officers and affiliates to pay expenses. It is not an operating political party. The party is going to need financing to continue as a going concern. A failure to finance could cause the party to go out of business. This report shall not be construed as any kind of investment advice or solicitation. You can lose all your money by investing in this party.
Hello Floyd, this is not an easy question to answer. i guess it depends. for selecting info it is faster to do a seq scan if the engine has to read more then 20 % of the data. -- Thanks Daniel, it's the same in obstacle... so i guess 20% is a fairly good figure... If you do updates/deletes , you may not want a seq scan since it locks the whole table.... So it depends!!! Superboer.
> > If you do updates/deletes , you may not want a seq scan since it locks > the whole table.... > Can you explain this further?