Oracle8 Application Developer's Guide Release 8.0 A58241-01 |
Library | Product | Contents | Index

# 4
Managing Schema Objects

This chapter discusses the procedures necessary to create and manage the different types of objects contained in a user's schema. The topics included are:

- Managing Tables
- Managing Views
- Modifying a Join View
- Managing Sequences
- Managing Synonyms
- Managing Indexes
- Managing Clusters, Clustered Tables, and Cluster Indexes
- Managing Hash Clusters and Clustered Tables
- Miscellaneous Management Topics for Schema Objects

  See Also: Specific information is described in the following locations: Procedures, functions, and packages - Chapter 10 Object types - Chapter 7 Dependency information - Chapter 15. If you use symmetric replication, see Oracle8 Replication for information on managing schema objects, such as snapshots. If you use Trusted Oracle, there are additional privileges required and issues to consider when managing schema objects; see the Trusted Oracle documentation.

## Managing Tables

A table is the data structure that holds data in a relational database. A table is composed of rows and columns.

A table can represent a single entity that you want to track within your system. Such a table might represent a list of the employees within your organization or the orders placed for your company's products.

A table can also represent a relationship between two entities. Such a table could be used to portray the association between employees and their job skills or the relationship of products to orders. Within the tables, foreign keys are used to represent relationships.

Although some well designed tables might both represent an entity and describe the relationship between that entity and another entity, most tables should represent either an entity or a relationship. For example, the EMP table describes the employees in a firm, but this table also includes a foreign key column, DEPTNO, which represents the relationships of employees to departments.

The following sections explain how to create, alter, and drop tables. Some simple guidelines to follow when managing tables in your database are included; see the Oracle8 Administrator's Guide for more suggestions. You should also refer to a text on relational database or table design.

### Designing Tables

You should consider the following guidelines when designing your tables:

- Use descriptive names for tables, columns, indexes, and clusters.
- Be consistent in abbreviations and in the use of singular and plural forms of table names and columns.
- Document the meaning of each table and its columns with the COMMENT command.
- Normalize each table.
- Select the appropriate datatype for each column.
- Define columns that allow nulls last, to conserve storage space.
- Cluster tables whenever appropriate, to conserve storage space and optimize performance of SQL statements.

Before creating a table, you should also determine whether to use integrity constraints. Integrity constraints can be defined on the columns of a table to enforce the business rules of your database automatically; see Chapter 9, "Maintaining Data Integrity" for guidelines.

### Creating Tables

To create a table, use the SQL command `CREATE TABLE`. For example, if the user `SCOTT` issues the following statement, he creates a non-clustered table named EMP in his schema that is physically stored in the `USERS` tablespace. Notice that integrity constraints are defined on several columns of the table.

    CREATE TABLE emp (
       empno      NUMBER(5) PRIMARY KEY,
       ename      VARCHAR2(15) NOT NULL,
       job        VARCHAR2(10),
       mgr        NUMBER(5),
       hiredate   DATE DEFAULT (sysdate),
       sal        NUMBER(7,2),
       comm       NUMBER(7,2),
       deptno     NUMBER(3) NOT NULL
                  CONSTRAINT dept_fkey REFERENCES dept)
       PCTFREE 10
       PCTUSED 40
       TABLESPACE users
       STORAGE (  INITIAL 50K
                  NEXT 50K
                  MAXEXTENTS 10
                    PCTINCREASE 25 );

#### Managing the Space Usage of Data Blocks

The following sections explain how to use the `PCTFREE` and `PCTUSED` parameters to do the following:

- increase the performance of writing and retrieving a data or index segment
- decrease the amount of unused space in data blocks
- decrease the amount of row chaining between data blocks

#### Specifying PCTFREE

The `PCTFREE` default is 10 percent; any integer from 0 to 99 is acceptable, as long as the sum of `PCTFREE` and `PCTUSED` does not exceed 100. (If `PCTFREE` is set to 99, Oracle puts at least one row in each block, regardless of row size. If the rows are very small and blocks very large, even more than one row might fit.)

A lower `PCTFREE`:

- reserves less room for updates to existing table rows
- allows inserts to fill the block more completely
- might save space, because the total data for a table or index is stored in fewer blocks (more rows or entries per block)
- increases processing costs because blocks frequently need to be reorganized as their free space area becomes filled with new or updated data
- potentially increases processing costs and space required if updates to rows or index entries cause rows to grow and span blocks (because `UPDATE`, `DELETE`, and `SELECT` statements might need to read more blocks for a given row and because chained row pieces contain references to other pieces)

A higher `PCTFREE`:

- reserves more room for future updates to existing table rows
- might require more blocks for the same amount of inserted data (inserting fewer rows per block)
- lessens processing costs because blocks infrequently need reorganization of their free space area
- might improve update performance, because Oracle must chain row pieces less frequently, if ever

In setting `PCTFREE`, you should understand the nature of the table or index data. Updates can cause rows to grow. When using `NUMBER`, `VARCHAR2`, `LONG`, or LONG RAW, new values might not be the same size as values they replace. If there are many updates in which data values get longer, increase `PCTFREE`; if updates to rows do not affect the total row width, then `PCTFREE` can be low.

Your goal is to find a satisfactory trade-off between densely packed data (low `PCTFREE`, full blocks) and good update performance (high `PCTFREE`, less-full blocks).

`PCTFREE` also affects the performance of a given user's queries on tables with uncommitted transactions belonging to other users. Assuring read consistency might cause frequent reorganization of data in blocks that have little free space.

##### PCTFREE for Non-Clustered Tables

If the data in the rows of a non-clustered table is likely to increase in size over time, reserve space for these updates. If you do not reserve room for updates, updated rows are likely to be chained between blocks, reducing I/O performance associated with these rows.

##### PCTFREE for Clustered Tables

The discussion for non-clustered tables also applies to clustered tables. However, if `PCTFREE` is reached, new rows from any table contained in the same cluster key go into a new data block chained to the existing cluster key.

##### PCTFREE for Indexes

Indexes infrequently require the use of free space for updates to index data. Therefore, the `PCTFREE` value for index segment data blocks is normally very low (for example, 5 or less).

#### Specifying PCTUSED

Once the percentage of free space in a data block reaches `PCTFREE`, no new rows are inserted in that block until the percentage of space used falls below `PCTUSED`. Oracle tries to keep a data block at least `PCTUSED` full. The percent is of block space available for data after overhead is subtracted from total space.

The default for `PCTUSED` is 40 percent; any integer between 0 and 99, inclusive, is acceptable as long as the sum of `PCTUSED` and `PCTFREE` does not exceed 100.

A lower `PCTUSED`:

- usually keeps blocks less full than a higher PCTUSED
- reduces processing costs incurred during UPDATE and DELETE statements for moving a block to the free list when the block has fallen below that percentage of usage
- increases the unused space in a database

A higher PCTUSED:

- usually keeps blocks fuller than a lower PCTUSED
- improves space efficiency
- increases processing cost during INSERTs and UPDATEs

#### Choosing Associated PCTUSED and PCTFREE Values

If you decide not to use the default values for PCTFREE and PCTUSED, use the following guidelines.

- The sum of PCTFREE and PCTUSED must be equal to or less than 100.
- If the sum is less than 100, the ideal compromise of space utilization and I/O performance is a sum of PCTFREE and PCTUSED that differs from 100 by the percentage of space in the available block that an average row occupies. For example, assume that the data block size is 2048 bytes, minus 100 bytes of overhead, leaving 1948 bytes available for data. If an average row requires 195 bytes, or 10% of 1948, then an appropriate combination of PCTUSED and PCTFREE that sums to 90% would make the best use of database space.
- If the sum equals 100, Oracle attempts to keep no more than PCTFREE free space, and the processing costs are highest.
- Fixed block overhead is not included in the computation of PCTUSED or PCTFREE.
- The smaller the difference between 100 and the sum of PCTFREE and PCTUSED (as in PCTUSED of 75, PCTFREE of 20), the more efficient space usage is at some performance cost.

#### Examples of Choosing PCTFREE and PCTUSED Values

The following examples illustrate correctly specifying values for PCTFREE and PCTUSED in given scenarios.

##### Example1

Scenario: | Common activity includes `UPDATE` statements that increase the size of the rows. Performance is important.
Settings: | `PCTFREE` = 20`` `PCTUSED` = 40
Explanation: | `PCTFREE` is set to 20 to allow enough room for rows that increase in size as a result of updates. `PCTUSED` is set to 40 so that less processing is done during high update activity, thus improving performance.

##### Example2

Scenario: | Most activity includes `INSERT` and `DELETE` statements, and UPDATE statements that do not increase the size of affected rows. Performance is important
Settings: | `PCTFREE` = 5`` `PCTUSED` = 60
Explanation: | `PCTFREE` is set to 5 because most `UPDATE` statements do not increase row sizes. `PCTUSED` is set to 60 so that space freed by `DELETE` statements is used relatively soon, yet the amount of processing is minimized.

Example3

Scenario: | The table is very large; therefore, storage is a primary concern. Most activity includes read-only transactions; therefore, query performance is important.
Settings: | `PCTFREE` = 5`` `PCTUSED` = 90
Explanation: | `PCTFREE` is set to 5 because `UPDATE` statements are rarely issued. `PCTUSED` is set to 90 so that more space per block is used to store table data. This setting for `PCTUSED` reduces the number of data blocks required to store the table's data and decreases the average number of data blocks to scan for queries, thereby increasing the performance of queries.

#### Privileges Required to Create a Table

To create a new table in your schema, you must have the `CREATE TABLE` system privilege. To create a table in another user's schema, you must have the `CREATE ANY TABLE` system privilege. Additionally, the owner of the table must have a quota for the tablespace that contains the table, or the `UNLIMITED TABLESPACE` system privilege.

### Altering Tables

You might alter a table in an Oracle database for any of the following reasons:

- to add one or more new columns to the table
- to add one or more integrity constraints to a table
- to modify an existing column's definition (datatype, length, default value, and `NOT NULL` integrity constraint)
- to modify data block space usage parameters (`PCTFREE, PCTUSED`)
- to modify transaction entry settings (`INITRANS, MAXTRANS`)
- to modify storage parameters (`NEXT, PCTINCREASE,` etc.)
- to enable or disable integrity constraints associated with the table
- to drop integrity constraints associated with the table

When altering the column definitions of a table, you can only increase the length of an existing column, unless the table has no records. You can also decrease the length of a column in an empty table. For columns of datatype CHAR, increasing the length of a column might be a time consuming operation that requires substantial additional storage, especially if the table contains many rows. This is because the CHAR value in each row must be blank-padded to satisfy the new column length.

If you change the datatype (for example, from `VARCHAR2` to `CHAR`), the data in the column does not change. However, the length of new `CHAR` columns might change, due to blank-padding requirements.

Use the SQL command `ALTER` `TABLE` to alter a table, as in

    ALTER TABLE emp
        PCTFREE 30
        PCTUSED 60;

Altering a table has the following implications:

- If a new column is added to a table, the column is initially null. You can add a column with a `NOT NULL` constraint to a table only if the table does not contain any rows.
- If a view or PL/SQL program unit depends on a base table, the alteration of the base table might affect the dependent object, and always invalidates the dependent object.

#### Privileges Required to Alter a Table

To alter a table, the table must be contained in your schema, or you must have either the ALTER object privilege for the table or the `ALTER ANY TABLE` system privilege.

### Dropping Tables

Use the SQL command `DROP TABLE` to drop a table. For example, the following statement drops the `EMP` table:

    DROP TABLE emp;

If the table that you are dropping contains any primary or unique keys referenced by foreign keys of other tables, and you intend to drop the `FOREIGN KEY` constraints of the child tables, include the `CASCADE` option in the `DROP TABLE` command, as in

    DROP TABLE emp CASCADE CONSTRAINTS;

Dropping a table has the following effects:

- The table definition is removed from the data dictionary. All rows of the table are then inaccessible.
- All indexes and triggers associated with the table are dropped.
- All views and PL/SQL program units that depend on a dropped table remain, yet become invalid (not usable).
- All synonyms for a dropped table remain, but return an error when used.
- All extents allocated for a non-clustered table that is dropped are returned to the free space of the tablespace and can be used by any other object requiring new extents.
- All rows corresponding to a clustered table are deleted from the blocks of the cluster.
- If the table is a master table for snapshots, Oracle does not drop the snapshots, but does drop the snapshot log. The snapshots can still be used, but they cannot be refreshed unless the table is re-created.

If you want to delete all of the rows of a table, but keep the table definition, you should use the `TRUNCATE` `TABLE` command. This command is described in the Oracle8 Administrator's Guide.

#### Privileges Required to Drop a Table

To drop a table, the table must be contained in your schema or you must have the `DROP ANY TABLE` system privilege.

## Managing Views

A view is a logical representation of another table or combination of tables. A view derives its data from the tables on which it is based. These tables are called base tables. Base tables might in turn be actual tables or might be views themselves.

All operations performed on a view actually affect the base table of the view. You can use views in almost the same way as tables. You can query, update, insert into, and delete from views, just as you can standard tables.

Views can provide a different representation (such as subsets or supersets) of the data that resides within other tables and views. Views are very powerful because they allow you to tailor the presentation of data to different types of users.

The following sections explain how to create, replace, and drop views using SQL commands.

### Creating Views

Use the SQL command `CREATE VIEW` to create a view. You can define views with any query that references tables, snapshots, or other views; however, the query that defines a view cannot contain the ORDER BY or FOR UPDATE clauses. For example, the following statement creates a view on a subset of data in the EMP table:

    CREATE VIEW sales_staff AS
        SELECT empno, ename, deptno
        FROM emp
        WHERE deptno = 10
        WITH CHECK OPTION CONSTRAINT sales_staff_cnst;

The query that defines the `SALES_STAFF` view references only rows in department 10. Furthermore, the `WITH CHECK OPTION` creates the view with the constraint that `INSERT` and `UPDATE` statements issued against the view are not allowed to create or result in rows that the view cannot select.

Considering the example above, the following `INSERT` statement successfully inserts a row into the `EMP` table via the `SALES_STAFF` view:

    INSERT INTO sales_staff VALUES (7584, 'OSTER', 10);

However, the following `INSERT` statement is rolled back and returns an error because it attempts to insert a row for department number 30, which could not be selected using the `SALES_STAFF` view:

    INSERT INTO sales_staff VALUES (7591, 'WILLIAMS', 30);

The following statement creates a view that joins data from the `EMP` and `DEPT` tables:

    CREATE VIEW division1_staff AS
        SELECT ename, empno, job, dname
        FROM emp, dept
        WHERE emp.deptno IN (10, 30)
        AND emp.deptno = dept.deptno;

The `DIVISION1_STAFF` view is defined by a query that joins information from the `EMP` and `DEPT` tables. The `WITH CHECK OPTION` is not specified in the `CREATE` `VIEW` statement because rows cannot be inserted into or updated in a view defined with a query that contains a join that uses the `WITH CHECK OPTION`; see page 4-13 and page 4-15 and following.

#### Expansion of Defining Queries at View Creation Time

In accordance with the ANSI/ISO standard, Oracle expands any wildcard in a top-level view query into a column list when a view is created and stores the resulting query in the data dictionary; any subqueries are left intact. The column names in an expanded column list are enclosed in quote marks to account for the possibility that the columns of the base object were originally entered with quotes and require them for the query to be syntactically correct.

As an example, assume that the DEPT view is created as follows:

    CREATE VIEW dept AS SELECT * FROM scott.dept;

Oracle stores the defining query of the DEPT view as

    SELECT "DEPTNO", "DNAME", "LOC" FROM scott.dept

Views created with errors do not have wildcards expanded. However, if the view is eventually compiled without errors, wildcards in the defining query are expanded.

#### Creating Views with Errors

Assuming no syntax errors, a view can be created (with errors) even if the defining query of the view cannot be executed. For example, if a view is created that refers to a non-existent table or an invalid column of an existing table, or if the owner of the view does not have the required privileges, the view can still be created and entered into the data dictionary.

You can only create a view with errors by using the `FORCE` option of the `CREATE` `VIEW` command:

    CREATE FORCE VIEW AS ...;

When a view is created with errors, Oracle returns a message that indicates the view was created with errors. The status of such a view is left as `INVALID`. If conditions later change so that the query of an invalid view can be executed, the view can be recompiled and become valid. Oracle dynamically compiles the invalid view if you attempt to use it.

#### Privileges Required to Create a View

To create a view, you must have been granted the following privileges:

- You must have the `CREATE VIEW` system privilege to create a view in your schema or the `CREATE ANY VIEW` system privilege to create a view in another user's schema. These privileges can be acquired explicitly or via a role.
- The owner of the view must have been explicitly granted the necessary privileges to access all objects referenced within the definition of the view; the owner cannot have obtained the required privileges through roles. Also, the functionality of the view is dependent on the privileges of the view's owner. For example, if you (the view owner) are granted only the `INSERT` privilege for Scott's `EMP` table, you can create a view on his EMP table, but you can only use this view to insert new rows into the EMP table.
- If the view owner intends to grant access to the view to other users, the owner must have received the object privileges to the base objects with the `GRANT` `OPTION` or the system privileges with the `ADMIN` `OPTION`; if not, the view owner has insufficient privileges to grant access to the view to other users.

### Replacing Views

To alter the definition of a view, you must replace the view using one of the following methods:

- A view can be dropped and then re-created. When a view is dropped, all grants of corresponding view privileges are revoked from roles and users. After the view is re-created, necessary privileges must be regranted.
- A view can be replaced by redefining it with a `CREATE` `VIEW` statement that contains the `OR` `REPLACE` option. This option is used to replace the current definition of a view but preserve the present security authorizations. For example, assume that you create the `SALES_STAFF` view, as given in a previous example. You also grant several object privileges to roles and other users. However, now you realize that you must redefine the `SALES_STAFF` view to correct the department number specified in the `WHERE` clause of the defining query, because it should have been 30. To preserve the grants of object privileges that you have made, you can replace the current version of the `SALES_STAFF` view with the following statement:

      CREATE OR REPLACE VIEW sales_staff AS
          SELECT empno, ename, deptno
          FROM emp
          WHERE deptno = 30
          WITH CHECK OPTION CONSTRAINT sales_staff_cnst;

Replacing a view has the following effects:

- Replacing a view replaces the view's definition in the data dictionary. All underlying objects referenced by the view are not affected.
- If previously defined but not included in the new view definition, the constraint associated with the `WITH CHECK OPTION` for a view's definition is dropped.
- All views and PL/SQL program units dependent on a replaced view become invalid.

#### Privileges Required to Replace a View

To replace a view, you must have all of the privileges needed to drop the view, as well as all of those required to create the view.

### Using Views

