Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606

    Thumbs up Unanswered: Did You Know: MYTH! "If >20% of a table is returned by a query it will table scan."

    Taken from 1-800-Database.com
    Did you know that the following is a myth commonly believed to be true of the Sybase ASE query optimizer? "If more than 20% of a table is returned by a query, the only choice the optimizer has is to table scan."

    This is a very old myth, as the story goes, many years ago a senior engineer answered a question at an ISUG conference. The question itself is list in the mists of time. The off-the-cuff answer was something like, "Hmm, well, if about 20% of a table will be returned by a query, a table scan will be done. The percentage varies at times, but it is always around 20%." This myth was picked up quickly. IT was quoted by Technical Support, consultants, and even put into the manuals. Since we don't know the exact question that was asked, it's hard to know exactly what the former engineer was referring to. However, and educated guess would be that he was talking about the pessimistic costing of non-clustered indexes in pre-11.9.2 versions of ASE. Basically this costing assumed that every read from the leaf of the index to a data page would cost one I/O. In other words, for every qualifying row in the index, one I/O would be done. This, of course, was not the best assumption to make because it didn't take into account that more than one qualifying row may exist on the same database. Even if he was talking about the pessimistic non-clustered index costing, the "20% Rule" still does not hold true. The optimizer's decision of which access to use in order to get the required rows is based on the estimated cost -- not on a set percentage of the column. The existence of covering indexes is one way to disprove the "rule". Another is the fact that a clustered index can easily be chosen to return values based on SARGs and joins of columns. While the "20% rule" may have seemed to be a reasonable rule of thumb at the time it began, it has never accurately described the optimizer's behavior.
    Thanks,

    Matt

  2. #2
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    Interesting!

    A similar rule is quoted for Oracle. I wonder if that's a myth too?

  3. #3
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97

    Re: Did You Know: MYTH! "If >20% of a table is returned by a query it will table scan."

    Originally posted by MattR
    Taken from 1-800-Database.com
    Did you know that the following is a myth commonly believed to be true of the Sybase ASE query optimizer? "If more than 20% of a table is returned by a query, the only choice the optimizer has is to table scan."
    I've read a nice article about this. It was published in
    the ISUG Technical Journal by Eric Miner. It can be
    downloaded from
    http://www.sybase.com/detail/printth...013270,00.html

    Regards,
    Mariano Corral

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •