7
SQL Statements (continued)

CALL

Syntax

Purpose

Enables you to execute a routine (a standalone procedure or function, or a procedure or function defined within a type or package) from within SQL. For information on creating such routine, refer to PL/SQL User's Guide and Reference.

Prerequisites

You must have EXECUTE privilege on the standalone routine or on the type or package in which the routine is defined.

Keywords and Parameters

schema

specifies the schema in which the standalone routine (or the package or type containing the routine) resides. If you do not specify schema, Oracle assumes the routine is in your own schema.

type or package

specifies the type or package in which the routine is defined.

function | procedure | method

specifies the name of the function or procedure being called, or a synonym that translates to a function or procedure.
When you call a type's member function or procedure, if the first argument (SELF) is a null IN OUT argument, Oracle returns an error. If SELF is a null IN argument, Oracle returns null. In both cases, the function or procedure is not invoked.
Restriction: If the routine is a function, the INTO clause is mandatory.

@dblink

in a distributed database system, specifies the name of the database containing the standalone routine (or the package or functioning containing the routine). If you omit dblink, Oracle looks in your local database.

expr

specifies one or more arguments to the routine.
Restrictions:

An expr cannot be a pseudocolumn or either of the correlation variables VALUE or REF.

Any expr that is an IN OUT or OUT argument of the routine must correspond to a host variable expression.

INTO :host_variable

applies only to calls to functions. This parameter specifies which host variable will store the return value of the function.

:indicator_variable

indicates the value or condition of the host variable.
For more information on host variables and indicator variables, refer to Pro*C/C++ Precompiler Programmer's Guide.

Example

The following statement creates a procedure UPDATESALARY, and then calls the procedure, which updates the specified employee ID with a new salary.

CREATE OR REPLACE PROCEDURE updateSalary
   (id NUMBER, newsalary NUMBER) IS 
   BEGIN 
      UPDATE emp SET sal=newsalary WHERE empno=id; 
   END; 

CALL updateSalary(1404, 50000);

COMMENT

Syntax

Purpose

To add a comment about a table, view, materialized view, or column into the data dictionary. See also "Comments".

You can view the comments on a particular table or column by querying the data dictionary views USER_TAB_COMMENTS, DBA_TAB_COMMENTS, or ALL_TAB_COMMENTS or USER_COL_COMMENTS, DBA_COL_COMMENTS, or ALL_COL_COMMENTS. For information on these views, see Oracle8i Reference.

To drop a comment from the database, set it to the empty string ' '.

Prerequisites

The table, view, or snapshot must be in your own schema or you must have COMMENT ANY TABLE system privilege.

Keywords and Parameters

TABLE

specifies the schema and name of the table, view, or snapshot to be commented. If you omit schema, Oracle assumes the table, view, or snapshot is in your own schema.

COLUMN

specifies the name of the column of a table, view, or snapshot to be commented. If you omit schema, Oracle assumes the table, view, or snapshot is in your own schema.

IS 'text'

is the text of the comment. See the syntax description of 'text' in "Text".

Example

To insert an explanatory remark on the NOTES column of the SHIPPING table, you might issue the following statement:

COMMENT ON COLUMN shipping.notes
    IS 'Special packing or shipping instructions';

To drop this comment from the database, issue the following statement:

COMMENT ON COLUMN shipping.notes IS ' ';

COMMIT

Syntax

Purpose

To end your current transaction and make permanent all changes performed in the transaction. A transaction is a sequence of SQL statements that Oracle treats as a single unit. This statement also erases all savepoints in the transaction and releases the transaction's locks. For more information on transactions, see Oracle8i Concepts.

Note:
Oracle issues an implicit COMMIT before and after any data definition language (DDL) statement.

You can also use this statement to

Commit an in-doubt distributed transaction manually
Terminate a read-only transaction begun by a SET TRANSACTION statement.

For more information on specifying characteristics of a transaction, see "SET TRANSACTION"

Oracle Corporation recommends that you explicitly end every transaction in your application programs with a COMMIT or ROLLBACK statement, including the last transaction, before disconnecting from Oracle. If you do not explicitly commit the transaction and the program terminates abnormally, the last uncommitted transaction is automatically rolled back.

A normal exit from most Oracle utilities and tools causes the current transaction to be committed. A normal exit from an Oracle precompiler program does not commit the transaction and relies on Oracle to roll back the current transaction.

Prerequisites

You need no privileges to commit your current transaction.

To manually commit a distributed in-doubt transaction that you originally committed, you must have FORCE TRANSACTION system privilege. To manually commit a distributed in-doubt transaction that was originally committed by another user, you must have FORCE ANY TRANSACTION system privilege.

Keywords and Parameters

WORK

is supported for compliance with standard SQL. The statements COMMIT and COMMIT WORK are equivalent.

COMMENT

specifies a comment to be associated with the current transaction. The 'text' is a quoted literal of up to 50 characters that Oracle stores in the data dictionary view DBA_2PC_PENDING along with the transaction ID if the transaction becomes in-doubt.

For more information on adding comments to SQL statements, see "COMMENT".

FORCE

in a distributed database system, manually commits an in-doubt distributed transaction. The transaction is identified by the 'text' containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING. You can use integer to specifically assign the transaction a system change number (SCN). If you omit integer, the transaction is committed using the current SCN.

Note: A COMMIT statement with a FORCE clause commits only the specified transaction. Such a statement does not affect your current transaction.

For more information on these topics, see Oracle8i Distributed Database Systems.
Restriction: COMMIT statements using the FORCE clause are not supported in PL/SQL.

Examples

INSERT Example

This statement inserts a row into the DEPT table and commits this change:

INSERT INTO dept VALUES (50, 'MARKETING', 'TAMPA'); 
COMMIT WORK;

COMMENT Example

The following statement commits the current transaction and associates a comment with it:

COMMIT 
    COMMENT 'In-doubt transaction Code 36, Call (415) 555-2637';

If a network or machine failure prevents this distributed transaction from committing properly, Oracle stores the comment in the data dictionary along with the transaction ID. The comment indicates the part of the application in which the failure occurred and provides information for contacting the administrator of the database where the transaction was committed.

In-Doubt Transaction Example

The following statement manually commits an in-doubt distributed transaction:

COMMIT FORCE '22.57.53';

constraint_clause

Syntax

table_constraint::=

column_constraint::=

table_ref_constraint::=

references_clause::=

constraint_state::=

foreign_key_clause::=

physical_attributes_clause::=

storage_clause: See the "storage_clause".

Purpose

To define an integrity constraint. An integrity constraint is a rule that restricts the values for one or more columns in a table or an index-organized table.