Views can be queried in the same manner as tables. For example, to query the DIVISION1_STAFF view, enter a valid SELECT statement that references the view:

    SELECT * FROM division1_staff;

    ENAME        EMPNO       JOB             DNAME
    ------------------------------------------------------
    CLARK         7782       MANAGER         ACCOUNTING
    KING          7839       PRESIDENT       ACCOUNTING
    MILLER        7934       CLERK           ACCOUNTING
    ALLEN         7499       SALESMAN        SALES
    WARD          7521       SALESMAN        SALES
    JAMES         7900       CLERK           SALES
    TURNER        7844       SALESMAN        SALES
    MARTIN        7654       SALESMAN        SALES
    BLAKE         7698       MANAGER         SALES

With some restrictions, rows can be inserted into, updated in, or deleted from a base table using a view. The following statement inserts a new row into the EMP table using the `SALES_STAFF` view:

    INSERT INTO sales_staff
        VALUES (7954, 'OSTER', 30);

Restrictions on DML operations for views use the following criteria in the order listed:

1. If a view is defined by a query that contains `SET` or `DISTINCT` operators, a GROUP BY clause, or a group function, rows cannot be inserted into, updated in, or deleted from the base tables using the view.
2. If a view is defined with the `WITH CHECK OPTION`, a row cannot be inserted into, or updated in, the base table (using the view) if the view cannot select the row from the base table.
3. If a `NOT NULL` column that does not have a `DEFAULT` clause is omitted from the view, a row cannot be inserted into the base table using the view.
4. If the view was created by using an expression, such as DECODE(deptno, 10, "`SALES`", ...), rows cannot be inserted into or updated in the base table using the view.

The constraint created by the `WITH CHECK OPTION` of the `SALES_STAFF` view only allows rows that have a department number of 10 to be inserted into, or updated in, the `EMP` table. Alternatively, assume that the `SALES_STAFF` view is defined by the following statement (that is, excluding the `DEPTNO` column):

    CREATE VIEW sales_staff AS
        SELECT empno, ename
        FROM emp
        WHERE deptno = 10
        WITH CHECK OPTION CONSTRAINT sales_staff_cnst;

Considering this view definition, you can update the `EMPNO` or `ENAME` fields of existing records, but you cannot insert rows into the EMP table via the `SALES_STAFF` view because the view does not let you alter the `DEPTNO` field. If you had defined a `DEFAULT` value of 10 on the `DEPTNO` field, you could perform inserts.

##### Referencing Invalid Views

When a user attempts to reference an invalid view, Oracle returns an error message to the user:

    ORA-04063: view 'view_name' has errors

This error message is returned when a view exists but is unusable due to errors in its query (whether it had errors when originally created or it was created successfully but became unusable later because underlying objects were altered or dropped).

#### Privileges Required to Use a View

To issue a query or an `INSERT, UPDATE`, or `DELETE` statement against a view, you must have the `SELECT, INSERT, UPDATE,` or `DELETE` object privilege for the view, respectively, either explicitly or via a role.

### Dropping Views

Use the SQL command `DROP VIEW` to drop a view, as in

    DROP VIEW sales_staff;

#### Privileges Required to Drop a View

You can drop any view contained in your schema. To drop a view in another user's schema, you must have the `DROP ANY VIEW` system privilege.

## Modifying a Join View

The Oracle Server allows you, with some restrictions, to modify views that involve joins. Consider the following simple view:

    CREATE VIEW emp_view AS
        SELECT ename, empno, deptno FROM emp;

This view does not involve a join operation. If you issue the SQL statement:

    UPDATE emp_view SET ename = 'CAESAR' WHERE empno = 7839;

then the `EMP` base table that underlies the view changes, and employee 7839's name changes from `KING` to `CAESAR` in the `EMP` table.

However, if you create a view that involves a join operation, such as:

    CREATE VIEW emp_dept AS
      SELECT e.empno, e.ename, e.deptno, d.dname, d.loc
        FROM emp e, dept d    /* JOIN operation */
         WHERE e.deptno = d.deptno
           AND d.loc IN ('DALLAS', 'NEW YORK', 'BOSTON');

then there are restrictions on modifying either the `EMP` or the `DEPT` base table through this view, for example, using a statement such as:

    UPDATE emp_dept_view SET ename = 'JOHNSON'
        WHERE ename = 'SMITH';

A modifiable join view is a view that contains more than one table in the top-level `FROM` clause of the `SELECT` statement, and that does not contain any of the following:

- DISTINCT operator
- aggregate functions: `AVG, COUNT, GLB, MAX, MIN, STDDEV, SUM,` or `VARIANCE`
- set operations: `UNION, UNION ALL, INTERSECT, MINUS`
- `GROUP BY` or `HAVING` clauses
- `START WITH` or `CONNECT BY` clauses
- `ROWNUM` pseudocolumn

A further restriction on which join views are modifiable is that if a view is a join on other nested views, then the other nested views must be mergeable into the top level view. See Oracle8 Concepts for more information about mergeable views.

#### Example Tables

The examples in this section use the familiar `EMP` and `DEPT` tables. However, the examples work only if you explicitly define the primary and foreign keys in these tables, or define unique indexes. Here are the appropriately constrained table definitions for `EMP` and `DEPT`:

    CREATE TABLE dept (
            deptno   NUMBER(4) PRIMARY KEY,
            dname    VARCHAR2(14),
            loc      VARCHAR2(13));

    CREATE TABLE emp (
            empno    NUMBER(4) PRIMARY KEY,
            ename    VARCHAR2(10),
            job      varchar2(9),
            mgr      NUMBER(4),
            hiredate DATE,
            sal      NUMBER(7,2),
            comm     NUMBER(7,2),
            deptno   NUMBER(2),
    FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO));.

You could also omit the primary and foreign key constraints listed above, and create a `UNIQUE INDEX` on `DEPT (DEPTNO)` to make the following examples work.

### Key-Preserved Tables

The concept of a key-preserved table is fundamental to understanding the restrictions on modifying join views. A table is key preserved if every key of the table can also be a key of the result of the join. So, a key-preserved table has its keys preserved through a join.

Note: It is not necessary that the key or keys of a table be selected for it to be key preserved. It is sufficient that if the key or keys were selected, then they would also be key(s) of the result of the join. The key-preserving property of a table does not depend on the actual data in the table. It is, rather, a property of its schema and not of the data in the table. For example, if in the `EMP` table there was at most one employee in each department, then `DEPT.DEPTNO` would be unique in the result of a join of `EMP` and `DEPT`, but `DEPT` would still not be a key-preserved table.

If you `SELECT` all rows from `EMP_DEPT_VIEW` defined in "Modifying a Join View" on page 4-15, the results are

    EMPNO      ENAME      DEPTNO    DNAME          LOC
    ---------------------------------------------------------
    7782       CLARK      10       ACCOUNTING      NEW YORK
    7839       KING       10       ACCOUNTING      NEW YORK
    7934       MILLER     10       ACCOUNTING      NEW YORK
    7369       SMITH      20       RESEARCH        DALLAS
    7876       ADAMS      20       RESEARCH        DALLAS
    7902       FORD       20       RESEARCH        DALLAS
    7788       SCOTT      20       RESEARCH        DALLAS
    7566       JONES      20       RESEARCH        DALLAS
    8 rows selected.

In this view, `EMP` is a key-preserved table, because `EMPNO` is a key of the `EMP` table, and also a key of the result of the join. `DEPT` is not a key-preserved table, because although `DEPTNO` is a key of the `DEPT` table, it is not a key of the join.

### Rule for DML Statements on Join Views

Any `UPDATE`, `INSERT`, or `DELETE` statement on a join view can modify only one underlying base table.

#### UPDATE Statements

The following example shows an `UPDATE` statement that successfully modifies the `EMP_DEPT` view (shown on page 4-15):

    UPDATE emp_dept
      SET sal = sal * 1.10
        WHERE deptno = 10;

The following `UPDATE` statement would be disallowed on the `EMP_DEPT` view:

    UPDATE emp_dept
      SET loc = 'BOSTON'
        WHERE ename = 'SMITH';

This statement fails with an `ORA-01779` error ("cannot modify a column which maps to a non key-preserved table"), because it attempts to modify the underlying `DEPT` table, and the `DEPT` table is not key preserved in the `EMP_DEPT` view.

In general, all modifiable columns of a join view must map to columns of a key-preserved table. If the view is defined using the `WITH CHECK OPTION` clause, then all join columns and all columns of repeated tables are not modifiable.

So, for example, if the `EMP_DEPT` view were defined using `WITH CHECK OPTION,` the following UPDATE statement would fail:

    UPDATE emp_dept
        SET deptno = 10
            WHERE ename = 'SMITH';

The statement fails because it is trying to update a join column.

#### DELETE Statements

You can delete from a join view provided there is one and only one key-preserved table in the join.

The following `DELETE` statement works on the `EMP_DEPT` view:

    DELETE FROM emp_dept
        WHERE ename = 'SMITH';

This `DELETE` statement on the `EMP_DEPT` view is legal because it can be translated to a `DELETE` operation on the base `EMP` table, and because the `EMP` table is the only key-preserved table in the join.

In the following view, a `DELETE` operation cannot be performed on the view because both `E1` and `E2` are key-preserved tables:

    CREATE VIEW emp_emp AS
        SELECT e1.ename, e2.empno, deptno
            FROM emp e1, emp e2
            WHERE e1.empno = e2.empno;

If a view is defined using the`WITH CHECK OPTION` clause and the key-preserved table is repeated, then rows cannot be deleted from such a view. For example:

    CREATE VIEW emp_mgr AS
        SELECT e1.ename, e2.ename mname
            FROM emp e1, emp e2
                WHERE e1.mgr = e2.empno
                WITH CHECK OPTION;

No deletion can be performed on this view because the view involves a self-join of the table that is key preserved.

#### INSERT Statements

