Re: Index on Number & Varchar2

Tillu wrote:
>
> One of the column in a new table can be ename - varchar2(20) or
> hase_code_ename - number(11) – Hash Code generated by JAVA.
> We are going to create non-unique index on this column as one frequent
> query will have where clause on this column only. Choice is either
> varchar2(20) or number(11).
> 1. Is Non-unique index on Number is faster then varchar2? Why?

Can't think why it would be. The 'speed' of an index will be determined by how many index levels you have to navigate to find a leaf block containing rowids for your value. There may be issues with lots of leaf block splits depending on how your data is populated in which case you *might* argue for one column over the other, but it'll have nothing to do with data type, more the cardinality and distribution of the data.

> 2. If Number column has negative values, then will it affect
> performance of Non-unique index on it?

Can't think why it would. I'd say no.

> 3. How much space Number & Varchar2 consume? i.e storage space
> difference between number(10) and varchar2(10)
>

Number 10 takes 6 bytes, varchar2(10) takes 10 bytes, assuming all positions are used.

Varchar2(20) only takes 10 bytes if it's only storing 10 characters.

Add about 5% overhead to size a B*-tree index.

If your table is very infrequently updated consider using bitmap indexes. They have the potential to build much smaller than B*-trees as the number of distinct values drops.  

> Sahil
Received on Tue Dec 02 2003 - 03:53:18 CST