Note:
Oracle does not support constraints on columns or attributes whose type is an object, nested table, varray, REF, or LOB. The only exception is that NOT NULL constraints are supported for columns or attributes whose type is object, VARRAY, REF, or LOB.

Prerequisites

Constraint clauses can appear in either CREATE TABLE or ALTER TABLE statements. To define an integrity constraint, you must have the privileges necessary to issue one of these statements. See "CREATE TABLE" and "ALTER TABLE".

To create a referential integrity constraint, the parent table must be in your own schema, or you must have the REFERENCES privilege on the columns of the referenced key in the parent table.

Keywords and Parameters

Examples

NOT NULL Example

The following statement alters the EMP table and defines and enables a NOT NULL constraint on the SAL column:

ALTER TABLE emp 
   MODIFY (sal  NUMBER  CONSTRAINT nn_sal NOT NULL);

NN_SAL ensures that no employee in the table has a null salary.

Unique Key Example

The following statement creates the DEPT table and defines and enables a unique key on the DNAME column:

CREATE TABLE dept 
    (deptno  NUMBER(2), 
     dname   VARCHAR2(9)  CONSTRAINT unq_dname UNIQUE,
      loc     VARCHAR2(10) );

The constraint UNQ_DNAME identifies the DNAME column as a unique key. This constraint ensures that no two departments in the table have the same name. However, the constraint does allow departments without names.

Alternatively, you can define and enable this constraint with the table_constraint syntax:

CREATE TABLE dept 
    (deptno  NUMBER(2), 
     dname   VARCHAR2(9), 
     loc     VARCHAR2(10), 
        CONSTRAINT unq_dname 
        UNIQUE (dname) 
    USING INDEX PCTFREE 20
        TABLESPACE user_x
        STORAGE (INITIAL 8K  NEXT 6K) );

The above statement also uses the USING INDEX clause to specify storage characteristics for the index that Oracle creates to enable the constraint.

Composite Unique Key Example

The following statement defines and enables a composite unique key on the combination of the CITY and STATE columns of the CENSUS table:

ALTER TABLE census 
    ADD CONSTRAINT unq_city_state 
    UNIQUE (city, state) 
    USING INDEX PCTFREE 5  
        TABLESPACE user_y
    EXCEPTIONS INTO bad_keys_in_ship_cont;

The UNQ_CITY_STATE constraint ensures that the same combination of CITY and STATE values does not appear in the table more than once.

The ADD CONSTRAINT clause also specifies other properties of the constraint:

The USING INDEX clause specifies storage characteristics for the index Oracle creates to enable the constraint.
The EXCEPTIONS INTO clause causes Oracle to write information to the BAD_KEYS_IN_SHIP_CONT table about any rows currently in the CENSUS table that violate the constraint.

Primary Key Example

The following statement creates the DEPT table and defines and enables a primary key on the DEPTNO column:

CREATE TABLE dept 
    (deptno  NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY, 
     dname   VARCHAR2(9), 
     loc     VARCHAR2(10) );

The PK_DEPT constraint identifies the DEPTNO column as the primary key of the DEPT table. This constraint ensures that no two departments in the table have the same department number and that no department number is NULL.

Alternatively, you can define and enable this constraint with table_constraint syntax:

CREATE TABLE dept 
    (deptno  NUMBER(2), 
    dname   VARCHAR2(9), 
    loc   VARCHAR2(10), 
    CONSTRAINT pk_dept PRIMARY KEY (deptno) );

Composite Primary Key Example

The following statement defines a composite primary key on the combination of the SHIP_NO and CONTAINER_NO columns of the SHIP_CONT table:

ALTER TABLE ship_cont 
    ADD PRIMARY KEY (ship_no, container_no) DISABLE;

This constraint identifies the combination of the SHIP_NO and CONTAINER_NO columns as the primary key of the SHIP_CONT table. The constraint ensures that no two rows in the table have the same values for both the SHIP_NO column and the CONTAINER_NO column.

The CONSTRAINT clause also specifies the following properties of the constraint:

The constraint definition does not include a constraint name, so Oracle generates a name for the constraint.
The DISABLE clause causes Oracle to define the constraint but not enable it.

Referential Integrity Constraint Example

The following statement creates the EMP table and defines and enables a foreign key on the DEPTNO column that references the primary key on the DEPTNO column of the DEPT table:

CREATE TABLE emp 
   (empno      NUMBER(4), 
    ename      VARCHAR2(10), 
    job        VARCHAR2(9), 
    mgr        NUMBER(4), 
    hiredate   DATE, 
    sal        NUMBER(7,2), 
    comm       NUMBER(7,2), 
    deptno     CONSTRAINT fk_deptno REFERENCES dept(deptno) );

The constraint FK_DEPTNO ensures that all departments given for employees in the EMP table are present in the DEPT table. However, employees can have null department numbers, meaning they are not assigned to any department. To ensure that all employees are assigned to a department, you could create a NOT NULL constraint on the DEPTNO column in the EMP table, in addition to the REFERENCES constraint.

Before you define and enable this constraint, you must define and enable a constraint that designates the DEPTNO column of the DEPT table as a primary or unique key.

The referential integrity constraint definition does not use the FOREIGN KEY keyword to identify the columns that make up the foreign key. Because the constraint is defined with a column constraint clause on the DEPTNO column, the foreign key is automatically on the DEPTNO column.

The constraint definition identifies both the parent table and the columns of the referenced key. Because the referenced key is the parent table's primary key, the referenced key column names are optional.

The above statement omits the DEPTNO column's datatype. Because this column is a foreign key, Oracle automatically assigns it the datatype of the DEPT.DEPTNO column to which the foreign key refers.

Alternatively, you can define a referential integrity constraint with table_constraint syntax:

CREATE TABLE emp 
  (empno     NUMBER(4), 
   ename     VARCHAR2(10), 
   job       VARCHAR2(9), 
   mgr       NUMBER(4), 
   hiredate  DATE, 
   sal       NUMBER(7,2), 
   comm      NUMBER(7,2), 
   deptno, 
   CONSTRAINT fk_deptno 
      FOREIGN  KEY (deptno) 
      REFERENCES  dept(deptno) );

The foreign key definitions in both statements of this statement omit the ON DELETE clause, causing Oracle to forbid the deletion of a department if any employee works in that department.

ON DELETE Example

This statement creates the EMP table, defines and enables two referential integrity constraints, and uses the ON DELETE clause:

CREATE TABLE emp 
  (empno    NUMBER(4) PRIMARY KEY, 
   ename    VARCHAR2(10), 
   job      VARCHAR2(9), 
   mgr      NUMBER(4) CONSTRAINT fk_mgr 
            REFERENCES emp ON DELETE SET NULL, 
   hiredate DATE, 
   sal      NUMBER(7,2), 
   comm     NUMBER(7,2), 
   deptno   NUMBER(2)   CONSTRAINT fk_deptno 
            REFERENCES dept(deptno) 
            ON DELETE CASCADE );

