If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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.

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.