0

What is the Data length for the below 4 columns in the Employee table?

Table : Employee

Column_Name ----- Data_Type ------- Nullable ---- Column_Id

Emp_ID           VARCHAR2(10 BYTE)    No         1
Emp_Name         VARCHAR2(100 CHAR)   No         2
Emp_Notes        VARCHAR2(1000)       No         3
Emp_det_created  TIMESTAMP(9)         No         4

The table structure is as below

CREATE TABLE employee
  (    Emp_ID VARCHAR2(10 BYTE) NOT NULL ENABLE,       
Emp_Name VARCHAR2(100 CHAR) NOT NULL ENABLE,
Emp_Notes VARCHAR2(1000) NOT NULL ENABLE,       
Emp_det_created TIMESTAMP (9) NOT NULL ENABLE,
 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
 STORAGE(INITIAL 1000 NEXT 1948506 MINEXTENTS 1 MAXEXTENTS 2100000045
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "EMPL"  ENABLE
  ) SEGMENT CREATION IMMEDIATE
 PCTFREE 10 PCTUSED 20 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
 STORAGE(INITIAL 1000 NEXT 1948506 MINEXTENTS 1 MAXEXTENTS 2100000045
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE EMPL ;

O/P: select * from Employee ;

Emp_Id ---- Emp_Name---- Emp_Notes--- Emp_det_created

A10 -- Claire -- Good given --- 04/01/2015

A20 -- Rosey--- Plain text ---- 03/25/2016

A30 -- Aven-- Plain message ---- 05/01/2017

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Anitha
  • 77
  • 1
  • 2
  • 9
  • Why do you need to know? Are you really worried if a `TIMESTAMP` is [11 or 20 bytes](https://stackoverflow.com/a/62031628/1509264)? Do you care if the `VARCHAR2(10 BYTE)` has a maximum size on the disk of maybe 11 or 12 bytes if you are mostly going to be storing smaller strings in it and it is going take up less space? Do you know what character set you are using and are you storing extended characters or just ASCII? – MT0 Jun 12 '20 at 15:14
  • Why are you using quoted names for the columns? And more than that, why are you using quoted names that contain spaces? [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=89b053d35bcabad3c2f6cdd530bd9395) – MT0 Jun 12 '20 at 15:20
  • Yes.. I need to know the max sizes of the data length. Is this correct? Timestamp(9) = 11 bytes.. How about the other 3 fields of varchar2(10) = 10bytes ? and varchar2(1000) = 1000 bytes ? And about the varchar2(100char)? – Anitha Jun 12 '20 at 15:22
  • It depends .... what character set are you using in the database? – MT0 Jun 12 '20 at 15:23
  • Basically there are no spaces in the table DDL.. when i tried to type here.. it came up – Anitha Jun 12 '20 at 15:24
  • @Anitha: ok, that's good. Please, please edit your post an remove the spaces. For you it's fine, but other people view your question and start using spaces in names, which will cause errors that are fiendishly hard to find. – wolφi Jun 12 '20 at 15:29
  • I have edited it. – Anitha Jun 12 '20 at 15:32
  • Thanks, that is very kind of you! – wolφi Jun 12 '20 at 15:34
  • Thank You!. Our is Unicode character set that we are using in the database. – Anitha Jun 12 '20 at 15:39

1 Answers1

2

The CREATE TABLE statement declares the maximum length of variable-length datatypes.

If you want to see the actual length a column in a specific row takes, you can use the DUMP function:

SELECT emp_id, 
       dump(emp_id), 
       dump(emp_name), 
       dump(emp_notes), 
       dump(emp_det_created)
  FROM employee;

A10 Typ=1 Len=3: 65,49,48   Typ=1 Len=5: 67,108,97,105,114  Typ=1 Len=10: 71,111,111,100,32,103,105,118,101,110 Typ=180 Len=7: 120,115,4,1,1,1,1
A20 Typ=1 Len=3: 65,50,48   Typ=1 Len=5: 82,111,115,101,121 Typ=1 Len=10: 80,108,97,105,110,32,116,101,120,116  Typ=180 Len=7: 120,116,3,25,1,1,1
A30 Typ=1 Len=3: 65,51,48   Typ=1 Len=4: 65,118,101,110 Typ=1 Len=13: 80,108,97,105,110,32,109,101,115,115,97,103,101   Typ=180 Len=7: 120,117,1,5,1,1,1

So, your timestamp column (Typ=180) uses 7 bytes.

If you want only the length, you can also use the VSIZE function:

SELECT vsize(emp_notes) FROM employee;
10
10
13

As databases are good on calculating statistics, you can even get the minimum, maximum, average length:

SELECT min(v), avg(v), max(v)
  FROM (SELECT vsize(emp_notes) AS v FROM employee);

MIN(V) AVG(V) MAX(v)
    10     11     13

For the whole table, Oracle keeps the average column length in a statistics table. This is done every night at 22:00, if you don't want to wait, you can call (null is the current schema, you can also specify your schema name):

EXEC DBMS_STATS.GATHER_TABLE_STATS(null, 'EMPLOYEE');

Then you can inspect the average lengths with

SELECT column_name, data_type, data_length, avg_col_len
  FROM user_tab_cols 
 WHERE table_name='EMPLOYEE';

COLUMN_NAME      DATA_TYPE  DATA_LENGTH AVG_COL_LEN
EMP_ID           VARCHAR2            10           4
EMP_NAME         VARCHAR2           400           6
EMP_NOTES        VARCHAR2          4000          12
EMP_DET_CREATED  TIMESTAMP(9)        11          11

According to VSIZE, the length of a TIMESTAMP depends on the actual data:

INSERT INTO employee VALUES('A40','A','day',           DATE      '2017-01-05');
INSERT INTO employee VALUES('A41','A','day',           TIMESTAMP '2017-01-05 00:00:00');
INSERT INTO employee VALUES('A42','A','day and time',  TIMESTAMP '2017-01-05 17:50:00');
INSERT INTO employee VALUES('A43','A','and frac secs', TIMESTAMP '2017-01-05 17:50:00.1');

SELECT emp_notes, dump(emp_det_created) 
  FROM employee WHERE emp_id between 'A40' and 'A43';

date          Typ=180 Len=7: 120,117,1,5,1,1,1
day           Typ=180 Len=7: 120,117,1,5,1,1,1
day and time  Typ=180 Len=7: 120,117,1,5,18,51,1
and frac secs Typ=180 Len=11: 120,117,1,5,18,51,1,5,245,225,0
wolφi
  • 8,091
  • 2
  • 35
  • 64