Re: Compare Index on Number & Varchar2

I had a similar question a while back. Specifically my question was, is it faster to join on a numeric-based index or a varchar(2) based index.

After much research, and a discussion with an Oracle P&T friend of mine, the answer was/is: "It depends." There is no right answer; your results will vary based on your specific situation and your data.

suggestion: create both test cases, set on the autotracing and timing, and compare results.

boss

>
> One of the column in a new table can be ename - varchar2(20) or hase_code_e=
> name - number(11) =96 Hash Code generated by JAVA. =0AWe are going to creat=
> e non-unique index on this column as one frequent query will have where cla=
> use on this column only. Choice is either varchar2(20) or number(11).=0A1. =
> Is Non-unique index on Number is faster then varchar2? Why?=0A2. If Number =
> column has negative values, then will it affect performance of Non-unique i=
> ndex on it? =0A3. How much space Number & Varchar2 consume? i.e storage spa=
> ce difference between number(10) and varchar2(10)=0A=0ATillu=0A=0A
> --Next_1070355278---0-202.54.124.178-31576--
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: sahil patel
> INET: tosahil_at_rediffmail.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Todd Boss
  INET: boss_at_i-sphere.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).