Because of the first ON DELETE clause, if manager number 2332 is deleted from the EMP table, Oracle sets to null the value of MGR for all employees in the EMP table who previously had manager 2332.

Because of the second ON DELETE clause, Oracle cascades any deletion of a DEPTNO value in the DEPT table to the DEPTNO values of its dependent rows of the EMP table. For example, if Department 20 is deleted from the DEPT table, Oracle deletes the department's employees from the EMP table.

Composite Referential Integrity Constraint Example

The following statement defines and enables a foreign key on the combination of the AREACO and PHONENO columns of the PHONE_CALLS table:

ALTER TABLE phone_calls 
    ADD CONSTRAINT fk_areaco_phoneno 
        FOREIGN KEY (areaco, phoneno) 
        REFERENCES customers(areaco, phoneno)
        EXCEPTIONS INTO wrong_numbers;

The constraint FK_AREACO_PHONENO ensures that all the calls in the PHONE_CALLS table are made from phone numbers that are listed in the CUSTOMERS table. Before you define and enable this constraint, you must define and enable a constraint that designates the combination of the AREACO and PHONENO columns of the CUSTOMERS table as a primary or unique key.

The EXCEPTIONS INTO clause causes Oracle to write information to the WRONG_NUMBERS table about any rows in the PHONE_CALLS table that violate the constraint.

CHECK Constraint Examples

The following statement creates the DEPT table and defines a CHECK constraint in each of the table's columns:

CREATE TABLE dept  
   (deptno NUMBER  CONSTRAINT check_deptno
          CHECK (deptno BETWEEN 10 AND 99) 
          DISABLE, 
    dname VARCHAR2(9)  CONSTRAINT check_dname 
          CHECK (dname = UPPER(dname)) 
          DISABLE, 
    loc VARCHAR2(10)  CONSTRAINT check_loc 
          CHECK (loc IN ('DALLAS','BOSTON',
          'NEW YORK','CHICAGO')) 
          DISABLE);

Each constraint restricts the values of the column in which it is defined:

CHECK_DEPTNO ensures that no department numbers are less than 10 or greater than 99.
CHECK_DNAME ensures that all department names are in uppercase.
CHECK_LOC restricts department locations to Dallas, Boston, New York, or Chicago.

Because each CONSTRAINT clause contains the DISABLE clause, Oracle only defines the constraints and does not enable them.

The following statement creates the EMP table and uses a table_constraint_clause to define and enable a CHECK constraint:

CREATE TABLE emp 
    (empno          NUMBER(4), 
     ename          VARCHAR2(10), 
     job            VARCHAR2(9),
     mgr            NUMBER(4), 
     hiredate       DATE, 
     sal            NUMBER(7,2), 
     comm           NUMBER(7,2),
     deptno         NUMBER(2),
     CHECK (sal + comm <= 5000) );

This constraint uses an inequality condition to limit an employee's total compensation, the sum of salary and commission, to $5000:

If an employee has non-null values for both salary and commission, the sum of these values must not exceed $5000 to satisfy the constraint.
If an employee has a null salary or commission, the result of the condition is unknown and the employee automatically satisfies the constraint.

Because the CONSTRAINT clause in this example does not supply a constraint name, Oracle generates a name for the constraint.

The following statement defines and enables a PRIMARY KEY constraint, two referential integrity constraints, a NOT NULL constraint, and two CHECK constraints:

CREATE TABLE order_detail 
  (CONSTRAINT pk_od PRIMARY KEY (order_id, part_no), 
   order_id NUMBER 
      CONSTRAINT fk_oid REFERENCES scott.order (order_id), 
   part_no            NUMBER 
      CONSTRAINT fk_pno REFERENCES scott.part (part_no), 
   quantity            NUMBER 
      CONSTRAINT nn_qty NOT NULL 
      CONSTRAINT check_qty_low CHECK (quantity > 0), 
   cost            NUMBER 
      CONSTRAINT check_cost CHECK (cost > 0) );

The constraints enable the following rules on table data:

PK_OD identifies the combination of the ORDER_ID and PART_NO columns as the primary key of the table. To satisfy this constraint, no two rows in the table can contain the same combination of values in the ORDER_ID and the PART_NO columns, and no row in the table can have a null in either the ORDER_ID column or the PART_NO column.
FK_OID identifies the ORDER_ID column as a foreign key that references the ORDER_ID column in the ORDER table in SCOTT's schema. All new values added to the column ORDER_DETAIL.ORDER_ID must already appear in the column SCOTT.ORDER.ORDER_ID.
FK_PNO identifies the PART_NO column as a foreign key that references the PART_NO column in the PART table owned by SCOTT. All new values added to the column ORDER_DETAIL.PART_NO must already appear in the column SCOTT.PART.PART_NO.
NN_QTY forbids nulls in the QUANTITY column.
CHECK_QTY ensures that values in the QUANTITY column are always greater than zero.
CHECK_COST ensures the values in the COST column are always greater than zero.

This example also illustrates the following points about constraint clauses and column definitions:

Table_constraint syntax and column definitions can appear in any order. In this example, the table_constraint syntax that defines the PK_OD constraint precedes the column definitions.
A column definition can use column_constraint syntax multiple times. In this example, the definition of the QUANTITY column contains the definitions of both the NN_QTY and CHECK_QTY constraints.
A table can have multiple CHECK constraints. Multiple CHECK constraints, each with a simple condition enforcing a single business rule, is better than a single CHECK constraint with a complicated condition enforcing multiple business rules. When a constraint is violated, Oracle returns an error identifying the constraint. Such an error more precisely identifies the violated business rule if the identified constraint enables a single business rule.

DEFERRABLE Constraint Examples

The following statement creates table GAMES with a NOT DEFERRABLE INITIALLY IMMEDIATE constraint check on the SCORES column:

CREATE TABLE games (scores NUMBER CHECK (scores >= 0));

To define a unique constraint on a column as INITIALLY DEFERRED DEFERRABLE, issue the following statement:

CREATE TABLE orders
  (ord_num NUMBER CONSTRAINT unq_num UNIQUE (ord_num)
   INITIALLY DEFERRED DEFERRABLE);

CREATE CLUSTER

Syntax

physical_attributes_clause::=

storage_clause: See the "storage_clause".

parallel_clause::=

Purpose

To create a cluster. A cluster is a schema object that contains data from one or more tables, all of which have one or more columns in common. Oracle stores together all the rows (from all the tables) that share the same cluster key.