The following `INSERT` statement on the `EMP_DEPT` view succeeds:

    INSERT INTO emp_dept (ename, empno, deptno)
        VALUES ('KURODA', 9010, 40);

because only one key-preserved base table is being modified (`EMP`), and 40 is a valid `DEPTNO` in the `DEPT` table (thus satisfying the `FOREIGN KEY` integrity constraint on the EMP table).

An `INSERT` statement such as

    INSERT INTO emp_dept (ename, empno, deptno)
        VALUES ('KURODA', 9010, 77);

would fail for the same reason that such an `UPDATE` on the base `EMP` table would fail: the `FOREIGN` `KEY` integrity constraint on the `EMP` table is violated.

An `INSERT` statement such as

    INSERT INTO emp_dept (empno, ename, loc)
        VALUES (9010, 'KURODA', 'BOSTON');

would fail with an `ORA-01776` error ("cannot modify more than one base table through a view").

An `INSERT` cannot, implicitly or explicitly, refer to columns of a non-key-preserved table. If the join view is defined using the `WITH CHECK OPTION` clause, then you cannot perform an `INSERT` to it.

### Using the UPDATABLE_COLUMNS Views

Three views you can use for modifying join views are shown in Table 4-1.

##### Table 4-1 UPDATABLE_COLUMNS Views

View Name | Description
`USER_UPDATABLE_COLUMNS` | Shows all columns in all tables and views in the user's schema that are modifiable.
`DBA_UPDATABLE_COLUMNS` | Shows all columns in all tables and views in the DBA schema that are modifiable.
`ALL_UPDATABLE_VIEWS` | Shows all columns in all tables and views that are modifiable.

### Outer Joins

Views that involve outer joins are modifiable in some cases. For example:

    CREATE VIEW emp_dept_oj1 AS
        SELECT empno, ename, e.deptno, dname, loc
        FROM emp e, dept d
        WHERE e.deptno = d.deptno (+);

The statement

    SELECT * FROM emp_dept_oj1;

results in:

    EMPNO   ENAME      DEPTNO  DNAME          LOC
    ------- ---------- ------- -------------- -------------
    7369    SMITH      40      OPERATIONS     BOSTON
    7499    ALLEN      30      SALES          CHICAGO
    7566    JONES      20      RESEARCH       DALLAS
    7654    MARTIN     30      SALES          CHICAGO
    7698    BLAKE      30      SALES          CHICAGO
    7782    CLARK      10      ACCOUNTING     NEW YORK
    7788    SCOTT      20      RESEARCH       DALLAS
    7839    KING       10      ACCOUNTING     NEW YORK
    7844    TURNER     30      SALES          CHICAGO
    7876    ADAMS      20      RESEARCH       DALLAS
    7900    JAMES      30      SALES          CHICAGO
    7902    FORD       20      RESEARCH       DALLAS
    7934    MILLER     10      ACCOUNTING     NEW YORK
    7521    WARD       30      SALES          CHICAGO
    14 rows selected.

Columns in the base `EMP` table of `EMP_DEPT_OJ1` are modifiable through the view, because `EMP` is a key-preserved table in the join.

The following view also contains an outer join:

    CREATE VIEW emp_dept_oj2 AS

    SELECT e.empno, e.ename, e.deptno, d.dname, d.loc
    FROM emp e, dept d
    WHERE e.deptno (+) = d.deptno;

The statement

    SELECT * FROM emp_dept_oj2;

results in:

    EMPNO      ENAME      DEPTNO    DNAME          LOC
    ---------- ---------- --------- -------------- ----
    7782       CLARK      10        ACCOUNTING     NEW YORK
    7839       KING       10        ACCOUNTING     NEW YORK
    7934       MILLER     10        ACCOUNTING     NEW YORK
    7369       SMITH      20        RESEARCH       DALLAS
    7876       ADAMS      20        RESEARCH       DALLAS
    7902       FORD       20        RESEARCH       DALLAS
    7788       SCOTT      20        RESEARCH       DALLAS
    7566       JONES      20        RESEARCH       DALLAS
    7499       ALLEN      30        SALES          CHICAGO
    7698       BLAKE      30        SALES          CHICAGO
    7654       MARTIN     30        SALES          CHICAGO
    7900       JAMES      30        SALES          CHICAGO
    7844       TURNER     30        SALES          CHICAGO
    7521       WARD       30        SALES          CHICAGO
                                    OPERATIONS     BOSTON
    15 rows selected.

In this view, `EMP` is no longer a key-preserved table, because the `EMPNO` column in the result of the join can have nulls (the last row in the `SELECT` above). So, `UPDATE, DELETE,` and `INSERT` operations cannot be performed on this view.

In the case of views containing an outer join on other nested views, a table is key preserved if the view or views containing the table are merged into their outer views, all the way to the top. A view which is being outer-joined is currently merged only if it is "simple." For example:

    SELECT col1, col2, ... FROM T;

that is, the select list of the view has no expressions, and there is no WHERE clause.

Consider the following set of views:

    CREATE emp_v AS

    SELECT empno, ename, deptno

    FROM emp;

    CREATE VIEW emp_dept_oj1 AS

    SELECT e.*, loc, d.dname

    FROM emp_v e, dept d

    WHERE e.deptno = d.deptno (+);

In these examples, `EMP_V` is merged into `EMP_DEPT_OJ1` because `EMP_V` is a simple view, and so `EMP` is a key-preserved table. But if `EMP_V` is changed as follows:

    CREATE emp_v_2 AS

    SELECT empno, ename, deptno

    FROM emp

    WHERE sal > 1000;

then, because of the presence of the `WHERE` clause, `EMP_V_2` cannot be merged into `EMP_DEPT_OJ1`, and hence `EMP` is no longer a key-preserved table.

If you are in doubt whether a view is modifiable, you can `SELECT` from the view `USER_UPDATABLE_COLUMNS` to see if it is. For example:

    SELECT * FROM USER_UPDATABLE_COLUMNS WHERE TABLE_NAME = 'EMP_DEPT_VIEW';

might return:

    OWNER       TABLE_NAME      COLUMN_NAM      UPD
    ----------  ----------      ----------      ---
    SCOTT       EMP_DEPT_V      EMPNO           NO
    SCOTT       EMP_DEPT_V      ENAME           NO
    SCOTT       EMP_DEPT_V      DEPTNO          NO
    SCOTT       EMP_DEPT_V      DNAME           NO
    SCOTT       EMP_DEPT_V      LOC             NO
    5 rows selected.

## Managing Sequences

The sequence generator generates sequential numbers. Sequence number generation is useful to generate unique primary keys for your data automatically, and to coordinate keys across multiple rows or tables.

Without sequences, sequential values can only be produced programmatically. A new primary key value can be obtained by selecting the most recently produced value and incrementing it. This method requires a lock during the transaction and causes multiple users to wait for the next value of the primary key; this waiting is known as serialization. If you have such constructs in your applications, you should replace them with access to sequences. Sequences eliminate serialization and improve the concurrency of your application.

The following sections explain how to create, alter, use, and drop sequences using SQL commands.

### Creating Sequences

Use the SQL command `CREATE SEQUENCE` to create a sequence. The following statement creates a sequence used to generate employee numbers for the `EMPNO` column of the `EMP` table:

    CREATE SEQUENCE emp_sequence

    INCREMENT BY 1
    START WITH 1
    NOMAXVALUE
    NOCYCLE
    CACHE 10;

Notice that several parameters can be specified to control the function of sequences. You can use these parameters to indicate whether the sequence is ascending or descending, the starting point of the sequence, the minimum and maximum values, and the interval between sequence values. The `NOCYCLE` option indicates that the sequence cannot generate more values after reaching its maximum or minimum value.

The `CACHE` option of the `CREATE SEQUENCE` command pre-allocates a set of sequence numbers and keeps them in memory so that they can be accessed faster. When the last of the sequence numbers in the cache have been used, another set of numbers is read into the cache.

For additional implications for caching sequence numbers when using the Oracle Parallel Server, see Oracle8 Parallel Server Concepts and Administration. General information about caching sequence numbers is included in "Caching Sequence Numbers" on page 4-28.

#### Privileges Required to Create a Sequence

To create a sequence in your schema, you must have the `CREATE SEQUENCE` system privilege. To create a sequence in another user's schema, you must have the `CREATE ANY SEQUENCE` privilege.

### Altering Sequences

You can change any of the parameters that define how corresponding sequence numbers are generated; however, you cannot alter a sequence to change the starting number of a sequence. To do this, the sequence must be dropped and re-created.

Use the SQL command `ALTER SEQUENCE` to alter a sequence, as in:

    ALTER SEQUENCE emp_sequence

    INCREMENT BY 10
    MAXVALUE 10000
    CYCLE
    CACHE 20;

#### Privileges Required to Alter a Sequence

To alter a sequence, your schema must contain the sequence, or you must have the `ALTER ANY SEQUENCE` system privilege.

### Using Sequences

The following sections provide some information on how to use a sequence once it has been defined. Once defined, a sequence can be made available to many users. A sequence can be accessed and incremented by multiple users with no waiting. Oracle does not wait for a transaction that has incremented a sequence to complete before that sequence can be incremented again.

The examples outlined in the following sections show how sequences can be used in master/detail table relationships. Assume an order entry system is partially comprised of two tables, ORDERS (master table) and LINE_ITEMS (detail table), that hold information about customer orders. A sequence named ORDER_SEQ is defined by the following statement:

    CREATE SEQUENCE order_seq

    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOCYCLE
    CACHE 20;

#### Referencing a Sequence

