Re: Indexing by number or by varchar2

This is a very good question, and one that is often debated. Here is my approach:

1. For master-level tables, use meaningful PK's (ie. varchar2 with real values). Varchar2 will take up almost twice as much storage as a same-size number column, but most master-level tables aren't all that large anyway. You find that by using meaningful PK's in master-level tables, you'll have fewer joins to do.
2. For detail-level or transactional tables, use artificial PK's (ie. number columns, preferably with the value inserted from a sequence using a before-insert trigger). Since transactional tables tend to grow large, the number columns works out well since the PK index will be half the size of a similar PK index on a varchar2 column.

This is the guideline I use, but your situation might be different. for instance, if user's will need to change what would otherwise be the PK in a parent table, then use artificial keys in all the tables. If you want to prevent user's from changing the PK in parent tables, then use meaningful keys.

Hope this helps,
Dave

Inaki Agirre wrote in message <35211663.379D7D0_at_si.unirioja.es>...
>Quick question
>
>If I must implement some codification, does it pay to define the primary
>column as number or could I use a meaningful varchar2, say, (3) length.
>--
>Inaki Ekaitz Agirre
>inaki.agirre_at_si.unirioja.es
Received on Thu Apr 02 1998 - 00:00:00 CST