For general information on clusters, see Oracle8i Concepts. For information on performance considerations of clusters, see Oracle8i Application Developer's Guide - Fundamentals. For suggestions on when to use clusters, see Oracle8i Tuning.

Prerequisites

To create a cluster in your own schema, you must have CREATE CLUSTER system privilege. To create a cluster in another user's schema, you must have CREATE ANY CLUSTER system privilege. Also, the owner of the schema to contain the cluster must have either space quota on the tablespace containing the cluster or UNLIMITED TABLESPACE system privilege.

Oracle does not automatically create an index for a cluster when the cluster is initially created. Data manipulation language (DML) statements cannot be issued against clustered tables until a cluster index has been created.

Keywords and Parameters

schema

is the schema to contain the cluster. If you omit schema, Oracle creates the cluster in your current schema.

cluster

is the name of the cluster to be created.

After you create a cluster, you add tables to it. A cluster can contain a maximum of 32 tables. After you create a cluster and add tables to it, the cluster is transparent. You can access clustered tables with SQL statements just as you can nonclustered tables. For information on adding tables to a cluster, see "CREATE TABLE".

column

is the name of a column in the cluster key. You can specify up to 16 cluster key columns. These columns must correspond in both datatype and size to columns in each of the clustered tables, although they need not correspond in name.
You cannot specify integrity constraints as part of the definition of a cluster key column. Instead, you can associate integrity constraints with the tables that belong to the cluster.

datatype

is the datatype of a cluster key column. For information on datatypes, see the section "Datatypes".
Restrictions:

You cannot specify a cluster key column of datatype LONG, LONG RAW, REF, nested table, varray, BLOB, CLOB, BFILE, or user-defined object type.

You cannot use the HASH IS clause if any column datatype is not INTEGER or NUMBER with scale 0.

You can specify a column of type ROWID, but Oracle does not guarantee that the values in such columns are valid rowids.

physical_attributes_clause

specifies the storage characteristics of the cluster. Each table in the cluster uses these storage characteristics as well.

PCTUSED

specifies the limit that Oracle uses to determine when additional rows can be added to a cluster's data block. The value of this parameter is expressed as a whole number and interpreted as a percentage.

PCTFREE

specifies the space reserved in each of the cluster's data blocks for future expansion. The value of the parameter is expressed as a whole number and interpreted as a percentage.

INITRANS

specifies the initial number of concurrent update transactions allocated for data blocks of the cluster. The value of this parameter for a cluster cannot be less than 2 or more than the value of the MAXTRANS parameter. The default value is 2 or the INITRANS value for the cluster's tablespace, whichever is greater.

MAXTRANS

specifies the maximum number of concurrent update transactions for any given data block belonging to the cluster. The value of this parameter cannot be less than the value of the INITRANS parameter. The maximum value of this parameter is 255. The default value is the MAXTRANS value for the tablespace to contain the cluster.

For a complete description of the PCTUSED, PCTFREE, INITRANS, and MAXTRANS parameters, see "CREATE TABLE".

storage_clause

specifies how data blocks are allocated to the cluster. See the "storage_clause".

SIZE

specifies the amount of space in bytes to store all rows with the same cluster key value or the same hash value. Use K or M to specify this space in kilobytes or megabytes. This space determines the maximum number of cluster or hash values stored in a data block. If SIZE is not a divisor of the data block size, Oracle uses the next largest divisor. If SIZE is larger than the data block size, Oracle uses the operating system block size, reserving at least one data block per cluster or hash value.

Oracle also considers the length of the cluster key when determining how much space to reserve for the rows having a cluster key value. Larger cluster keys require larger sizes. To see the actual size, query the KEY_SIZE column of the USER_CLUSTERS data dictionary view. (This does not apply to hash clusters, because hash values are not actually stored in the cluster.)
If you omit this parameter, Oracle reserves one data block for each cluster key value or hash value.

TABLESPACE

specifies the tablespace in which the cluster is created.

INDEX

creates an indexed cluster. In an indexed cluster, Oracle stores together rows having the same cluster key value. Each distinct cluster key value is stored only once in each data block, regardless of the number of tables and rows in which it occurs.
After you create an indexed cluster, you must create an index on the cluster key before you can issue any data manipulation language (DML) statements against a table in the cluster. This index is called the cluster index. For information on creating a cluster index, see "CREATE INDEX".

Note: You cannot create a cluster index for a hash cluster, and you need not create an index on a hash cluster key. If you specify neither INDEX nor HASHKEYS, Oracle creates an indexed cluster by default.

For more information in indexed clusters, see Oracle8i Concepts.

HASHKEYS

creates a hash cluster and specifies the number of hash values for a hash cluster. In a hash cluster, Oracle stores together rows that have the same hash key value. The hash value for a row is the value returned by the cluster's hash function.
Oracle rounds up the HASHKEYS value to the nearest prime number to obtain the actual number of hash values. The minimum value for this parameter is 2. If you omit both the INDEX clause and the HASHKEYS parameter, Oracle creates an indexed cluster by default.
When you create a hash cluster, Oracle immediately allocates space for the cluster based on the values of the SIZE and HASHKEYS parameters. For more information on how Oracle allocates space for clusters, see Oracle8i Concepts.

SINGLE TABLE

specifies that the cluster is a type of hash cluster containing only one table. This clause can provide faster access to rows than would result if the table were not part of a cluster.
Restriction: Only one table can be present in the cluster at a time. However, you can drop the table and create a different table in the same cluster.

HASH IS

specifies an expression to be used as the hash function for the hash cluster. The expression:

Must evaluate to a positive value

Must contain at least one column with referenced columns of any datatype as long as the entire expression evaluates to a number of scale 0. For example: NUM_COLUMN * length(VARCHAR2_COLUMN)

Cannot reference user-defined PL/SQL functions

Cannot reference SYSDATE, USERENV, TO_DATE, UID, USER, LEVEL, or ROWNUM

Cannot evaluate to a constant

Cannot contain a subquery

Cannot contain columns qualified with a schema or object name (other than the cluster name)

If you omit the HASH IS clause, Oracle uses an internal hash function for the hash cluster.

The cluster key of a hash column can have one or more columns of any datatype. Hash clusters with composite cluster keys or cluster keys made up of noninteger columns must use the internal hash function.

parallel_clause

causes creation of the cluster to be parallelized. See also the Notes to the parallel_clause of "CREATE TABLE".

NOPARALLEL

specifies serial execution. This is the default.

PARALLEL

causes Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.

PARALLEL integer

specifies the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.

Restriction: If the tables in cluster contain any columns of LOB or user-defined object type, this statement as well as subsequent INSERT, UPDATE, or DELETE operations on cluster are executed serially without notification.

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 clause 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.

