Re: Performance Difference between NUMERIC and VARCHAR2 attribute

On 15-Nov-98 14:46:25 Tony Jambu wrote:

>Hi all

Hi Tony.

>Does anyone have real-world experience (not something you read) with
>regards to, whether a NUMERIC column will provide performance
>improvement over VARCHAR2 column?

>Taking into considreration :
>- using column in a comparison
>- file storage
>- index creation on column

>My brief test shows no improvement

I experienced a difference concerning the order by clause. When you issue something like

select * from your_table
order by numeric_column;

the optimizer uses an index on numeric_column. If you do

select * from your_table
order by varchar2_column;

the optimizer will *not* use an index (unless you have set nls_sort to binary perhaps). This is because numeric columns are sorted equally allover the world but every languale has its own sorting rule for characters.

>If a numeric column provides better performance in a predicate clause,
>would you then create a surrogate for the varchar column that is the
>PK?

Well, if you use the PK for sorts: yes. (but who sorts lists by customer_id and not by name, ZIP, state or the like? :-)

I don't know, if there are performance differences wher you just use the PK to identify and find rows in a table though...

>Thanks in adv. for you help

>tony

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |