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...)
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.
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.
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.
OCP DBA 8i, 9i, 10g
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.
Click Here to Expand Forum to Full Width