Examples

Creating a Cluster

The following statement creates an indexed cluster named PERSONNEL with the cluster key column DEPARTMENT_NUMBER, a cluster size of 512 bytes, and storage parameter values:

CREATE CLUSTER personnel 
    ( department_number  NUMBER(2) ) 
    SIZE 512 
    STORAGE (INITIAL 100K NEXT 50K);

Adding Tables to a Cluster

The following statements add the EMP and DEPT tables to the cluster:

CREATE TABLE emp 
    (empno     NUMBER        PRIMARY KEY, 
     ename     VARCHAR2(10)  NOT NULL 
                             CHECK (ename = UPPER(ename)), 
     job       VARCHAR2(9), 
     mgr       NUMBER        REFERENCES scott.emp(empno), 
     hiredate  DATE          
        CHECK (hiredate < TO_DATE ('08-14-1998', 'MM-DD-YYYY')), 
     sal       NUMBER(10,2)  CHECK (sal > 500), 
     comm      NUMBER(9,0)   DEFAULT NULL, 
     deptno    NUMBER(2)      NOT NULL ) 
     CLUSTER personnel (deptno); 
 
CREATE TABLE dept 
    (deptno  NUMBER(2), 
     dname   VARCHAR2(9), 
     loc     VARCHAR2(9))
     CLUSTER personnel (deptno);

Cluster Key Example

The following statement creates the cluster index on the cluster key of PERSONNEL:

CREATE INDEX idx_personnel ON CLUSTER personnel;

After creating the cluster index, you can insert rows into either the EMP or DEPT tables.

Hash Cluster Examples

The following statement creates a hash cluster named PERSONNEL with the cluster key column DEPARTMENT_NUMBER, a maximum of 503 hash key values, each of which is allocated 512 bytes, and storage parameter values:

CREATE CLUSTER personnel
( department_number  NUMBER )
    SIZE 512  HASHKEYS 500 
    STORAGE (INITIAL 100K  NEXT 50K);

Because the above statement omits the HASH IS clause, Oracle uses the internal hash function for the cluster.

The following statement creates a hash cluster named PERSONNEL with the cluster key made up of the columns HOME_AREA_CODE and HOME_PREFIX, and uses a SQL expression containing these columns for the hash function:

CREATE CLUSTER personnel 
    ( home_area_code  NUMBER,
      home_prefix     NUMBER ) 
    HASHKEYS 20
    HASH IS MOD(home_area_code + home_prefix, 101);

Single-Table Hash Cluster Example

The following statement creates a single-table hash cluster named PERSONNEL with the cluster key DEPTNO and a maximum of 503 hash key values, each of which is allocated 512 bytes:

CREATE CLUSTER personnel
   (deptno NUMBER)
   SIZE 512 SINGLE TABLE HASHKEYS 500;


Click the "Next" button to see the next statement in this chapter.

schema	specifies the schema in which the standalone routine (or the package or type containing the routine) resides. If you do not specify schema, Oracle assumes the routine is in your own schema.
type or package	specifies the type or package in which the routine is defined.
function \| procedure \| method	specifies the name of the function or procedure being called, or a synonym that translates to a function or procedure. When you call a type's member function or procedure, if the first argument (SELF) is a null IN OUT argument, Oracle returns an error. If SELF is a null IN argument, Oracle returns null. In both cases, the function or procedure is not invoked. Restriction: If the routine is a function, the INTO clause is mandatory.
@dblink	in a distributed database system, specifies the name of the database containing the standalone routine (or the package or functioning containing the routine). If you omit dblink, Oracle looks in your local database.
expr	specifies one or more arguments to the routine. Restrictions: An expr cannot be a pseudocolumn or either of the correlation variables VALUE or REF. Any expr that is an IN OUT or OUT argument of the routine must correspond to a host variable expression.
INTO :host_variable	applies only to calls to functions. This parameter specifies which host variable will store the return value of the function.
:indicator_variable	indicates the value or condition of the host variable. For more information on host variables and indicator variables, refer to ProC/C++ Precompiler Programmer's Guide.*

TABLE	specifies the schema and name of the table, view, or snapshot to be commented. If you omit schema, Oracle assumes the table, view, or snapshot is in your own schema.
COLUMN	specifies the name of the column of a table, view, or snapshot to be commented. If you omit schema, Oracle assumes the table, view, or snapshot is in your own schema.
IS 'text'	is the text of the comment. See the syntax description of 'text' in "Text".

WORK	is supported for compliance with standard SQL. The statements COMMIT and COMMIT WORK are equivalent.
COMMENT	specifies a comment to be associated with the current transaction. The 'text' is a quoted literal of up to 50 characters that Oracle stores in the data dictionary view DBA_2PC_PENDING along with the transaction ID if the transaction becomes in-doubt.
	For more information on adding comments to SQL statements, see "COMMENT".
FORCE	in a distributed database system, manually commits an in-doubt distributed transaction. The transaction is identified by the 'text' containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING. You can use integer to specifically assign the transaction a system change number (SCN). If you omit integer, the transaction is committed using the current SCN.
	Note: A COMMIT statement with a FORCE clause commits only the specified transaction. Such a statement does not affect your current transaction.
	For more information on these topics, see Oracle8i Distributed Database Systems. Restriction: COMMIT statements using the FORCE clause are not supported in PL/SQL.

*table_constraint*	The table_constraint syntax is part of the table definition. An integrity constraint defined with this syntax can impose rules on any columns in the table.
	The table_constraint syntax can appear in a CREATE TABLE or ALTER TABLE statement. This syntax can define any type of integrity constraint except a NOT NULL constraint.
*column_constraint*	The column_constraint syntax is part of a column definition. Usually, an integrity constraint defined with this syntax can impose rules only on the column in which it is defined. The column_constraint syntax that appears in a CREATE TABLE or ALTER TABLE ADD statement can define any type of integrity constraint. Column_constraint syntax that appears in an ALTER TABLE MODIFY column_options statement can only define or remove a NOT NULL constraint.
CONSTRAINT	identifies the integrity constraint by the name constraint. Oracle stores this name in the data dictionary along with the definition of the integrity constraint. If you omit this identifier, Oracle generates a name with the form SYS_Cn.
	If you do not specify NULL or NOT NULL in a column definition, NULL is the default.
	Restriction: You cannot create a constraint on columns or attributes whose type is user-defined object, LOB, or REF, with the following exceptions: You can specify a NOT NULL constraint on columns or attributes of user-defined object type, varray, and LOB. You can specify NOT NULL and referential integrity constraints on a column of type REF.
