Infolinks

Friday 29 June 2012

Oracle Unicode Data Types

Oracle Unicode Data Types

Oracle supports Unicode data through NCHAR,NVARCHAR2, and NCLOB datatypes. (in Version 8 and above)
Unicode character data may be stored in a database even if the database character set is not Unicode. The exact character set used by NCHAR/NVARCHAR2 may vary depending on the setting of the national character set when the database is created.
The datatypes CLOB and NCLOB can store up to 4 gigabytes of character data or national char. set data. LOB datatypes replace the depracated LONG datatype.
The BFILE datatype stores unstructured binary data in operating-system files outside the database.
A BFILE column or attribute stores a file locator that points to an external file containing the data.
Full list of Datatypes
Date formats
For input and output of dates, the standard Oracle default date format is DD-MON-YY, ('13-NOV-92')
You can change this default date format for an instance with the parameter NLS_DATE_FORMAT.
You can also change it during a user session with the ALTER SESSION statement.
Time formats
To account for time zone differences, the new datatype 'TIMESTAMP WITH LOCAL TIME ZONE'
Can be used to store time zone adjusted data
An example:
SQL>
create table timetest (
col1 timestamp,
col2 timestamp with time zone,
col3 timestamp with local time zone);
insert into timetest values (sysdate,sysdate,sysdate);
commit;
select * from timetest;
alter session set time_zone='America/New_York';
select * from timetest;
See the SQL Reference manual for full details about this syntax.
Nulls Indicate Absence of Value
A null is the absence of a value. Nulls indicate missing or unknown data. A null should not be used to imply any other value, such as zero.
A column allows nulls unless a NOT NULL or PRIMARY KEY integrity constraint has been defined for the column.
Nulls columns generally require 1 byte to store the length of the column (zero).
Trailing nulls (at the end of a row) require no storage because a new row header signals that the remaining columns in the previous row are null. For example, if the last three columns of a table are null, no information is stored for those columns.
To identify nulls in SQL, use the IS NULL predicate.
Use the SQL function NVL to convert nulls to non-null values.

A bitmap index will include any NULLs, a binary tree index won't.
Column Order
Try to place columns that frequently contain nulls last so that rows take less space.
Note, the benefits of this are lost if the table includes a LONG column (so use BLOB instead)
Default Values for Columns
You can assign a default value to a column so that when a new row is inserted and a value for the column is omitted a value is supplied automatically. If a default value is not explicitly defined for a column, then the default for the column is implicitly set to NULL.
create table ACCOUNTS(
AC_ID_PK number primary key,
AC_COUNTRY_ID number default 44,
AC_CREATED date default sysdate,
)

No comments:

Post a Comment