Re: Best data Type for Primary Keys

dean_attewell_at_hotmail.com (Dean Attewell) wrote in message news:<3bda48ba.0211262201.5618d2ea_at_posting.google.com>...
> Is a VARCHAR2 as fast or as better than a NUMBER datatype?
>
> or what is the best datatype to use?
>
> Thanks
> Dean

Hi,

First thing First, If your data is going to be purely numbers then go for NUMBER and its Characters/Mixed then VARCHAR2.

Regarding Fastness both are same when you do a FTS. Here is an example.

SQL> create table t (oid varchar2(10)) ; Table created.

SQL> insert /*+ append */ into t
  2 select object_id from all_objects ;

21923 rows created.

SQL> commit ;

Commit complete.

SQL> create table t1 as select object_id from all_objects   2 where 1=0 ;

Table created.

SQL> insert /*+ append */ into t1
  2 select object_id from all_objects ;

21924 rows created.

SQL> commit ;

Commit complete.

SQL> desc t ;

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 OID                                                VARCHAR2(10)

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 OBJECT_ID                                 NOT NULL NUMBER

SQL> set autotrace traceonly
SQL> select * from t ;

21923 rows selected.

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (FULL) OF 'T' Statistics

          0  recursive calls
         12  db block gets
       1497  consistent gets
         34  physical reads
          0  redo size
     442494  bytes sent via SQL*Net to client
     162596  bytes received via SQL*Net from client
       1463  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      21923  rows processed

SQL> select * from t1
  2 ;

21924 rows selected.

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (FULL) OF 'T1' Statistics

          0  recursive calls
         12  db block gets
       1497  consistent gets
         34  physical reads
          0  redo size
     420686  bytes sent via SQL*Net to client
     162596  bytes received via SQL*Net from client
       1463  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      21924  rows processed

But the results may vary if you use a WHERE clause. Check for the other possibilites.

Regards,
Sagi. Received on Wed Nov 27 2002 - 18:58:59 CET