|
|
Unicode Data types
Oracle supports Unicode data through NCHAR,NVARCHAR2, and
NCLOB datatypes. (Version 8 and above)
These data types are guaranteed to be Unicode encoding and always use character length semantics. The character sets used by NCHAR/NVARCHAR2 can be either UTF8 or AL16UTF16, depending on the setting of the national character set when the database is created.
Unicode character data may be stored in a database even if the database character set is not Unicode.
The datatypes CLOB and NCLOB can store up to 4 gigabytes of character data or national char. set data. LOB datatypes are intended to replace the 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.
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,
)
"The estimated cost of the National ID scheme is £3.1bn, Chris Pond
MP, has put benefit fraud related to false ID at £50m." - The
Register
See also
Full list of Datatypes