UNIQUE	designates a column or combination of columns as a unique key. To satisfy a UNIQUE constraint, no two rows in the table can have the same value for the unique key. However, the unique key made up of a single column can contain nulls. A composite unique key is made up of a combination of columns. To define a composite unique key, you must use table_constraint syntax rather than column_constraint syntax. Any row that contains nulls in all key columns automatically satisfies the constraint. However, two rows that contain nulls for one or more key columns and the same combination of values for the other key columns violate the constraint.
	Restrictions: For a composite unique key, no two rows in the table can have the same combination of values in the key columns. A composite unique key cannot have more than 32 columns. The overall size of the key (in bytes) should not exceed approximately the width of all indexed columns plus the number of indexed columns. A unique key column cannot be of datatype LONG or LONG RAW. You cannot designate the same column or combination of columns as both a unique key and a primary key.
PRIMARY KEY	designates a column or combination of columns as the table's primary key. A composite primary key is made up of a combination of columns. To define a composite primary key, you must use the table_constraint syntax rather than the column_constraint syntax. Restrictions: A table can have only one primary key. None of the columns in the primary key can have datatype LONG or LONG RAW. No primary key value can appear in more than one row in the table. No column that is part of the primary key can contain a null. The size of the PRIMARY KEY of an index-organized table cannot exceed one-half of the database block size or 3800 bytes, whichever is less. (PRIMARY KEY is required for an index-organized table.) A composite primary key cannot have more than 32 columns. The overall size of the key (in bytes) should not exceed approximately the width of all indexed columns plus the number of indexed columns. You cannot designate the same column or combination of columns as both a primary key and a unique key.
NULL \| NOT NULL	determines whether a column can contain nulls. You must specify NULL and NOT NULL with column_constraint syntax, not with table_constraint syntax.
	NULL	specifies that a column can contain null values. The NULL keyword does not actually define an integrity constraint. If you do not specify either NOT NULL or NULL, the column can contain nulls by default.
	NOT NULL	specifies that a column cannot contain null values. To satisfy this constraint, every row in the table must contain a value for the column.
Referential integrity constraints
	Referential integrity constraints designate a column or combination of columns as the foreign key and establish a relationship between that foreign key and a specified primary or unique key, called the referenced key. The table containing the foreign key is called the child table, and the table containing the referenced key is called the parent table. The foreign key and the referenced key can be in the same table. In this case, the parent and child tables are the same.
	From the table level, specify referential integrity using the foreign_key_clause with the table_constraint syntax. This syntax allows you to specify a composite foreign key, which is made up of a combination of columns. From the column level, use the REFERENCES clause of the column_constraint syntax to specify a referential integrity constraint in which the foreign key is made up of a single column.
	You can designate the same column or combination of columns as both a foreign key and a primary or unique key. You can also designate the same column or combination of columns as both a foreign key and a cluster key. You can define multiple foreign keys in a table. Also, a single column can be part of more than one foreign key.
	Restrictions: A foreign key cannot be of type LONG or LONG RAW. The referenced UNIQUE or PRIMARY key constraint on the parent table must already be defined. The child and parent tables must be on the same database. To enable referential integrity constraints across nodes of a distributed database, you must use database triggers. For more information, see Oracle8i Application Developer's Guide - Fundamentals. You cannot define a referential integrity constraint in a CREATE TABLE statement that contains an AS subquery clause. Instead, you must create the table without the constraint and then add it later with an ALTER TABLE statement.
	*foreign_key_clause*	designates a column or combination of columns as the foreign key from the table level. You must use this syntax to define a composite foreign key. To satisfy a referential integrity constraint involving composite keys, either the values of the foreign key columns must match the values of the referenced key columns in a row in the parent table, or the value of at least one of the columns of the foreign key must be null.
		Restrictions: A composite foreign key cannot have more than 32 columns. The overall size of the key (in bytes) should not exceed approximately the width of all indexed columns plus the number of indexed columns. A composite foreign key must refer to a composite unique key or a composite primary key.
	REFERENCES	designates the current column or attribute as the foreign key and identifies the parent table and the column or combination of columns that make up the referenced key. If you identify only the parent table and omit the column names, the foreign key automatically references the primary key of the parent table. The corresponding columns of the referenced key and the foreign key must match in number and datatypes.
	ON DELETE	determines how Oracle automatically maintains referential integrity if you remove a referenced primary or unique key value. If you omit this clause, Oracle does not allow you to delete referenced key values in the parent table that have dependent rows in the child table.
		CASCADE specifies that Oracle removes dependent foreign key values. SET NULL specifies that Oracle converts dependent foreign key values to NULL.
CHECK	specifies a condition that each row in the table must satisfy. To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a null). For information and syntax, see "Conditions". When Oracle evaluates a CHECK constraint condition for a particular row, any column names in the condition refer to the column values in that row. If you create multiple CHECK constraints for a column, design them carefully so their purposes do not conflict. Oracle does not verify that CHECK conditions are not mutually exclusive.
	Restrictions: The condition of a CHECK constraint can refer to any column in the table, but it cannot refer to columns of other tables. CHECK constraint conditions cannot contain the following constructs: Queries to refer to values in other rows Calls to the functions SYSDATE, UID, USER, or USERENV The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM Date constants that are not fully specified
*table_ref_constraint* and *column_ref_constraint*	further describe a column of type REF. The only difference between these clauses is that you specify table_ref_constraint from the table level, so you must identify the REF column or attribute you are defining. You specify column_ref_constraint after you have already identified the REF column or attribute. Both types of constraint let you specify a SCOPE constraint, a WITH ROWID constraint, or a referential integrity constraint. As is the case for regular table and column constraints, you use FOREIGN KEY syntax for a referential integrity constraint at the table level, and REFERENCES syntax for a referential integrity constraint at the column level. See "Referential integrity constraints". If the REF column's scope table or reference table has a primary-key-based object identifier, then it is a user-defined REF column. For more information on REFs, see Oracle8i Concepts.
	ref_column	is the name of a REF column of an object or relational table.
	ref_attribute	is an embedded REF attribute within an object column of a relational table.
	SCOPE	In a table with a REF column, each REF value in the column can conceivably reference a row in a different object table. The SCOPE clause restricts the scope of references to a single table, scope_table_name. The values in the REF column or attribute point to objects in scope_table_name, in which object instances (of the same type as the REF column) are stored. You can only specify one scope table per REF column.
		Restrictions: You can add a SCOPE constraint to an existing column only if the table is empty. You cannot specify SCOPE for the REF elements of a varray column. You must specify this clause if you specify AS subquery and the subquery returns user-defined REFs. The scope_table_name must be in your own schema or you must have SELECT privileges on scope_table_name or SELECT ANY TABLE system privileges. You cannot drop a SCOPE table constraint from a REF column.
	WITH ROWID	stores the rowid along with the REF value in ref_column or ref_attribute. Storing a REF value with a rowid can improve the performance of dereferencing operations, but will also use more space. Default storage of REF values is without rowids.
		Restrictions: You cannot specify a WITH ROWID constraint for the REF elements of a varray column. You cannot drop a WITH ROWID constraint from a REF column. If the REF column or attribute is scoped, then this clause is ignored and the rowid is not stored with the REF value.
	references_clause	specifies a referential integrity constraint on the REF column.This clause also implicitly restricts the scope of the REF column or attribute to the reference table. If you do not specify CONSTRAINT, Oracle generates a system name for the constraint.
		Restrictions: If you add a referential integrity constraint to an existing REF column that is scoped, then the referenced table must be the same as the scope table of the REF column. The system adds a scope constraint when you add a referential integrity constraint to an existing unscoped REF column. Therefore, all the restrictions that apply for the SCOPE constraint also apply in this case. If you later drop the referential integrity constraint, the REF column will remain scoped to the referenced table.
