Oracle Tables DDL

CREATE Table

Table placement in relation to other database objects is important for optimal database performance. Attempt to minimize contention by placing tables away from their associated indexes and away from other I/O-intensive database objects such as redo logs and rollback segments. For very large tables consider manually splitting tables up amongst several disks. This process would also require sizing the table extents such that they just fill a single tablespace datafile. User objects including tables should not be placed in the SYSTEM tablespace. The SYSTEM tablespace contains data dictionary objects. Serious datafile contention could result by placing user objects into the SYSTEM tablespace and can seriously affect performance.

Relational and Object Tables

Relational Table

Object Table

Relational and Object Properties

Physical Properties

LOB Parameters

Table Properties

Segments Clause

More Simple Forms of the CREATE TABLE Statement.

CREATE TABLE <table_name>
(
	<column_name 1> <datatype> [NOT NULL],
	<column_name 1> <datatype> [NOT NULL],
...
	<column_name n> <datatype> [NOT NULL];
)
... OR ... 
CREATE TABLE <table_name> AS [query];
... OR ... 
CREATE TABLE <table_name>
[(
	<column_name 1> <datatype> [NOT NULL],
	<column_name 1> <datatype> [NOT NULL],
...
	<column_name n> <datatype> [NOT NULL];
)]
AS [query];

Primary and Secondary Keys

FOREIGN KEY (column, column)
	REFERENCES table_name (column, column)
	CONSTRAINT constraint_name
PRIMARY KEY (column, column)
	USING INDEX TABLESPACE tablespace_name
	STORAGE (storage_clause)
	CONSTRAINT constraint_name

In the example above the foreign key constraint is enforced such that its values must match its corresponding primary or unique key values. No index is automatically generated. Indexes should be maintained on foreign keys or excessive full table scans may result. A primary key will automatically have an index generated named for the constraint. A primary key automatically forces its column or columns to be not null and if a single column, unique. For a composite set of columns, the resulting set must be unique. Foreign and primary key constraints are known as referential constraints. Referential integrity violations occur when a parent record is deleted and child records still exist. Additionally, user-defined constraints are used to enforce business rules.

Comments on Table Columns

Comments can be created on or attached to columns in tables as shown in the example below.

COMMENT ON COLUMN SALARY.LOWLIMIT IS 'Low limit of this salary grade';

ALTER Table

Add columns, change column types and lengths plus add, change or drop column constraints. Table columns can not be dropped or renamed. Table storage characteristics can be altered for subsequent extents or data blocks only. The CASCADE option with DISABLE or DROP clauses allows cascading to all dependant integrity constraints. DISABLE switches a constraint off; for PRIMARY and UNIQUE keys the indexes are dropped aswell. ENABLE or ADD turns a constraint on or creates it. With UNIQUE and PRIMARY KEY constraints an index is created. If the index clause is not specified the index is created in the default tablespace of the creator with the tablespace default storage characteristics.

ALTER TABLE <table_name> ADD (<column_name> <datatype> [NOT NULL]);
ALTER TABLE <table_name> MODIFY (<column_name> <datatype> [NOT NULL]);
RENAME <old_table_name> TO <new_table_name;>

ALTER TABLE allows for allocation of new extents with specification of size and placement in different tablespaces and deallocation of unused extents. When deallocating unused extents the bytes reserved for future inserts above the highest value are set using the KEEP clause of the DEALLOCATE clause. Space unused above the high-water mark can be deallocated. Space above the high_water mark cannot be deallocated even if that space is unused.

If a table increases in size at a rate making calculation of exact sizing difficult then increase PCTINCREASE. Do space calculations as accurately as possible and then apply a 50% unknown factor in order to allocate enough space and setting of PCTINCREASE to zero. Sometimes the only method of extreme table growth management is by setting PCTINCREASE to a high value.

DROP Table

LOCK TABLE Command

The LOCK TABLE command overrides automated locking and denies access to users for a table or view as long as the lock is in place. The table or view will remain ina locked state until transaction COMMIT or ROLLBACK. Thus the locking a table is transactional at the session level. Note that the locking a table blocks only WRITE access and not READ.

Index Organised Tables

Index organised tables have rows grouped in relation to a primary index. Clustering uses a BTree which stores the primary index and non-index columns. Primary index columns are stored at the beginning of the segment in the order the the columns within the primary index.

Advantages of Index Organised Tables

The OVERFLOW and INCLUDING Clauses

The optional OVERFLOW clause allows for the the placing of index columns exceeding a specified threshold into separate overflow segments thus preserving dense clustering of BTree index columns. The INCLUDING clause forces the storage of non-index columns into the overflow data segment. PCTTHRESHOLD reserves space in each index block for the index. Thus space is reserved in the index block for the indexes. Always choose a threshold value which accomodates index columns into the index segment and non-index columns into the overflow segment. Note that index organised tables are particulary efficient for storage management of binary objects, however, note that indexes cannot be anything but simple data types.

The COMPRESS Clause

The COMPRESS clause eliminates repeated occurences of index column prefix values. Prefix entries are shared amongst all suffix entries in an index block.

CREATE TABLE accounts
(
	account_type		CHAR(1)		NOT NULL,
	account_sub_type	CHAR(1)		NOT NULL,
	chart_of_account	NUMBER(4)	NOT NULL,
	name			VARCHAR2(32),
	debit_amount		NUMBER(13,2),
	credit_amount		NUMBER(13,2
	CONSTRAINT pk_accounts_index PRIMARY KEY (account_type, account_sub_type, chart_of_account)
)
ORGANIZATION INDEX COMPRESS TABLESPACE indx PCTTHRESHOLD 20 OVERFLOW TABLESPACE indxovfl;

Specifying Prefix Length

Prefix length is the number of index columns, ie. the number of columns stored within the index.

CREATE TABLE test (a INTEGER, b INTEGER, c INTEGER, d INTEGER, PRIMARY KEY (a, b, c)) ORGANIZATION COMPRESS 2;

[1,2,3],
[1,2,4],
[1,2,7],
[1,3,5],
[1,3,4],
[1,4,4]

... becomes ...

[1,2,
	[3,4,7]
],
[1,3,
	[5,4]
],
[1,4,
	[4]
]

... and ...

CREATE TABLE test (a INTEGER, b INTEGER, c INTEGER, d INTEGER, PRIMARY KEY (a, b, c)) ORGANIZATION COMPRESS 1;

[1,2,3],
[1,2,4],
[1,2,7],
[1,3,5],
[1,3,4],
[1,4,4]

... becomes ...

[1,
	[2,3],
	[2,4],
	[2,7],
	[3,5],
	[3,4],
	[4,4]
]

Index Organised Table Fragmentation

Changes to data can cause fragmentation. The ALTER TABLE MOVE statement can be used to rebuild index organised tables and thus reduce fragmentation. The example below will rebuild both the index and the overflow segments.

ALTER TABLE accounts MOVE TABLESPACE indx OVERFLOW TABLESPACE indxovfl