A sequence is referenced in SQL statements with the `NEXTVAL` and `CURRVAL` pseudocolumns; each new sequence number is generated by a reference to the sequence's pseudocolumn `NEXTVAL`, while the current sequence number can be repeatedly referenced using the pseudo-column `CURRVAL`.

`NEXTVAL` and `CURRVAL` are not reserved words or keywords and can be used as pseudo-column names in SQL statements such as `SELECT`s, `INSERT`s, or `UPDATE`s.

##### Generating Sequence Numbers with NEXTVAL

To generate and use a sequence number, reference seq_name.`NEXTVAL`. For example, assume a customer places an order. The sequence number can be referenced in a values list, as in:

    INSERT INTO orders (orderno, custno)

    VALUES (order_seq.NEXTVAL, 1032);

or in the `SET` clause of an `UPDATE` statement, as in:

    UPDATE orders

    SET orderno = order_seq.NEXTVAL
    WHERE orderno = 10112;

or the outermost SELECT of a query or subquery, as in

    SELECT order_seq.NEXTVAL FROM dual;

As defined, the first reference to O`RDER_SEQ.NEXTVAL` returns the value 1. Each subsequent statement that references `ORDER_SEQ.NEXTVAL` generates the next sequence number (2, 3, 4,. . .). The pseudo-column `NEXTVAL` can be used to generate as many new sequence numbers as necessary. However, only a single sequence number can be generated per row; that is, if `NEXTVAL` is referenced more than once in a single statement, the first reference generates the next number and all subsequent references in the statement return the same number.

Once a sequence number is generated, the sequence number is available only to the session that generated the number. Independent of transactions committing or rolling back, other users referencing `ORDER_SEQ.NEXTVAL` obtain unique values. If two users are accessing the same sequence concurrently, the sequence numbers each user receives might have gaps because sequence numbers are also being generated by the other user.

##### Using Sequence Numbers with CURRVAL

To use or refer to the current sequence value of your session, reference seq_name.`CURRVAL`. `CURRVAL` can only be used if seq_name.`NEXTVAL` has been referenced in the current user session (in the current or a previous transaction). `CURRVAL` can be referenced as many times as necessary, including multiple times within the same statement. The next sequence number is not generated until `NEXTVAL` is referenced. Continuing with the previous example, you would finish placing the customer's order by inserting the line items for the order:

    INSERT INTO line_items (orderno, partno, quantity)

    VALUES (order_seq.CURRVAL, 20321, 3);

    INSERT INTO line_items (orderno, partno, quantity)

    VALUES (order_seq.CURRVAL, 29374, 1);

Assuming the `INSERT` statement given in the previous section generated a new sequence number of 347, both rows inserted by the statements in this section insert rows with order numbers of 347.

##### Uses and Restrictions of NEXTVAL and CURRVAL

CURRVAL and NEXTVAL can be used in the following places:

- `VALUES` clause of `INSERT` statements
- the `SELECT` list of a `SELECT` statement
- the `SET` clause of an `UPDATE` statement

`CURRVAL` and `NEXTVAL` cannot be used in these places:

- a subquery
- a view's query or snapshot's query
- a `SELECT` statement with the `DISTINCT` operator
- a `SELECT` statement with a `GROUP BY` or `ORDER BY` clause
- a `SELECT` statement that is combined with another `SELECT` statement with the `UNION, INTERSECT`, or `MINUS` set operator
- the `WHERE` clause of a `SELECT` statement
- `DEFAULT` value of a column in a `CREATE TABLE` or `ALTER TABLE` statement
- the condition of a `CHECK` constraint

#### Caching Sequence Numbers

Sequence numbers can be kept in the sequence cache in the System Global Area (SGA). Sequence numbers can be accessed more quickly in the sequence cache than they can be read from disk.

The sequence cache consists of entries. Each entry can hold many sequence numbers for a single sequence.

Follow these guidelines for fast access to all sequence numbers:

- Be sure the sequence cache can hold all the sequences used concurrently by your applications.
- Increase the number of values for each sequence held in the sequence cache.

##### The Number of Entries in the Sequence Cache

When an application accesses a sequence in the sequence cache, the sequence numbers are read quickly. However, if an application accesses a sequence that is not in the cache, the sequence must be read from disk to the cache before the sequence numbers are used.

If your applications use many sequences concurrently, your sequence cache might not be large enough to hold all the sequences. In this case, access to sequence numbers might often require disk reads. For fast access to all sequences, be sure your cache has enough entries to hold all the sequences used concurrently by your applications.

The number of entries in the sequence cache is determined by the initialization parameter `SEQUENCE_CACHE_ENTRIES`. The default value for this parameter is 10 entries. Oracle creates and uses sequences internally for auditing, grants of system privileges, grants of object privileges, profiles, debugging stored procedures, and labels. Be sure your sequence cache has enough entries to hold these sequences as well as sequences used by your applications.

If the value for your `SEQUENCE_CACHE_ENTRIES` parameter is too low, it is possible to skip sequence values. For example, assume that this parameter is set to 4, and that you currently have four cached sequences. If you create a fifth sequence, it will replace the least recently used sequence in the cache. All of the remaining values in this displaced sequence are lost. That is, if the displaced sequence originally held 10 cached sequence values, and only one had been used, nine would be lost when the sequence was displaced.

##### The Number of Values in Each Sequence Cache Entry

When a sequence is read into the sequence cache, sequence values are generated and stored in a cache entry. These values can then be accessed quickly. The number of sequence values stored in the cache is determined by the `CACHE` parameter in the `CREATE SEQUENCE` statement. The default value for this parameter is 20.

This `CREATE` `SEQUENCE` statement creates the SEQ2 sequence so that 50 values of the sequence are stored in the `SEQUENCE` cache:

    CREATE SEQUENCE seq2

    CACHE 50

The first 50 values of SEQ2 can then be read from the cache. When the 51st value is accessed, the next 50 values will be read from disk.

Choosing a high value for CACHE allows you to access more successive sequence numbers with fewer reads from disk to the sequence cache. However, if there is an instance failure, all sequence values in the cache are lost. Cached sequence numbers also could be skipped after an export and import if transactions continue to access the sequence numbers while the export is running.

If you use the NOCACHE option in the CREATE SEQUENCE statement, the values of the sequence are not stored in the sequence cache. In this case, every access to the sequence requires a disk read. Such disk reads slow access to the sequence. This CREATE SEQUENCE statement creates the SEQ3 sequence so that its values are never stored in the cache:

    CREATE SEQUENCE seq3

    NOCACHE

#### Privileges Required to Use a Sequence

To use a sequence, your schema must contain the sequence or you must have been granted the SELECT object privilege for another user's sequence.

### Dropping Sequences

To drop a sequence, use the SQL command DROP SEQUENCE. For example, the following statement drops the ORDER_SEQ sequence:

    DROP SEQUENCE order_seq;

When you drop a sequence, its definition is removed from the data dictionary. Any synonyms for the sequence remain, but return an error when referenced.

#### Privileges Required to Drop a Sequence

You can drop any sequence in your schema. To drop a sequence in another schema, you must have the DROP ANY SEQUENCE system privilege.

## Managing Synonyms

A synonym is an alias for a table, view, snapshot, sequence, procedure, function, or package. The following sections explain how to create, use, and drop synonyms using SQL commands.

### Creating Synonyms

Use the SQL command `CREATE SYNONYM` to create a synonym. The following statement creates a public synonym named `PUBLIC_EMP` on the `EMP` table contained in the schema of `JWARD`:

    CREATE PUBLIC SYNONYM public_emp FOR jward.emp;

#### Privileges Required to Create a Synonym

You must have the `CREATE SYNONYM` system privilege to create a private synonym in your schema, or the`CREATE ANY SYNONYM` system privilege to create a private synonym in another user's schema. To create a public synonym, you must have the `CREATE PUBLIC SYNONYM` system privilege.

### Using Synonyms

A synonym can be referenced in a SQL statement the same way that the underlying object of the synonym can be referenced. For example, if a synonym named EMP refers to a table or view, the following statement is valid:

    INSERT INTO emp (empno, ename, job)
        VALUES (emp_sequence.NEXTVAL, 'SMITH', 'CLERK');

If the synonym named `FIRE_EMP` refers to a stand-alone procedure or package procedure, you could execute it in SQL*Plus or Enterprise Manager with the command

    EXECUTE fire_emp(7344);

#### Privileges Required to Use a Synonym

You can successfully use any private synonym contained in your schema or any public synonym, assuming that you have the necessary privileges to access the underlying object, either explicitly, from an enabled role, or from `PUBLIC`. You can also reference any private synonym contained in another schema if you have been granted the necessary object privileges for the private synonym. You can only reference another user's synonym using the object privileges that you have been granted. For example, if you have the `SELECT` privilege for the `JWARD`.`EMP` synonym, you can query the `JWARD`.`EMP` synonym, but you cannot insert rows using the synonym for `JWARD`.`EMP`.

### Dropping Synonyms

To drop a synonym, use the SQL command `DROP SYNONYM.` To drop a private synonym, omit the `PUBLIC` keyword; to drop a public synonym, include the `PUBLIC` keyword. The following statement drops the private synonym named `EMP`:

    DROP SYNONYM emp;

The following statement drops the public synonym named `PUBLIC_EMP`:

    DROP PUBLIC SYNONYM public_emp;

When you drop a synonym, its definition is removed from the data dictionary. All objects that reference a dropped synonym remain (for example, views and procedures) but become invalid.

#### Privileges Required to Drop a Synonym