DEFERRABLE	indicates that constraint checking can be deferred until the end of the transaction by using the SET CONSTRAINT(S) statement. For information on checking constraints after each DML statement, see "SET CONSTRAINT(S)". See Oracle8i Administrator's Guide and Oracle8i Concepts for more information about deferred constraints.
NOT DEFERRABLE	indicates that this constraint is checked at the end of each DML statement. If you do not specify either word, then NOT DEFERRABLE is the default.
INITIALLY IMMEDIATE	indicates that at the start of every transaction, the default is to check this constraint at the end of every DML statement. If you do not specify INITIALLY, INITIALLY IMMEDIATE is the default.
INITIALLY DEFERRED	implies that this constraint is DEFERRABLE and specifies that, by default, the constraint is checked only at the end of each transaction.
Restrictions: You cannot defer a NOT DEFERRABLE constraint with the SET CONSTRAINT(S) statement. You cannot specify either DEFERRABLE or NOT DEFERRABLE if you are modifying an existing constraint directly (that is, by specifying the ALTER TABLE ... MODIFY constraint statement). You cannot alter a constraint's deferrability status. You must drop the constraint and re-create it.
RELY \| NORELY	specifies whether an enabled constraint is to be enforced. Specify RELY to enable an existing constraint without enforcement. Specify NORELY to enable and enforce an existing constraint. The default is NORELY. Unenforced constraints are generally useful only with materialized views and query rewrite. Depending on the QUERY_REWRITE_INTEGRITY mode (see "ALTER SESSION"), query rewrite can use constraints that are enabled with or without enforcement to determine join information. For more information on materialized views and query rewrite, see Oracle8i Tuning.
	Restrictions: RELY and NORELY are relevant only if you are modifying an existing constraint (that is, you have issued the ALTER TABLE ... MODIFY constraint statement). You cannot set a NOT NULL constraint to RELY.
USING INDEX	specifies parameters for the index Oracle uses to enable a UNIQUE or PRIMARY KEY constraint. The name of the index is the same as the name of the constraint. You can choose the values of the INITRANS, MAXTRANS, TABLESPACE, STORAGE, PCTFREE, LOGGING, and NOLOGGING parameters for the index. For information on these parameters, see "CREATE TABLE".
	Restrictions: Use this clause only when enabling UNIQUE and PRIMARY KEY constraints. You cannot specify the PCTUSED parameter with this clause, because that parameter is not valid with indexes.
NOSORT	indicates that the rows are stored in the database in ascending order and therefore Oracle does not have to sort the rows when creating the index.
ENABLE	specifies that the constraint will be applied to all new data in the table. Before you can enable a referential integrity constraint, its referenced constraint must be enabled. ENABLE VALIDATE additionally specifies that all old data also complies with the constraint. An enabled validated constraint guarantees that all data is and will continue to be valid.
	If you place a primary key constraint in ENABLE VALIDATE mode, the validation process will verify that the primary key columns contain no nulls. To avoid this overhead, mark each column in the primary key NOT NULL before enabling the table's primary key constraint. (For optimal results, do this before inserting data into the column.)
	ENABLE NOVALIDATE ensures that all new DML operations on the constrained data comply with the constraint, but does not ensure that existing data in the table complies with the constraint.
	Enabling a primary key or unique key constraint automatically creates a unique index to enforce the constraint. This index is dropped if the constraint is subsequently disabled, causing Oracle to rebuild the index every time the constraint is enabled. To avoid this behavior, create new primary key and unique key constraints initially disabled. Then create nonunique indexes or use existing nonunique indexes to enforce the constraints. For additional notes and restrictions, see the enable_disable_clause of "CREATE TABLE".
DISABLE	disables the integrity constraint. If you do not specify this clause when creating a constraint, Oracle automatically enables the constraint.
	DISABLE VALIDATE disables the constraint and drops the index on the constraint, but keeps the constraint valid. This feature is most useful in data warehousing situations, where the need arises to load into a range-partitioned table a quantity of data with a distinct range of values in the unique key. In such situations, the disable validate state enables you to save space by not having an index. You can then load data from a nonpartitioned table into a partitioned table using the exchange_partition_clause of the ALTER TABLE statement. All other modifications to the table by other SQL statements are disallowed.
	If the unique key coincides with the partitioning key of the partitioned table, disabling the constraint saves overhead and has no detrimental effects. If the unique key does not coincide with the partitioning key, Oracle performs automatic table scans during the exchange to validate the constraint, which might offset the benefit of loading without an index.
	DISABLE NOVALIDATE signifies that Oracle makes no effort to maintain the constraint (because it is disabled) and cannot guarantee that the constraint is true (because it is not being validated). For information on when to use this setting, see Oracle8i Tuning. You cannot drop a table whose primary key is being referenced by a foreign key even if the foreign key constraint is in DISABLE NOVALIDATE state. Further, the optimizer can use constraints in DISABLE NOVALIDATE state.
	If you specify neither VALIDATE nor NOVALIDATE, the default is NOVALIDATE. If you disable a unique or primary key constraint that is using a unique index, Oracle drops the unique index.
EXCEPTIONS INTO	specifies a table into which Oracle places the ROWIDs of all rows violating the constraint.
	Note: You must create an appropriate exceptions report table to accept information from the EXCEPTIONS INTO clause before enabling the constraint. You can create an exception table by submitting the script UTLEXCPT1.SQL, which creates a table named EXCEPTIONS. You can create additional exceptions tables with different names by modifying and resubmitting the script. (You can use the UTLEXCPT1.SQL script with index-organized tables. You could not use earlier versions of the script for this purpose. See Oracle8i Migration for compatibility information.)
	This clause is valid only when validating a constraint.

