CREATE TABLE

Purpose

• column definitions

• integrity constraints

• the table's tablespace

• storage characteristics

• an optional cluster

• data from an arbitrary query

• degree of parallelism used to create the table and the default degree of parallelism for queries on the table

Prerequisites

Syntax

Keywords and Parameters

schema

is the schema to contain the table. If you omit schema, Oracle7 creates the table in your own schema.

table

is the name of the table to be created.

column

specifies the name of a column of the table. A table can have up to 254 columns. You may only omit column definitions when using the AS subquery clause.

datatype

is the datatype of a column. Datatypes are defined .

You can omit the datatype only if the statement also designates the column as part of a foreign key in a referential integrity constraint. Oracle7 automatically assigns the column the datatype of the corresponding column of the referenced key of the referential integrity constraint.

DEFAULT

specifies a value to be assigned to the column if a subsequent INSERT statement omits a value for the column. The datatype of the expression must match the datatype of the column. The column must also be long enough to hold this expression. For the syntax of expr, see . A DEFAULT expression cannot contain references to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants that are not fully specified.

column_constraint

defines an integrity constraint as part of the column definition. See the syntax description of column_constraint .

table_constraint

defines an integrity constraint as part of the table definition. See the syntax description of table_constraint .

PCTFREE

specifies the percentage of space in each of the table's data blocks reserved for future updates to the table's rows. The value of PCTFREE must be a value from 0 to 99. A value of 0 allows the entire block to be filled by inserts of new rows. The default value is 10. This value reserves 10% of each block for updates to existing rows and allows inserts of new rows to fill a maximum of 90% of each block.

PCTFREE has the same function in the commands that create and alter clusters, indexes, snapshots, and snapshot logs. The combination of PCTFREE and PCTUSED determines whether inserted rows will go into existing data blocks or into new blocks.

PCTUSED

specifies the minimum percentage of used space that Oracle7 maintains for each data block of the table. A block becomes a candidate for row insertion when its used space falls below PCTUSED. PCTUSED is specified as a positive integer from 1 to 99 and defaults to 40.

PCTUSED has the same function in the commands that create and alter clusters, snapshots, and snapshot logs.

The sum of PCTFREE and PCTUSED must be less than 100. You can use PCTFREE and PCTUSED together use space within a table more efficiently. For information on the performance effects of different values PCTUSED and PCTFREE, see Oracle7 Server Tuning.

INITRANS

specifies the initial number of transaction entries allocated within each data block allocated to the table. This value can range from 1 to 255 and defaults to 1. In general, you should not change the INITRANS value from its default.

Each transaction that updates a block requires a transaction entry in the block. The size of a transaction entry depends on your operating system.

This parameter ensures that a minimum number of concurrent transactions can update the block and helps avoid the overhead of dynamically allocating a transaction entry.

The INITRANS parameter serves the same purpose in clusters, indexes, snapshots, and snapshot logs as in tables. The minimum and default INITRANS value for a cluster or index is 2, rather than 1.

MAXTRANS

specifies the maximum number of concurrent transactions that can update a data block allocated to the table. This limit does not apply to queries. This value can range from 1 to 255 and the default is a function of the data block size. You should not change the MAXTRANS value from its default.

If the number concurrent transactions updating a block exceeds the INITRANS value, Oracle7 dynamically allocates transaction entries in the block until either the MAXTRANS value is exceeded or the block has no more free space.

The MAXTRANS parameter serves the same purpose in clusters, snapshots, and snapshot logs as in tables.

TABLESPACE

specifies the tablespace in which Oracle7 creates the table. If you omit this option, then Oracle7 creates the table in the default tablespace of the owner of the schema containing the table.

STORAGE

specifies the storage characteristics for the table. This clause has performance ramifications for large tables. Storage should be allocated to minimize dynamic allocation of additional space. See the STORAGE clause .

RECOVERABLE

specifies that the creation of the table (and any indices required because of constraints) will be logged in the redo log file. This is the default.

If the database is run in ARCHIVELOG mode, media recovery from a backup will recreate the table (and any indices required because of constraints). You cannot specify RECOVERABLE when using NOARCHIVELOG mode.

UNRECOVERABLE

specifies that the creation of the table (and any indices required because of constraints) will not be logged in the redo log file. As a result, media recovery will not recreate the table (and any indices required because of constraints).

This keyword can only be specified with the AS subquery clause. Using this keyword makes table creation faster than using the RECOVERABLE option because redo log entries are not written.

CLUSTER

specifies that the table is to be part of the cluster. The columns listed in this clause are the table columns that correspond to the cluster's columns. Generally, the cluster columns of a table are the column or columns that comprise its primary key or a portion of its primary key.

Specify one column from the table for each column in the cluster key. The columns are matched by position, not by name. Since a clustered table uses the cluster's space allocation, do not use the PCTFREE, PCTUSED, INITRANS, or MAXTRANS parameters, the TABLESPACE option, or the STORAGE clause with the CLUSTER option.

PARALLEL