You can drop any private synonym in your own schema. To drop a private synonym in another user's schema, you must have the `DROP ANY SYNONYM` system privilege. To drop a public synonym, you must have the `DROP PUBLIC SYNONYM` system privilege.

## Managing Indexes

Indexes are used in Oracle to provide quick access to rows in a table. Indexes provide faster access to data for operations that return a small portion of a table's rows.

Oracle does not limit the number of indexes you can create on a table. However, you should consider the performance benefits of indexes and the needs of your database applications to determine which columns to index.

The following sections explain how to create, alter, and drop indexes using SQL commands. Some simple guidelines to follow when managing indexes are included. See Oracle8 Tuning for performance implications of index creation.

#### Create Indexes After Inserting Table Data

With one notable exception, you should usually create indexes after you have inserted or loaded (using SQL*Loader or Import) data into a table. It is more efficient to insert rows of data into a table that has no indexes and then to create the indexes for subsequent queries, etc. If you create indexes before table data is loaded, every index must be updated every time you insert a row into the table. The exception to this rule is that you must create an index for a cluster before you insert any data into the cluster.

When you create an index on a table that already has data, Oracle must use sort space to create the index. Oracle uses the sort space in memory allocated for the creator of the index (the amount per user is determined by the initialization parameter `SORT_AREA_SIZE`), but must also swap sort information to and from temporary segments allocated on behalf of the index creation. If the index is extremely large, it might be beneficial to complete the following steps:

1. Create a new temporary tablespace using the `CREATE TABLESPACE` command.
2. Use the `TEMPORARY TABLESPACE` option of the `ALTER USER` command to make this your new temporary tablespace.
3. Create the index using the `CREATE INDEX` command.
4. Drop this tablespace using the `DROP TABLESPACE` command. Then use the `ALTER USER` command to reset your temporary tablespace to your original temporary tablespace.

Under certain conditions, you can load data into a table with the SQL*Loader "direct path load", and an index can be created as data is loaded; refer to Oracle8 Utilities for more information.

##### Index the Correct Tables and Columns

Use the following guidelines for determining when to create an index:

- Create an index if you frequently want to retrieve less than 15% of the rows in a large table. The percentage varies greatly according to the relative speed of a table scan and how clustered the row data is about the index key. The faster the table scan, the lower the percentage; the more clustered the row data, the higher the percentage.
- Index columns used for joins to improve performance on joins of multiple tables.

  Note: Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key; see "Concurrency Control, Indexes, and Foreign Keys" on page 9-10 for more information.

- Small tables do not require indexes; if a query is taking too long, the table might have grown from small to large.

Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are candidates for indexing:

- Values are relatively unique in the column.
- There is a wide range of values.
- The column contains many nulls, but queries often select all rows having a value. In this case, the phrase

      WHERE COL_X > -9.99 x 10^125

  is preferable to

      WHERE COL_X IS NOT NULL

  because the first uses an index on `COL_X` (assuming that `COL_X` is a numeric column).

Columns with the following characteristics are less suitable for indexing:

- The column has few distinct values (for example, a column for the sex of employees).
- There are many nulls in the column and you do not search on the non-null values.

`LONG` and `LONG RAW` columns cannot be indexed.

The size of a single index entry cannot exceed roughly one-half (minus some overhead) of the available space in the data block. Consult with the database administrator for assistance in determining the space required by an index.

##### Limit the Number of Indexes per Table

A table can have any number of indexes. However, the more indexes, the more overhead is incurred as the table is altered. When rows are inserted or deleted, all indexes on the table must be updated. When a column is updated, all indexes on the column must be updated.

Thus, there is a trade-off between speed of retrieval for queries on a table and speed of accomplishing updates on the table. For example, if a table is primarily read-only, more indexes might be useful, but if a table is heavily updated, fewer indexes might be preferable.

##### Order Index Columns for Performance

The order in which columns are named in the CREATE INDEX command need not correspond to the order in which they appear in the table. However, the order of columns in the `CREATE INDEX` statement is significant because query performance can be affected by the order chosen. In general, you should put the column expected to be used most often first in the index.

For example, assume the columns of the `VENDOR_PARTS` table are as shown in Figure 4-1.

#### Figure 4-1 The VENDOR_PARTS Table

Assume that there are five vendors, and each vendor has about 1000 parts.

Suppose that the `VENDOR_PARTS` table is commonly queried by SQL statements such as the following:

    SELECT * FROM vendor_parts

    WHERE part_no = 457 AND vendor_id = 1012;

To increase the performance of such queries, you might create a composite index putting the most selective column first; that is, the column with the most values:

    CREATE INDEX ind_vendor_id

    ON vendor_parts (part_no, vendor_id);

Indexes speed retrieval on any query using the leading portion of the index. So in the above example, queries with `WHERE` clauses using only the `PART_NO` column also note a performance gain. Because there are only five distinct values, placing a separate index on `VENDOR_ID` would serve no purpose.

### Creating Indexes

You can create an index for a table to improve the performance of queries issued against the corresponding table. You can also create an index for a cluster. You can create a composite index on multiple columns up to a maximum of 16 columns. A composite index key cannot exceed roughly one-half (minus some overhead) of the available space in the data block.

Oracle automatically creates an index to enforce a `UNIQUE` or `PRIMARY` `KEY` integrity constraint. In general, it is better to create such constraints to enforce uniqueness and not explicitly use the obsolete `CREATE UNIQUE INDEX` syntax.

Use the SQL command `CREATE INDEX` to create an index. The following statement creates an index named `EMP_ENAME` for the `ENAME` column of the `EMP` table:

    CREATE INDEX emp_ename ON emp(ename)

    TABLESPACE users
    STORAGE (INITIAL 20K

    NEXT 20k
    PCTINCREASE 75)

    PCTFREE 0;

Notice that several storage settings are explicitly specified for the index.

#### Privileges Required to Create an Index

To create a new index, you must own, or have the `INDEX` object privilege for, the corresponding table. The schema that contains the index must also have a quota for the tablespace intended to contain the index, or the `UNLIMITED` `TABLESPACE` system privilege. To create an index in another user's schema, you must have the `CREATE` `ANY INDEX` system privilege.

### Dropping Indexes

You might drop an index for the following reasons:

- The index is not providing anticipated performance improvements for queries issued against the associated table (the table is very small, or there are many rows in the table but very few index entries, etc.).
- Applications do not contain queries that use the index.
- The index is no longer needed and must be dropped before being rebuilt.

When you drop an index, all extents of the index's segment are returned to the containing tablespace and become available for other objects in the tablespace.

Use the SQL command `DROP INDEX` to drop an index. For example, to drop the `EMP_ENAME` index, enter the following statement:

    DROP INDEX emp_ename;

If you drop a table, all associated indexes are dropped.

##### Privileges Required to Drop an Index

To drop an index, the index must be contained in your schema or you must have the`DROP ANY INDEX` system privilege.

## Managing Clusters, Clustered Tables, and Cluster Indexes

Because clusters store related rows of different tables together in the same data blocks, two primary benefits are achieved when clusters are properly used:

- Disk I/O is reduced and access time improves for joins of clustered tables.
- In a cluster, a cluster key value (that is, the related value) is only stored once, no matter how many rows of different tables contain the value. Therefore, less storage may be required to store related table data in a cluster than is necessary in non-clustered table format.

### Guidelines for Creating Clusters

Some guidelines for creating clusters are outlined below. For performance characteristics, see Oracle8 Tuning.

##### Choose Appropriate Tables to Cluster

Use clusters to store one or more tables that are primarily queried (not predominantly inserted into or updated), and for which queries often join data of multiple tables in the cluster or retrieve related data from a single table.

##### Choose Appropriate Columns for the Cluster Key

Choose cluster key columns carefully. If multiple columns are used in queries that join the tables, make the cluster key a composite key. In general, the same column characteristics that make a good index apply for cluster indexes; see "Index the Correct Tables and Columns" on page 4-33 for more information about these guidelines.

A good cluster key has enough unique values so that the group of rows corresponding to each key value fills approximately one data block. Too few rows per cluster key value can waste space and result in negligible performance gains. Cluster keys that are so specific that only a few rows share a common value can cause wasted space in blocks, unless a small `SIZE` was specified at cluster creation time.

Too many rows per cluster key value can cause extra searching to find rows for that key. Cluster keys on values that are too general (for example, `MALE` and `FEMALE`) result in excessive searching and can result in worse performance than with no clustering.

A cluster index cannot be unique or include a column defined as `LONG`.

### Performance Considerations

Also note that clusters can reduce the performance of DML statements (`INSERT`s, `UPDATE`s, and `DELETE`s) as compared to storing a table separately with its own index. These disadvantages relate to the use of space and the number of blocks that must be visited to scan a table. Because multiple tables share each block, more blocks must be used to store a clustered table than if that same table were stored non-clustered. You should decide about using clusters with these trade-offs in mind.

To identify data that would be better stored in clustered form than non-clustered, look for tables that are related via referential integrity constraints and tables that are frequently accessed together using `SELECT` statements that join data from two or more tables. If you cluster tables on the columns used to join table data, you reduce the number of data blocks that must be accessed to process the query; all the rows needed for a join on a cluster key are in the same block. Therefore, query performance for joins is improved. Similarly, it may be useful to cluster an individual table. For example, the `EMP` table could be clustered on the `DEPTNO` column to cluster the rows for employees in the same department. This would be advantageous if applications commonly process rows, department by department.

Like indexes, clusters do not affect application design. The existence of a cluster is transparent to users and to applications. Data stored in a clustered table is accessed via SQL just like data stored in a non-clustered table.

### Creating Clusters, Clustered Tables, and Cluster Indexes

