Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    1

    Unanswered: Key Fields better if Numeric?

    Someone told me that in Oracle, index creation and query performance (based on indices) will be much faster if the key field (on which index is there) is numeric.
    I looked all over the place, didnt find anything to support or dispute this.

    Is this true?

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    taking this to a really simplistic level... a NUMBER(3) can easily be stored in two physical bytes (max decimal value = 999, in binary less than two bytes).

    To store the same range of values in a varchar2 column would require 4 bytes (1 for length and up to 3 for data).

    So, an index scan on the same column stored in number(3) could potentially require as little as 50% of the disk reads.

    Yes, it's a very watered down version of the facts, but yes your info is generally/mostly correct.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hey,

    Can any one prove this by any small case study. This fact was not none
    to me that index create on numeric are better than index on character fields

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  4. #4
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hi,

    my expirience:
    with numeric fields you have the possibility to bring your database into being more uniform.
    In this way you can better organize your triggers, functions, procedures ..
    Regards,
    Julia

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If you are going to use surrogate keys, use a NUMBER. As Bill says this means it will typically be smaller than a VARCHAR2, and so provide performance benefits. Also, you will likely use a SEQUENCE to generate the values, which will be numbers.

    But this is only relevant when defining surrogate keys. If your table has a natural key which is a code or a name, then you are going to have to index that code or name anyway, either as the primary or as a unique key. I wouldn't advocate adding surrogate numeric keys to every table that would otherwise have a perfectly reasonable VARCHAR2 key - though I know some people would slavishly do so!

  6. #6
    Join Date
    Oct 2003
    Posts
    87
    I think you'll discover there's really very little performance to be gained, if any, by choosing a datatype for an index that stores in the fewest bytes possible; and it really has no bearing on good relational design anyway.

    Its sometimes surprising how things REALLY work. First, study how a B-Tree organized index is searched; second, study how index keys are compressed.
    Oracle - DB2 - MS Access -

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Indexes are used for more than pure single key searches where a B-Tree index is efficient. Key compression applies to both numeric and varchar columns.

    Going back to my earlier example, a 50 million row table using the number column for the index would result in 100Mb of indexed data. Using a varchar2 column for the index would result in 200mb of indexed data (these figures for comparison, not actual volumes). I appreciate there is a little more to this than those simple maths. As paginit suggested, maybe someone could experiment with various numeric/varchar combinations?

    When an index range scan, or a fast full scan is applied to a 100Mb or a 200Mb index, performance differences will be quite noticeable.

    Finally, Oracle also has to apply the current NLS_SORT parameter to a varchar column when reading an index, I believe this is done on the fly and computationally expensive.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  8. #8
    Join Date
    Oct 2003
    Posts
    87
    Originally posted by billm
    Hi,

    Indexes are used for more than pure single key searches where a B-Tree index is efficient. Key compression applies to both numeric and varchar columns.

    Going back to my earlier example, a 50 million row table using the number column for the index would result in 100Mb of indexed data. Using a varchar2 column for the index would result in 200mb of indexed data (these figures for comparison, not actual volumes). I appreciate there is a little more to this than those simple maths. As paginit suggested, maybe someone could experiment with various numeric/varchar combinations?

    When an index range scan, or a fast full scan is applied to a 100Mb or a 200Mb index, performance differences will be quite noticeable.

    Finally, Oracle also has to apply the current NLS_SORT parameter to a varchar column when reading an index, I believe this is done on the fly and computationally expensive.

    Hth
    Bill
    I would agree with the index scan taking a little longer provided the varchar key didn't compress well . . . thanks for mentioning that, Regards . . .
    Oracle - DB2 - MS Access -

Posting Permissions

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