specifies the degree of parallelism for creating the table and the default degree of parallelism for queries on the table once created. For more information, see the parallel_clause .

ENABLE

enables an integrity constraint. See the ENABLE clause .

DISABLE

disables an integrity constraint. See the DISABLE clause .

Constraints specified in the ENABLE and DISABLE clauses of a CREATE TABLE statement must be defined in the statement. You can also enable and disable constraints with the ENABLE and DISABLE keywords of the CONSTRAINT clause. If you define a constraint but do not explicitly enable or disable it, Oracle7 enables it by default.

You cannot use the ENABLE and DISABLE clauses in a CREATE TABLE statement to enable and disable triggers.

AS subquery

inserts the rows returned by the subquery into the table upon its creation. See the syntax description of subquery .

The number of columns in the table must equal the number of expressions in the subquery. The column definitions can only specify column names, default values, and integrity constraints, not datatypes. Oracle7 derives datatypes and lengths from the subquery. Oracle7 also follows the following rules for integrity constraints:

• Oracle7 also automatically defines any NOT NULL constraints on columns in the new table that existed on the corresponding columns of the selected table if the subquery selects the column rather than an expression containing the column.

• A CREATE TABLE statement cannot contain both the AS clause and a referential integrity constraint definition.

• If a CREATE TABLE statement contains both the AS clause and a CONSTRAINT clause or an ENABLE clause with the EXCEPTIONS option, Oracle7 ignores the EXCEPTIONS option. If any rows violate the constraint, Oracle7 does not create the table and returns an error message.

If all expressions in the subquery are columns, rather than expressions, you can omit the columns from the table definition entirely. In this case, the names of the columns of table are the same as the columns in the subquery.

CACHE

specifies that the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables.

NOCACHE

specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the default behavior.

Usage Notes

After creating a table, you can define additional columns and integrity constraints with the ADD clause of the ALTER TABLE command. You can change the definition of an existing column with the MODIFY clause of the ALTER TABLE command. To modify an integrity constraint, you must drop the constraint and redefine it.

UNRECOVERABLE

Example I

To define the EMP table owned by SCOTT, you could issue the following statement:

CREATE TABLE scott.emp 
	(empno     NUMBER        CONSTRAINT pk_emp PRIMARY KEY, 
	 ename     VARCHAR2(10)  CONSTRAINT nn_ename NOT NULL 
	                         CONSTRAINT upper_ename 
	                           CHECK (ename = UPPER(ename)), 
	 job       VARCHAR2(9), 
	 mgr       NUMBER        CONSTRAINT fk_mgr 
	                           REFERENCES scott.emp(empno), 
	 hiredate  DATE          DEFAULT SYSDATE, 
	 sal       NUMBER(10,2)  CONSTRAINT ck_sal 
	                           CHECK (sal > 500), 
	 comm      NUMBER(9,0)   DEFAULT NULL, 
	 deptno    NUMBER(2)     CONSTRAINT nn_deptno NOT NULL 
	                         CONSTRAINT fk_deptno 
REFERENCES scott.dept(deptno) ) 
	PCTFREE 5 PCTUSED 75 ; 

This table contains 8 columns. For example, the EMPNO column is of datatype NUMBER and has an associated integrity constraint named PK_EMP. The HIRDEDATE column is of datatype DATE and has a default value of SYSDATE.

This table definition specifies a PCTFREE of 5 and a PCTUSED of 75, which is appropriate for a relatively static table. The definition also defines integrity constraints on the columns of the EMP table.

Example II

To define the sample table SALGRADE in the HUMAN_RESOURCE tablespace with a small storage and limited allocation potential, issue the following statement:

CREATE TABLE salgrade 
	( grade  NUMBER  CONSTRAINT pk_salgrade 
	                   PRIMARY KEY 
	                   USING INDEX TABLESPACE users_a, 
	  losal  NUMBER, 
	  hisal  NUMBER ) 
	TABLESPACE human_resource 
	STORAGE (INITIAL     6144  
	         NEXT        6144 
	         MINEXTENTS     1  
	         MAXEXTENTS     5 
		     PCTINCREASE    5);

The above statement also defines a PRIMARY KEY constraint on the GRADE column and specifies that the index Oracle7 creates to enforce this constraint is created in the USERS_A tablespace.

For more examples of defining integrity constraints, see the CONSTRAINT clause . For examples of enabling and disabling integrity constraints, see the ENABLE and DISABLE clauses and .

Example III

Assuming you have the parallel query option, then the fastest method to create a table that has the same columns as the EMP table, but only for those employees in department 10, is to issue a command similar to the following:

CREATE TABLE emp_tmp
	UNRECOVERABLE
	PARALLEL (DEGREE 3)
	AS SELECT * FROM emp WHERE deptno = 10;

The UNRECOVERABLE keyword speeds up table creation because there is no overhead in generating and logging redo information.

Using parallelism speeds up the creation of the table because three processes are used to create the table. After the table is created, querying the table is also faster because the same degree of parallelism is used to access the table.

Related Topics