Use a cluster to store one or more tables that are frequently joined in queries. Do not use a cluster to cluster tables that are frequently accessed individually.

Once you create a cluster, tables can be created in the cluster. However, before you can insert any rows into the clustered tables, you must create a cluster index. The use of clusters does not affect the creation of additional indexes on the clustered tables; you can create and drop them as usual.

Use the SQL command `CREATE CLUSTER` to create a cluster. The following statement creates a cluster named EMP_DEPT, which stores the `EMP` and `DEPT` tables, clustered by the `DEPTNO` column:

    CREATE CLUSTER emp_dept (deptno NUMBER(3))

    PCTUSED 80
    PCTFREE 5;

Create a table in a cluster using the SQL command `CREATE TABLE` with the `CLUSTER` option. For example, the `EMP` and `DEPT` tables can be created in the `EMP_DEPT` cluster using the following statements:

    CREATE TABLE dept (

    deptno NUMBER(3) PRIMARY KEY,
    . . . )
    CLUSTER emp_dept (deptno);

    CREATE TABLE emp (

    empno NUMBER(5) PRIMARY KEY,
    ename VARCHAR2(15) NOT NULL,
    . . .
    deptno NUMBER(3) REFERENCES dept)
    CLUSTER emp_dept (deptno);

A table created in a cluster is contained in the schema specified in the `CREATE` `TABLE` statement; a clustered table might not be in the same schema that contains the cluster.

You must create a cluster index before any rows can be inserted into any clustered table. For example, the following statement creates a cluster index for the `EMP_DEPT` cluster:

    CREATE INDEX emp_dept_index

    ON CLUSTER emp_dept
    INITRANS 2
    MAXTRANS 5
    PCTFREE 5;

Note: A cluster index cannot be unique. Furthermore, Oracle is not guaranteed to enforce uniqueness of columns in the cluster key if they have UNIQUE or PRIMARY KEY constraints.

The cluster key establishes the relationship of the tables in the cluster.

#### Privileges Required to Create a Cluster, Clustered Table, and Cluster Index

To create a cluster in your schema, you must have the `CREATE` `CLUSTER` system privilege and a quota for the tablespace intended to contain the cluster or the `UNLIMITED` `TABLESPACE` system privilege. To create a cluster in another user's schema, you must have the `CREATE` `ANY` `CLUSTER` system privilege, and the owner must have a quota for the tablespace intended to contain the cluster or the `UNLIMITED` `TABLESPACE` system privilege.

To create a table in a cluster, you must have either the `CREATE` `TABLE` or `CREATE` `ANY` `TABLE` system privilege. You do not need a tablespace quota or the `UNLIMITED` `TABLESPACE` system privilege to create a table in a cluster.

To create a cluster index, your schema must contain the cluster, and you must have the following privileges:

- the `CREATE ANY INDEX` system privilege or, if you own the cluster, the `CREATE INDEX` privilege
- a quota for the tablespace intended to contain the cluster index, or the `UNLIMITED` `TABLESPACE` system privilege

### Manually Allocating Storage for a Cluster

Oracle dynamically allocates additional extents for the data segment of a cluster, as required. In some circumstances, you might want to explicitly allocate an additional extent for a cluster. For example, when using the Oracle Parallel Server, an extent of a cluster can be allocated explicitly for a specific instance.

You can allocate a new extent for a cluster using the SQL command `ALTER` `CLUSTER` with the `ALLOCATE` `EXTENT` option; see the Oracle8 Parallel Server Concepts and Administration manual for more information.

### Dropping Clusters, Clustered Tables, and Cluster Indexes

Drop a cluster if the tables currently within the cluster are no longer necessary. When you drop a cluster, the tables within the cluster and the corresponding cluster index are dropped; all extents belonging to both the cluster's data segment and the index segment of the cluster index are returned to the containing tablespace and become available for other segments within the tablespace.

You can individually drop clustered tables without affecting the table's cluster, other clustered tables, or the cluster index. Drop a clustered table in the same manner as a non-clustered table-use the SQL command `DROP` `TABLE`. See "Dropping Tables" on page 4-8 for more information about individually dropping tables.

Note: When you drop a single clustered table from a cluster, each row of the table must be deleted from the cluster. To maximize efficiency, if you intend to drop the entire cluster including all tables, use the `DROP` `CLUSTER` command with the `INCLUDING` `TABLES` option. You should only use the `DROP` `TABLE` command to drop an individual table from a cluster when the rest of the cluster is going to remain.

You can drop a cluster index without affecting the cluster or its clustered tables. However, you cannot use a clustered table if it does not have a cluster index. Cluster indexes are sometimes dropped as part of the procedure to rebuild a fragmented cluster index. See "Dropping Indexes" on page 4-36 for more information.

To drop a cluster that contains no tables, as well as its cluster index, if present, use the SQL command `DROP` `CLUSTER`. For example, the following statement drops the empty cluster named `EMP_DEPT`:

    DROP CLUSTER emp_dept;

If the cluster contains one or more clustered tables and you intend to drop the tables as well, add the `INCLUDING` `TABLES` option of the `DROP` `CLUSTER` command, as in

    DROP CLUSTER emp_dept INCLUDING TABLES;

If you do not include the `INCLUDING` `TABLES` option, and the cluster contains tables, an error is returned.

If one or more tables in a cluster contain primary or unique keys that are referenced by `FOREIGN` `KEY` constraints of tables outside the cluster, you cannot drop the cluster unless you also drop the dependent `FOREIGN KEY` constraints. Use the `CASCADE` `CONSTRAINTS` option of the `DROP` `CLUSTER` command, as in

    DROP CLUSTER emp_dept INCLUDING TABLES CASCADE CONSTRAINTS;

An error is returned if the above option is not used in the appropriate situation.

#### Privileges Required to Drop a Cluster

To drop a cluster, your schema must contain the cluster, or you must have the `DROP` `ANY` `CLUSTER` system privilege. You do not have to have any special privileges to drop a cluster that contains tables, even if the clustered tables are not owned by the owner of the cluster.

## Managing Hash Clusters and Clustered Tables

The following sections explain how to create, alter, and drop hash clusters and clustered tables using SQL commands.

### Creating Hash Clusters and Clustered Tables

A hash cluster is used to store individual tables or a group of clustered tables that are static and often queried by equality queries. Once you create a hash cluster, you can create tables. To create a hash cluster, use the SQL command `CREATE` `CLUSTER`. The following statement creates a cluster named `TRIAL_CLUSTER` that is used to store the `TRIAL` table, clustered by the `TRIALNO` column:

    CREATE CLUSTER trial_cluster (trialno NUMBER(5,0))

    PCTUSED 80     PCTFREE 5
    SIZE 2K
    HASH IS trialno HASHKEYS 100000;

    CREATE TABLE trial (

    trialno NUMBER(5) PRIMARY KEY,
    ...)
    CLUSTER trial_cluster (trialno);

### Controlling Space Usage Within a Hash Cluster

When you create a hash cluster, it is important that you correctly choose the cluster key and set the `HASH` `IS, SIZE`, and `HASHKEYS` parameters to achieve the desired performance and space usage for the cluster. The following sections provide guidance, as well as examples of setting these parameters.

#### Choosing the Key

Choosing the correct cluster key is dependent on the most common types of queries issued against the clustered tables. For example, consider the `EMP` table in a hash cluster. If queries often select rows by employee number, the `EMPNO` column should be the cluster key; if queries often select rows by department number, the `DEPTNO` column should be the cluster key. For hash clusters that contain a single table, the cluster key is typically the entire primary key of the contained table. A hash cluster with a composite key must use Oracle's internal hash function.

#### Setting HASH IS

Only specify the `HASH` `IS` parameter if the cluster key is a single column of the `NUMBER` datatype, and contains uniformly distributed integers. If the above conditions apply, you can distribute rows in the cluster such that each unique cluster key value hashes to a unique hash value (with no collisions). If the above conditions do not apply, you should use the internal hash function.

### Dropping Hash Clusters

Drop a hash cluster using the SQL command `DROP` `CLUSTER`:

    DROP CLUSTER emp_dept;

Drop a table in a hash cluster using the SQL command `DROP` `TABLE`. The implications of dropping hash clusters and tables in hash clusters are the same as for index clusters. See page 4-40 for more information about dropping clusters and the required privileges.

### When to Use Hashing

Storing a table in a hash cluster is an alternative to storing the same table with an index. Hashing is useful in the following situations:

- Most queries are equality queries on the cluster key. For example:

      SELECT . . . WHERE cluster_key = . . . ;

  In such cases, the cluster key in the equality condition is hashed, and the corresponding hash key is usually found with a single read. With an indexed table, the key value must first be found in the index (usually several reads), and then the row is read from the table (another read).

- The table or tables in the hash cluster are primarily static in size such that you can determine the number of rows and amount of space required for the tables in the cluster. If tables in a hash cluster require more space than the initial allocation for the cluster, performance degradation can be substantial because overflow blocks are required.
- A hash cluster with the `HASH` `IS` col, `HASHKEYS` n, and `SIZE` m clauses is an ideal representation for an array (table) of n items (rows) where each item consists of m bytes of data. For example:

      ARRAY X[100] OF NUMBER(8)

could be represented as

    CREATE CLUSTER c(subscript INTEGER)
            HASH IS subscript HASHKEYS 100 SIZE 10;
    CREATE TABLE x(subscript NUMBER(2)), value NUMBER(8))
            CLUSTER c(subscript);

Alternatively, hashing is not advantageous in the following situations:

- Most queries on the table retrieve rows over a range of cluster key values. For example, in full table scans, or queries such as

      SELECT . . . WHERE cluster_key < . . . ;

  A hash function cannot be used to determine the location of specific hash keys; instead, the equivalent of a full table scan must be done to fetch the rows for the query. With an index, key values are ordered in the index, so cluster key values that satisfy the `WHERE` clause of a query can be found with relatively few I/Os.

- A table is not static, but is continually growing. If a table grows without limit, the space required over the life of the table (thus, of its cluster) cannot be predetermined.
- Applications frequently perform full table scans on the table and the table is sparsely populated. A full table scan in this situation takes longer under hashing.
- You cannot afford to preallocate the space the hash cluster will eventually need.

In most cases, you should decide (based on the above information) whether to use hashing or indexing. If you use indexing, consider whether it is best to store a table individually or as part of a cluster; see page 4-37 for guidance.

If you decide to use hashing, a table can still have separate indexes on any columns, including the cluster key. For additional guidelines on the performance characteristics of hash clusters, see Oracle8 Tuning.

## Miscellaneous Management Topics for Schema Objects

The following sections explain miscellaneous topics regarding the management of the various schema objects discussed in this chapter.

- Creating Multiple Tables and Views in One Operation
- Naming Schema Objects
- Name Resolution in SQL Statements
- Renaming Schema Objects
- Listing Information about Schema Objects

### Creating Multiple Tables and Views in One Operation

You can create several tables and views and grant privileges in one operation using the SQL command `CREATE` `SCHEMA`. The `CREATE` `SCHEMA` command is useful if you want to guarantee the creation of several tables and views and grants in one operation; if an individual table or view creation fails or a grant fails, the entire statement is rolled back and none of the objects are created or the privileges granted.

For example, the following statement creates two tables and a view that joins data from the two tables:

    CREATE SCHEMA AUTHORIZATION scott

    CREATE VIEW sales_staff AS

    SELECT empno, ename, sal, comm
    FROM emp
    WHERE deptno = 30  WITH CHECK OPTION CONSTRAINT

                   sales_staff_cnst

    CREATE TABLE emp (

    empno       NUMBER(5) PRIMARY KEY,
    ename       VARCHAR2(15) NOT NULL,
    job         VARCHAR2(10),
    mgr         NUMBER(5),
    hiredate    DATE DEFAULT (sysdate),
    sal         NUMBER(7,2),
    comm        NUMBER(7,2),
    deptno      NUMBER(3) NOT NULL

        CONSTRAINT dept_fkey REFERENCES dept)

    CREATE TABLE dept (

    deptno      NUMBER(3) PRIMARY KEY,
    dname       VARCHAR2(15),
    loc         VARCHAR2(25))

    GRANT SELECT ON sales_staff TO human_resources;

The `CREATE` `SCHEMA` command does not support Oracle extensions to the ANSI `CREATE` `TABLE` and `CREATE` `VIEW` commands (for example, the `STORAGE` clause).

#### Privileges Required to Create Multiple Schema Objects

To create schema objects, such as multiple tables, using the `CREATE` `SCHEMA` command, you must have the required privileges for any included operation.

### Naming Schema Objects

You should decide when you want to use partial and complete global object names in the definition of views, synonyms, and procedures. Keep in mind that database names should be stable and databases should not be unnecessarily moved within a network.

In a distributed database system, each database should have a unique global name. The global name is composed of the database name and the network domain that contains the database. Each schema object in the database then has a global object name consisting of the schema object name and the global database name.

Because Oracle ensures that the schema object name is unique within a database, you can ensure that it is unique across all databases by assigning unique global database names. You should coordinate with your database administrator on this task, as it is usually the DBA who is responsible for assigning database names.

### Name Resolution in SQL Statements

An object name takes the form

    [schema.]name[@database]

Some examples include the following:

    emp
    scott.emp
    scott.emp@personnel

A session is established when a user logs onto a database. Object names are resolved relative to the current user session. The username of the current user is the default schema. The database to which the user has directly logged-on is the default database.

Oracle has separate namespaces for different classes of objects. All objects in the same namespace must have distinct names, but two objects in different namespaces can have the same name. Tables, views, snapshots, sequences, synonyms, procedures, functions, and packages are in a single namespace. Triggers, indexes, and clusters each have their own individual namespace. For example, there can be a table, trigger, and index all named `SCOTT.EMP`.

Based on the context of an object name, Oracle searches the appropriate namespace when resolving the name to an object. For example, in the statement

    DROP CLUSTER test

Oracle looks up `TEST` in the cluster namespace.

Rather than supplying an object name directly, you can also refer to an object using a synonym. A private synonym name has the same syntax as an ordinary object name. A public synonym is implicitly in the `PUBLIC` schema, but users cannot explicitly qualify a synonym with the schema `PUBLIC`.

Synonyms can only be used to reference objects in the same namespace as tables. Due to the possibility of synonyms, the following rules are used to resolve a name in a context that requires an object in the table namespace:

1. Look up the name in the table namespace.
2. If the name resolves to an object that is not a synonym, no further work is needed.
3. If the name resolves to a private synonym, replace the name with the definition of the synonym and return to step 1.
4. If the name was originally qualified with a schema, return an error; otherwise, check if the name is a public synonym.
5. If the name is not a public synonym, return an error; otherwise, replace the name with the definition of the public synonym and return to step 1.

When global object names are used in a distributed database (either explicitly or indirectly within a synonym), the local Oracle session resolves the reference as is locally required (for example, resolving a synonym to a remote table's global object name). After the partially resolved statement is shipped to the remote database, the remote Oracle session completes the resolution of the object as above.

See Oracle8 Concepts for more information about name resolution in a distributed database.

### Renaming Schema Objects

If necessary, you can rename some schema objects using two different methods: drop and re-create the object, or rename the object using the SQL command `RENAME`

.

Note: If you drop an object and re-create it, all privilege grants for the object are lost when the object is dropped. Privileges must be granted again when the object is re-created.

If you use the `RENAME` command to rename a table, view, sequence, or a private synonym of a table, view, or sequence, grants made for the object are carried forward for the new name, ad the next statement renames the `SALES_STAFF` view:

    RENAME sales_staff TO dept_30;

You cannot rename a stored PL/SQL program unit, public synonym, index, or cluster. To rename such an object, you must drop and re-create it.

Renaming a schema object has the following effects:

- All views and PL/SQL program units dependent on a renamed object become invalid (must be recompiled before next use).
- All synonyms for a renamed object return an error when used.

#### Privileges Required to Rename an Object

To rename an object, you must be the owner of the object.

### Listing Information about Schema Objects

The data dictionary provides many views that provide information about schema objects . The following is a summary of the views associated with schema objects:

- `ALL_OBJECTS, USER_OBJECTS`
- `ALL_CATALOG, USER_CATALOG`
- `ALL_TABLES, USER_TABLES`
- `ALL_TAB_COLUMNS, USER_TAB_COLUMNS`
- `ALL_TAB_COMMENTS, USER_TAB_COMMENTS`
- `ALL_COL_COMMENTS, USER_COL_COMMENTS`
- `ALL VIEWS, USER_VIEWS`
- `ALL_INDEXES, USER_INDEXES`
- `ALL_IND_COLUMNS, USER_IND_COLUMNS`
- `USER_CLUSTERS`
- `USER_CLU_COLUMNS`
- `ALL_SEQUENCES, USER_SEQUENCES`
- `ALL_SYNONYMS, USER_SYNONYMS`
- `ALL_DEPENDENCIES, USER_DEPENDENCIES`

##### Example 1: Listing Different Schema Objects by Type

The following query lists all of the objects owned by the user issuing the query:

    SELECT object_name, object_type FROM user_objects;

The query above might return results similar to the following:

    OBJECT_NAME               OBJECT_TYPE
    ------------------------- -------------------
    EMP_DEPT                  CLUSTER
    EMP                       TABLE
    DEPT                      TABLE
    EMP_DEPT_INDEX            INDEX
    PUBLIC_EMP                SYNONYM
    EMP_MGR                   VIEW

##### Example 2: Listing Column Information

Column information, such as name, datatype, length, precision, scale, and default data values, can be listed using one of the views ending with the `_COLUMNS` suffix. For example, the following query lists all of the default column values for the `EMP` and `DEPT` tables:

    SELECT table_name, column_name, data_default

    FROM user_tab_columns
    WHERE table_name = 'DEPT' OR table_name = 'EMP';

Considering the example statements at the beginning of this section, a display similar to the one below is displayed:

    TABLE_NAME  COLUMN_NAME     DATA_DEFAULT
    ----------  --------------- --------------------
    DEPT        DEPTNO
    DEPT        DNAME
    DEPT        LOC             ('NEW YORK')
    EMP         EMPNO
    EMP         ENAME
    EMP         JOB
    EMP         MGR
    EMP         HIREDATE        (sysdate)
    EMP         SAL
    EMP         COMM
    EMP         DEPTNO

Note: Not all columns have a user-specified default. These columns assume `NULL` when rows that do not specify values for these columns are inserted.

##### Example 3: Listing Dependencies of Views and Synonyms

When you create a view or a synonym, the view or synonym is based on its underlying base object. The `_DEPENDENCIES` data dictionary views can be used to reveal the dependencies for a view and the `_SYNONYMS` data dictionary views can be used to list the base object of a synonym. For example, the following query lists the base objects for the synonyms created by the user `JWARD`:

    SELECT table_owner, table_name

    FROM all_synonyms
    WHERE owner = 'JWARD';

This query might return information similar to the following:

    TABLE_OWNER                    TABLE_NAME
    ------------------------------ ------------
    SCOTT                          DEPT
    SCOTT                          EMP

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index