Data types for Oracle 8 - Oracle 11g + PL/SQL

Notes and Examples

VARCHAR2:
Storing character data as Varchar2 will save space:

Store ''SMITH'' not ''SMITH     ''

Oracle9i and above allow Varchar2 columns to be defined as a number of bytes VARCHAR2(50 BYTE) or a number of characters VARCHAR2(50 CHAR), the latter is useful if the database is ever converted to run a double-byte character set (such as Japanese), you won''t have to edit the column sizes. The default measure, normally BYTE, is set with nls_length_semantics.

CHAR:
Over time, when varchar2 columns are updated they will sometimes create chained rows, CHAR columns are fixed width they are not affected by this, so less DBA effort is required to maintain performance.

PL/SQL
When retrieving data for a NUMBER column, consider using the PL/SQL datatype: PLS_INTEGER for better performance.

LONG
Use BLOB instead of LONG

INTEGER
This ANSI datatype will be accepted by Oracle - it is actually a synonym for NUMBER(38)

The FLOAT datatype
This ANSI datatype will be accepted by Oracle - Very similar to NUMBER it stores zero, positive, and negative floating-point numbers

The NUMBER datatype
Stores zero, positive, and negative numbers, fixed or floating-point numbers

Fixed-point NUMBER
NUMBER(p,s)
precision p = length of the number in digits
scale s = places after the decimal point, or (for negative scale values) significant places before the decimal point.

Integer NUMBER
NUMBER(p)
This is a fixed-point number with precision p and scale 0. Equivalent to NUMBER(p,0)

Floating-Point NUMBER
NUMBER
floating-point number with decimal precision 38

Confusingly the Units of measure for PRECISION vary according to the datatype.
For NUMBER data types: precision p = Number of Digits
For FLOAT data types: precision p = Binary Precision (multiply by 0.30103 to convert)

{So FLOAT = FLOAT (126) = 126 x 0.30103 = approx 37.9 digits of precision.}

Example

 The value 7,456,123.89 will display as follows
NUMBER(9)     7456124 
NUMBER(9,1)   7456123.9
NUMBER(*,1)   7456123.9    
NUMBER(9,2)   7456123.89 
NUMBER(6)    [not accepted exceeds precision] 
NUMBER(7,-2)  7456100 
NUMBER        7456123.89 
FLOAT         7456123.89 
FLOAT(12)     7456000.0

Storing Varchar2 Data

For VARCHAR2 variable whose maximum size is less than 2,000 bytes (or for a CHAR variable), PL/SQL allocates enough memory for the maximum size at compile time.

For a VARCHAR2 whose maximum size is 2,000 bytes or more, PL/SQL allocates enough memory to store the actual value at run time. In this way, PL/SQL optimizes smaller VARCHAR2 variables for performance and larger ones for efficient memory use.

For example, if you assign the same 500-byte value to VARCHAR2(1999 BYTE) and VARCHAR2(2000 BYTE) variables, PL/SQL allocates 1999 bytes for the former variable at compile time and 500 bytes for the latter variable at run time.

Storing Numeric Data

Oracle stores all numeric data in variable length format - storage space is therefore dependent on the length of all the individual values stored in the table. Precision and scale settings do not affect storage requirements. DATA_SCALE may appear to be truncating data, but Oracle still stores the exact values as input. DATA_PRECISION can be used to constrain input values.

It is possible to save storage space by having an application truncate a fractional value before inserting into a table, but you have to be very sure the business logic makes sense.

Select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE
From cols Where table_name = ''Your_Table'';

A common space-saving trick is storing boolean values as an Oracle CHAR, rather than NUMBER:

Create TABLE my_demo (accountcode NUMBER, postableYN CHAR check (postableYN in (0,1)) );

-- Standard Boolean values: False=0 and True=1
Insert into my_demo values(525, ''1'');
Insert into my_demo values(526, ''0'');

Select accountcode, decode(postableYN,1,''True'',0,''False'') FROM my_demo;
-- or in French:
Select accountcode, decode(postableYN,1,''Vrai'',0,''Faux'') FROM my_demo;

Comparison with other RDBMS''s

Also consider the maximum length of a table name (or column name) and the maximum size of an SQL statement - these limits vary considerably between products and versions.

“Computer programmers do it byte by byte”

Related