Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: June 02, 2003 - 9:12 pm UTC

Last updated: February 02, 2005 - 5:14 am UTC

Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I have this question about field length of IDs. For example our table has 12 digits customer_id. I would like to store them as varchar2(12) since those IDs are assigned by us and we know the exact length.

However, some members of my team want to make the storage bigger like varchar2(15) or varchar2(20). They argue that we should reserve more space for future use. Since the ID is program generated, errors such as writing more digits will not happen.

I against those idea. I think we can store fields like address, remarks with a bigger fields. But for ID, we must make it exact. If years in the future and we need to increase the length, a simple alter table will do it. Otherwise it is very confusing for users who look at the table structure. They can't tell how long is the ID.

What is your take on this issue. Are there problems with assigning IDs to a bigger fields?

Thanks.

-- Kim

and Tom said...

if they are 12 DIGITS, number seems to be more appropriate. it would take 1/2 the space as well.

You can always alter the table and make the column LARGER in the future. do it right, do it at 12, and if need be later, a simple ALTER will increase it.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Kim, June 03, 2003 - 8:35 am UTC

Hi Tom,
But number will not allow leading 0s right? May be can take postal code as example.

Tom Kyte
June 03, 2003 - 9:31 am UTC

that is formatting, to_char deals with formatting numbers.

EXACT LENGTH OF NUMBER FIELD

umesh, June 03, 2003 - 8:37 am UTC 

SQL> 
SQL> 
SQL> CREATE TABLE T
  2  ( ID NUMBER);

Table created.

SQL> 
SQL> INSERT INTO T VALUES ( 12345678901234567890123456789012345678);

1 row created.

SQL> INSERT INTO T VALUES ( 123456789012345678901234567890123456789);

1 row created.

SQL> INSERT INTO T VALUES ( 1234567890123456789012345678901234567890);

1 row created.

SQL> INSERT INTO T VALUES ( 12345678901234567890123456789012345678901234567890);

1 row created.

SQL> INSERT INTO T VALUES ( 1234567890123456789012345678901234567890123456789012345678901234567);

1 row created.

SQL> SELECT DATA_LENGTH FROM USER_TAB_COLUMNS
  2  WHERE TABLE_NAME='T';

DATA_LENGTH                                                                     
-----------                                                                     
         22                                                                     

SQL> DESC USER_TAB_COLUMNS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COLUMN_NAME                               NOT NULL VARCHAR2(30)
 DATA_TYPE                                          VARCHAR2(106)
 DATA_TYPE_MOD                                      VARCHAR2(3)
 DATA_TYPE_OWNER                                    VARCHAR2(30)
 DATA_LENGTH                               NOT NULL NUMBER
 DATA_PRECISION                                     NUMBER
 DATA_SCALE                                         NUMBER
 NULLABLE                                           VARCHAR2(1)
 COLUMN_ID                                 NOT NULL NUMBER
 DEFAULT_LENGTH                                     NUMBER
 DATA_DEFAULT                                       LONG
 NUM_DISTINCT                                       NUMBER
 LOW_VALUE                                          RAW(32)
 HIGH_VALUE                                         RAW(32)
 DENSITY                                            NUMBER
 NUM_NULLS                                          NUMBER
 NUM_BUCKETS                                        NUMBER
 LAST_ANALYZED                                      DATE
 SAMPLE_SIZE                                        NUMBER
 CHARACTER_SET_NAME                                 VARCHAR2(44)
 CHAR_COL_DECL_LENGTH                               NUMBER
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 AVG_COL_LEN                                        NUMBER

SQL> ED
Wrote file afiedt.buf

  1  SELECT DATA_PRECISION,DATA_SCALE, DATA_LENGTH  FROM USER_TAB_COLUMNS
  2* WHERE TABLE_NAME='T'
SQL> /

DATA_PRECISION DATA_SCALE DATA_LENGTH                                           
-------------- ---------- -----------                                           
                                   22                                           

SQL> ED
Wrote file afiedt.buf

  1  SELECT DEFAULT_LENGTH, DATA_PRECISION,DATA_SCALE, DATA_LENGTH  FROM USER_TAB_COLUMNS
  2* WHERE TABLE_NAME='T'
SQL> /

DEFAULT_LENGTH DATA_PRECISION DATA_SCALE DATA_LENGTH                            
-------------- -------------- ---------- -----------                            
                                                  22                            

SQL> SPOOL OFF


WHAT EXACTLY IS DATA_LENGTH IN IT ? I AM ABLE TO INSERT MORE THAT 22 DIGITS IN IT ?
TOM , FROM DICTIONARY TABLES  I WANT TO KNOW THE WIDTH OF THE COLUMN 
THANKS IN ADVANCE

Tom Kyte
June 03, 2003 - 9:38 am UTC 

YOUR CAPSLOCK KEY IS STUCK.


22 is the number of bytes it might take at most to store a number.  A number in Oracle is stored in a format similar to a packed decimal with 2 digits per byte.

If you want to know the actual width of a value of a number (it is a varying length thing really) you would use VSIZE on an actual number:

ops$tkyte@ORA920> create table t ( x number );

Table created.

ops$tkyte@ORA920> begin
  2     for i in 14 .. 38
  3     loop
  4      insert into t values ( rpad('9',i,'9') );
  5      insert into t values ( rpad('9',i,'0') );
  6     end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> set numformat 9999999999999999999999999999999999999999
ops$tkyte@ORA920> column vs format 999
ops$tkyte@ORA920> select x, vsize(x) vs
  2    from t
  3   order by x
  4  /

                                        X   VS