schema	is the schema to contain the cluster. If you omit schema, Oracle creates the cluster in your current schema.
cluster	is the name of the cluster to be created.
	After you create a cluster, you add tables to it. A cluster can contain a maximum of 32 tables. After you create a cluster and add tables to it, the cluster is transparent. You can access clustered tables with SQL statements just as you can nonclustered tables. For information on adding tables to a cluster, see "CREATE TABLE".
column	is the name of a column in the cluster key. You can specify up to 16 cluster key columns. These columns must correspond in both datatype and size to columns in each of the clustered tables, although they need not correspond in name. You cannot specify integrity constraints as part of the definition of a cluster key column. Instead, you can associate integrity constraints with the tables that belong to the cluster.
datatype	is the datatype of a cluster key column. For information on datatypes, see the section "Datatypes". Restrictions: You cannot specify a cluster key column of datatype LONG, LONG RAW, REF, nested table, varray, BLOB, CLOB, BFILE, or user-defined object type. You cannot use the HASH IS clause if any column datatype is not INTEGER or NUMBER with scale 0. You can specify a column of type ROWID, but Oracle does not guarantee that the values in such columns are valid rowids.
*physical_attributes_clause*	specifies the storage characteristics of the cluster. Each table in the cluster uses these storage characteristics as well.
PCTUSED	specifies the limit that Oracle uses to determine when additional rows can be added to a cluster's data block. The value of this parameter is expressed as a whole number and interpreted as a percentage.
PCTFREE	specifies the space reserved in each of the cluster's data blocks for future expansion. The value of the parameter is expressed as a whole number and interpreted as a percentage.
INITRANS	specifies the initial number of concurrent update transactions allocated for data blocks of the cluster. The value of this parameter for a cluster cannot be less than 2 or more than the value of the MAXTRANS parameter. The default value is 2 or the INITRANS value for the cluster's tablespace, whichever is greater.
MAXTRANS	specifies the maximum number of concurrent update transactions for any given data block belonging to the cluster. The value of this parameter cannot be less than the value of the INITRANS parameter. The maximum value of this parameter is 255. The default value is the MAXTRANS value for the tablespace to contain the cluster.
	For a complete description of the PCTUSED, PCTFREE, INITRANS, and MAXTRANS parameters, see "CREATE TABLE".
*storage_clause*	specifies how data blocks are allocated to the cluster. See the "storage_clause".
SIZE	specifies the amount of space in bytes to store all rows with the same cluster key value or the same hash value. Use K or M to specify this space in kilobytes or megabytes. This space determines the maximum number of cluster or hash values stored in a data block. If SIZE is not a divisor of the data block size, Oracle uses the next largest divisor. If SIZE is larger than the data block size, Oracle uses the operating system block size, reserving at least one data block per cluster or hash value.
	Oracle also considers the length of the cluster key when determining how much space to reserve for the rows having a cluster key value. Larger cluster keys require larger sizes. To see the actual size, query the KEY_SIZE column of the USER_CLUSTERS data dictionary view. (This does not apply to hash clusters, because hash values are not actually stored in the cluster.) If you omit this parameter, Oracle reserves one data block for each cluster key value or hash value.
TABLESPACE	specifies the tablespace in which the cluster is created.
INDEX	creates an indexed cluster. In an indexed cluster, Oracle stores together rows having the same cluster key value. Each distinct cluster key value is stored only once in each data block, regardless of the number of tables and rows in which it occurs. After you create an indexed cluster, you must create an index on the cluster key before you can issue any data manipulation language (DML) statements against a table in the cluster. This index is called the cluster index. For information on creating a cluster index, see "CREATE INDEX".
	Note: You cannot create a cluster index for a hash cluster, and you need not create an index on a hash cluster key. If you specify neither INDEX nor HASHKEYS, Oracle creates an indexed cluster by default.
	For more information in indexed clusters, see Oracle8i Concepts.
HASHKEYS	creates a hash cluster and specifies the number of hash values for a hash cluster. In a hash cluster, Oracle stores together rows that have the same hash key value. The hash value for a row is the value returned by the cluster's hash function. Oracle rounds up the HASHKEYS value to the nearest prime number to obtain the actual number of hash values. The minimum value for this parameter is 2. If you omit both the INDEX clause and the HASHKEYS parameter, Oracle creates an indexed cluster by default. When you create a hash cluster, Oracle immediately allocates space for the cluster based on the values of the SIZE and HASHKEYS parameters. For more information on how Oracle allocates space for clusters, see Oracle8i Concepts.
	SINGLE TABLE	specifies that the cluster is a type of hash cluster containing only one table. This clause can provide faster access to rows than would result if the table were not part of a cluster. Restriction: Only one table can be present in the cluster at a time. However, you can drop the table and create a different table in the same cluster.
	HASH IS	specifies an expression to be used as the hash function for the hash cluster. The expression:
		Must evaluate to a positive value Must contain at least one column with referenced columns of any datatype as long as the entire expression evaluates to a number of scale 0. For example: NUM_COLUMN * length(VARCHAR2_COLUMN) Cannot reference user-defined PL/SQL functions
		Cannot reference SYSDATE, USERENV, TO_DATE, UID, USER, LEVEL, or ROWNUM Cannot evaluate to a constant Cannot contain a subquery Cannot contain columns qualified with a schema or object name (other than the cluster name)
	If you omit the HASH IS clause, Oracle uses an internal hash function for the hash cluster.
	The cluster key of a hash column can have one or more columns of any datatype. Hash clusters with composite cluster keys or cluster keys made up of noninteger columns must use the internal hash function.
*parallel_clause*	causes creation of the cluster to be parallelized. See also the Notes to the parallel_clause of "CREATE TABLE".
	NOPARALLEL	specifies serial execution. This is the default.
	PARALLEL	causes Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.
	PARALLEL integer	specifies the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.
	Restriction: If the tables in cluster contain any columns of LOB or user-defined object type, this statement as well as subsequent INSERT, UPDATE, or DELETE operations on cluster are executed serially without notification.
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 clause 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.

7SQL Statements (continued)

CALL

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

COMMENT

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

COMMIT

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

INSERT Example

COMMENT Example

In-Doubt Transaction Example

constraint_clause

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

NOT NULL Example

Unique Key Example

Composite Unique Key Example

Primary Key Example

Composite Primary Key Example

Referential Integrity Constraint Example

ON DELETE Example

Composite Referential Integrity Constraint Example

CHECK Constraint Examples

DEFERRABLE Constraint Examples

CREATE CLUSTER

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

Creating a Cluster

Adding Tables to a Cluster

Cluster Key Example

Hash Cluster Examples

Single-Table Hash Cluster Example

7
SQL Statements (continued)