indexing VARCHAR vs. NUMBER datatype
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: indexing VARCHAR vs. NUMBER datatype

Hybrid View

  1. #1
    Join Date
    Jul 2000
    Posts
    68
    all-

    I have never received an answer to this question, though I've asked many people. Hopefully somebody can help!

    For performance reasons, I always create indexes on "NUMBER" datatype columns. This is because I thought that it was faster & more efficient.

    Is this correct?

    Is it better to index on NUMBER type datacolumns, or is there no difference between them and VARCHAR/CHAR type data columns when indexing?

    And, in continuation, what is the best datatypes to index on? Does it matter at all? (Obvoiusly you can't index on BLOB types, but other than those...)

    thanks much!

  2. #2
    Join Date
    Jun 2001
    Posts
    20
    hi,
    I don't think it matters much what datatype the column is..it's more about the data pattern in that particular field.

    I generally don't index fields with non-repeating data..for example, a number field that represented a group number would be ok to index, but that same field containing unique user numbers probly wouldn't...the server would have to search the index, which would reference the table, taking up clock cycles.

    in the above example, though, I wouldn't use a number field anyway...i would use a varchar2..

    I reckon it boils down to this:

    disregard the datatype(other than blob, you're right), pay more attention to the pattern of data in your field.

    I assume you're talking about non-unique indexes here, used to speed up reports and such..unique indexes follow different rules.

  3. #3
    Join Date
    Jun 2001
    Posts
    20
    addendum:

    after re-reading your post I felt I should add - you're probly slowing down your DB by building indexes willy-nilly on every number field. reference my above statement on data patterns.

  4. #4
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    You can degrade the performance of your database by overindexing. You should expect to see gains when you index and remove the newly created indexes if you don't.

    The space involved in creating index also involves maintenance. In addition, indexes on tables that are updated or deleted frequently should be rebuilt when they become fragmented. This takes some time and attention.

    There are two types of indexes that are commonly used. The bitmap index is used when a column has very few distinct values (5% of the overall rows) and is part of table that is not frequently updated.

    A B-Tree index is used for columns in which no one value has more than 20% of the columns, but there are many different values in the column.

    It's a long answer, but datatype of NUMBER vs VARCHAR shouldn't matter.

    Good luck.
    David Knight
    OCP DBA 8i, 9i, 10g

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    WWALICE,

    Indexing on NUMBER data type is faster than indexing on VARCHAR2 data type. Not only Oracle but also other RDBMSs search the index object (indexed on NUMBER datatype) faster than VARCHAR2 datatype. Remember if a sequence number is used on the indexed column, then the index is moving towards one side, and the search takes longer time to find appropriate key values. In the values are entered randomly, then the index on number data type is good.

    If an index is created on the CHAR datatype column, then SQL statement must have its full value in the where clause in order to use the index, otherwise index will not be used. For ex, an index is created on NAME CHAR(20) , and you issue a statement like,
    SELECT * from emp where name = 'SCOTT';
    the index will not be used since query has less than 20 characters in the constant value.

  6. #6
    Join Date
    Jul 2000
    Posts
    68
    Thanks, all.

Posting Permissions

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



Click Here to Expand Forum to Full Width