----------------------------------------- ----
                           90000000000000    2
                           99999999999999    8
                          900000000000000    2
                          999999999999999    9
                         9000000000000000    2
                         9999999999999999    9
                        90000000000000000    2
                        99999999999999999   10
                       900000000000000000    2
                       999999999999999999   10
                      9000000000000000000    2
                      9999999999999999999   11
                     90000000000000000000    2
                     99999999999999999999   11
                    900000000000000000000    2
                    999999999999999999999   12
                   9000000000000000000000    2
                   9999999999999999999999   12
                  90000000000000000000000    2
                  99999999999999999999999   13
                 900000000000000000000000    2
                 999999999999999999999999   13
                9000000000000000000000000    2
                9999999999999999999999999   14
               90000000000000000000000000    2
               99999999999999999999999999   14
              900000000000000000000000000    2
              999999999999999999999999999   15
             9000000000000000000000000000    2
             9999999999999999999999999999   15
            90000000000000000000000000000    2
            99999999999999999999999999999   16
           900000000000000000000000000000    2
           999999999999999999999999999999   16
          9000000000000000000000000000000    2
          9999999999999999999999999999999   17
         90000000000000000000000000000000    2
         99999999999999999999999999999999   17
        900000000000000000000000000000000    2
        999999999999999999999999999999999   18
       9000000000000000000000000000000000    2
       9999999999999999999999999999999999   18
      90000000000000000000000000000000000    2
      99999999999999999999999999999999999   19
     900000000000000000000000000000000000    2
     999999999999999999999999999999999999   19
    9000000000000000000000000000000000000    2
    9999999999999999999999999999999999999   20
   90000000000000000000000000000000000000    2
   99999999999999999999999999999999999999   20

50 rows selected.

can we get the length from dictionary tables

umesh, June 04, 2003 - 11:54 pm UTC

Tom,
I did get the VSIZE to work. Is it possible to query a dictionary table to get the maximum width associated with a column
i.e
1) In EMP table if ENAME is VARCHAR2(12)
i need to get 12
2)If EMPNO is NUMBER
I need to get it maximum width
becoz
desc EMP will give the structure of table
against all varchar2 columns i get associated width
but will not display any width against number if you have not specified any ,while creating the table .

For number I think the default width is 38 digits

Thanks for your patience



Tom Kyte
June 05, 2003 - 7:42 am UTC

look at data_length in user_tab_columns.

It'll show 22 (max bytes needed to store) for a number(38) and data_precision/data_scale will be null.

You could always NVL(data_precision,38) as well from that table

Length of key fields

A Reader, June 05, 2003 - 8:23 am UTC

Tom,

I've recently heard people debating about whether (if you have a choice) it is slower/'worse'/bad practice to say store:

TOM KYTE
JOHN DOE
JANE SMITH

as primary keys when you can store say:

TK
JD
JS

(ignoring for the moment the fact that maintaining uniqueness may be an issue).

Is it true that it is less efficient when querying if they key field is 'wider' ?

Regards,

Paul

Tom Kyte
June 05, 2003 - 8:43 am UTC

keys should be keys.

I use surrogates frequently -- because many objects don't have natural keys, but when a natural key exists, use it.

Forget the length for a moment, the difference between a 10 and 15 byte compare isn't going to be significant in the grand scheme of things. Its all about the data. Store the data as the data should be stored.

OK

Nancy, March 26, 2004 - 5:28 am UTC

Hi Tom,
Is there any way to find the length of the sql statement in
kb.Can the following one do?
sql>select round(length(select ...from emp where ..)/1024)
as length_sql_in_kb from dual;
Is this a correct and valid approach?
Could you please check this?
Thanks in advance.


Tom Kyte
March 26, 2004 - 9:25 am UTC

sure, you could just


exec dbms_output.put_line( length('...')/1024 )


as well. (seems like a funny metric to have to collect though, curious as to "why" it matters...)



data length

Reader, February 02, 2005 - 4:27 am UTC 

Tom,

Pl.look at the following:

SQL> desc testlength
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(3)
 B                                                  VARCHAR2(20)
 C                                                  NUMBER(7,2)

SQL>  select column_name,data_length,data_precision,data_scale
  2   from user_tab_columns where table_name='TESTLENGTH';

COLUMN_NAM DATA_LENGTH DATA_PRECISION DATA_SCALE 
---------- ----------- -------------- ---------- 
A                   22              3          0
B                   20
C                   22              7          2

Why data_length is shown as 22 here for the number fields ( the default for number field of width 38) though the scale,precision are mentioned in the table definition?

What is the maximum record size in the above table?

To calculate the maximum record length, in the above example, we take the varchar2 as such (20 bytes) plus the half the space required for the numeric field (column a=2 bytes and column c=4 bytes). Hence, the maximum record length is calculated as 20+2+4=26 bytes. Is this right? Like to know that 26 bytes are sufficient to store a record with the maximum width specified in the table definition?

Rgds.

Tom Kyte
February 02, 2005 - 5:14 am UTC 

because a number an take upto 22 bytes, that is what this is saying.  Your numbers - no, but numbers take upto 22 bytes.

you care about the precision and scale for numbers, not the data length.


ops$tkyte@ORA9IR2> create table t ( a number(3), b varchar2(20), c number(7,2) );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( -999, rpad('x',20,'x'), -99999.99 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select vsize(a), vsize(b), vsize(c) from t;
 
  VSIZE(A)   VSIZE(B)   VSIZE(C)
---------- ---------- ----------
         4         20          6
 


to the 4, 20 and 6 you must add a null flag (1 byte each) and to the varchar2, a leading length byte.

so, 4+20+6+3+1 = 34

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.