Oracle8 Administrator's Guide
Release 8.0






Prev Next

Managing Tables

This chapter describes the various aspects of managing tables, and includes the following topics:

Before attempting tasks described in this chapter, familiarize yourself with the concepts in Chapter 10, Guidelines for Managing Schema Objects.

Guidelines for Managing Tables

This section describes guidelines to follow when managing tables, and includes the following topics:

Use these guidelines to make managing tables as easy as possible.

Design Tables Before Creating Them

Usually, the application developer is responsible for designing the elements of an application, including the tables. Database administrators are responsible for setting storage parameters and defining clusters for tables, based on information from the application developer about how the application works and the types of data expected.

Working with your application developer, carefully plan each table so that the following occurs:

Specify How Data Block Space Is to Be Used

By specifying the PCTFREE and PCTUSED parameters during the creation of each table, you can affect the efficiency of space utilization and amount of space reserved for updates to the current data in the data blocks of a table's data segment.

See Also: For information about specifying PCTFREE and PCTUSED, see "Managing Space in Data Blocks".

Specify Transaction Entry Parameters

By specifying the INITRANS and MAXTRANS parameters during the creation of each table, you can affect how much space is initially and can ever be allocated for transaction entries in the data blocks of a table's data segment.

See Also: For information about specifying INITRANS and MAXTRANS, see "Setting Storage Parameters".

Specify the Location of Each Table

If you have the proper privileges and tablespace quota, you can create a new table in any tablespace that is currently online. Therefore, you should specify the TABLESPACE option in a CREATE TABLE statement to identify the tablespace that will store the new table.

If you do not specify a tablespace in a CREATE TABLE statement, the table is created in your default tablespace.

When specifying the tablespace to contain a new table, make sure that you understand implications of your selection. By properly specifying a tablespace during the creation of each table, you can:

The following examples show how incorrect storage locations of schema objects can affect a database:

See Also: For information about specifying tablespaces, see "Assigning Tablespace Quotas to Users".

Parallelize Table Creation

If you have the parallel query option installed, you can parallelize the creation of tables created with a subquery in the CREATE TABLE command. Because multiple processes work together to create the table, performance of the table creation can improve.

See Also: For more information about the parallel query option and parallel table creation, see the Oracle8 Tuning guide.

For information about the CREATE TABLE command, see the Oracle8 SQL Reference.

Consider Creating UNRECOVERABLE Tables

When you create an unrecoverable table, the table cannot be recovered from archived logs (because the needed redo log records are not generated for the unrecoverable table creation). Thus, if you cannot afford to lose the table, you should take a backup after the table is created. In some situations, such as for tables that are created for temporary use, this precaution may not be necessary.

You can create an unrecoverable table by specifying UNRECOVERABLE when you create a table with a subquery in the CREATE TABLE AS SELECT statement. However, rows inserted afterwards are recoverable. In fact, after the statement is completed, all future statements are fully recoverable.

Creating an unrecoverable table has the following benefits:

In general, the relative performance improvement is greater for larger unrecoverable tables than for smaller tables. Creating small unrecoverable tables has little affect on the time it takes to create a table. However, for larger tables the performance improvement can be significant, especially when you are also parallelizing the table creation.

Estimate Table Size and Set Storage Parameters

Estimating the sizes of tables before creating them is useful for the following reasons:

Appendix A contains equations that help estimate the size of tables. Whether or not you estimate table size before creation, you can explicitly set storage parameters when creating each non-clustered table. (Clustered tables automatically use the storage parameters of the cluster.) Any storage parameter that you do not explicitly set when creating or subsequently altering a table automatically uses the corresponding default storage parameter set for the tablespace in which the table resides.

If you explicitly set the storage parameters for the extents of a table's data segment, try to store the table's data in a small number of large extents rather than a large number of small extents.

Plan for Large Tables

There are no limits on the physical size of tables and extents. You can specify the keyword UNLIMITED for MAXEXTENTS, thereby simplifying your planning for large objects, reducing wasted space and fragmentation, and improving space reuse. However, keep in mind that while Oracle allows an unlimited number of extents, when the number of extents in a table grows very large, you may see an impact on performance when performing any operation requiring that table.


You cannot alter data dictionary tables to have MAXEXTENTS greater than the allowed block maximum.


If you have such tables in your database, consider the following recommendations:

Separate the Table from Its Indexes Place indexes in separate tablespaces from other objects, and on separate disks if possible. If you ever need to drop and re-create an index on a very large table (such as when disabling and enabling a constraint, or re-creating the table), indexes isolated into separate tablespaces can often find contiguous space more easily than those in tablespaces that contain other objects.

Allocate Sufficient Temporary Space If applications that access the data in a very large table perform large sorts, ensure that enough space is available for large temporary segments and that users have access to this space (temporary segments always use the default STORAGE settings for their tablespaces).

Table Restrictions

Before creating tables, make sure you are aware of the following restrictions:

Creating Tables

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.

Create tables using the SQL command CREATE TABLE. When user SCOTT issues the following statement, he creates a non-clustered table named EMP in his schema and stores it in the USERS tablespace:

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)
                    NEXT 50K
                    MAXEXTENTS 10
                    PCTINCREASE 25 );

Notice that integrity constraints are defined on several columns of the table and that several storage settings are explicitly specified for the table.

See Also: For more information about system privileges, see Chapter 21, Managing User Privileges and Roles. For more information about tablespace quotas, see Chapter 20, Managing Users and Resources.

Altering Tables

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.

A table in an Oracle database can be altered for the following reasons:

You can increase the length of an existing column. However, you cannot decrease it unless there are no rows in the table. Furthermore, if you are modifying a table to increase the length of a column of datatype CHAR, realize that this may be a time consuming operation and may require 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.

When altering the data block space usage parameters (PCTFREE and PCTUSED) of a table, note that new settings apply to all data blocks used by the table, including blocks already allocated and subsequently allocated for the table. However, the blocks already allocated for the table are not immediately reorganized when space usage parameters are altered, but as necessary after the change.

When altering the transaction entry settings (INITRANS, MAXTRANS) of a table, note that a new setting for INITRANS only applies to data blocks subsequently allocated for the table, while a new setting for MAXTRANS applies to all blocks (already and subsequently allocated blocks) of a table.

The storage parameters INITIAL and MINEXTENTS cannot be altered. All new settings for the other storage parameters (for example, NEXT, PCTINCREASE) affect only extents subsequently allocated for the table. The size of the next extent allocated is determined by the current values of NEXT and PCTINCREASE, and is not based on previous values of these parameters.

You can alter a table using the SQL command ALTER TABLE. The following statement alters the EMP table:

      PCTFREE 30
      PCTUSED 60;


Before altering a table, familiarize yourself with the consequences of doing so.

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 may affect the dependent object.


See Also: See "Managing Object Dependencies" for information about how Oracle manages dependencies.

Manually Allocating Storage for a Table

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

A new extent can be allocated for a table using the SQL command ALTER TABLE with the ALLOCATE EXTENT option.

See Also: For information about the ALLOCATE EXTENT option, see Oracle8 Parallel Server Concepts and Administration.

Dropping Tables

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

To drop a table that is no longer needed, use the SQL command DROP TABLE. The following statement drops the EMP table:


If the table to be dropped 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 shown below:



Before dropping a table, familiarize yourself with the consequences of doing so:

  • Dropping a table removes the table definition from the data dictionary. All rows of the table are no longer accessible.
  • All indexes and triggers associated with a table are dropped.
  • All views and PL/SQL program units dependent on a dropped table remain, yet become invalid (not usable). See "Managing Object Dependencies" for information about how Oracle manages such dependencies.
  • 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 or new objects. All rows corresponding to a clustered table are deleted from the blocks of the cluster.

Index-Organized Tables

This section describes aspects of managing index-organized tables, and includes the following topics:

What Are Index-Organized Tables?

Index-organized tables are tables with data rows grouped according to the primary key. This clustering is achieved using a B*tree index. B*tree indexes are special types of index trees that differ from regular table B-tree indexes in that they store both the primary key and non-key columns. The attributes of index-organized tables are stored entirely within the physical data structures for the index. Put another way, index-organized tables are logical constructs for defining and accessing index content using SQL statements.

Why Use Index-Organized Tables?

Index-organized tables provide faster key-based access to table data for queries involving exact match and range searches. Changes to the table data (such as adding new rows, updating rows, or deleting rows) result only in updating the index structure (because there is no separate table storage area).

Also, storage requirements are reduced because key columns are not duplicated in the table and index. The remaining non-key columns are stored in the index structure.

Index-organized tables are particularly useful when you are using applications that must retrieve data based on a primary key. Also, index-organized tables are suitable for modeling application-specific index structures. For example, content-based information retrieval applications containing text, image and audio data require inverted indexes that can be effectively modeled using index-organized tables.

See Also: For more details about index-organized tables, see Oracle8 Concepts.

Differences Between Index-Organized and Regular Tables

Index-organized tables are like regular tables with an index on one or more of its columns. However, instead of maintaining two separate storage spaces for the table and B*tree index, an index-organized table only maintains a single B*tree index containing the primary key of the table and other column values.

Figure 12-1 Structure of Regular Table versus Index-Organized Table

Index-organized tables are suitable for accessing data by way of primary key or any key that is a valid prefix of the primary key. Also, there is no duplication of key values because a separate index structure containing the key values and ROWID is not created. Table 12-1 summarizes the difference between an index-organized table and a regular table.

Table 12-1 Comparison of Index-Organized table with a Regular Table
Regular Table   Index-Organized Table  

ROWID uniquely identifies a row; primary key can be optionally specified


Primary key uniquely identifies a row; primary key must be specified


Implicit ROWID Column; allows building physical secondary indexes


No implicit ROWID Column; can not have physical secondary indexes


ROWID-based access, key or scan


Primary key-based access or scan


Sequential scan returns all rows


Full-table scan returns all rows in primary key order


UNIQUE constraints allowed on other columns


UNIQUE constraints on other columns are not allowed


Triggers allowed on other columns


Triggers allowed on other columns


Table can be stored in a cluster containing other tables


An index-organized table can not be stored in a cluster


Distributed SQL, Replication, and Partitioning supported


Distributed SQL and Replication supported. Partitioning not supported


Creating Index-Organized Tables

You can use the CREATE TABLE statement to create index-organized tables; when doing so, you need to provide the following additional information:

The following example shows information to provide when creating index-organized tables:

      ( token char(20), 
      doc_oid NUMBER,
      token_frequency NUMBER,
      token_occurrence_data varchar2(512),
      CONSTRAINT pk_docindex 
      PRIMARY KEY (token, doc_oid))
    OVERFLOW TABLESPACE text_col_overflow;

This example shows that the ORGANIZATION INDEX qualifier specifies an index-organized table, where the key columns and non-key columns reside in an index defined on columns that designate the primary key (TOKEN,DOC_ID) for the table.

Index-organized tables can store object types. For example, you can create an index-organized table containing a column of object type mytype (for the purpose of this example) as follows:

CREATE TABLE iot (c1 NUMBER primary key, c2 mytype)

However, you cannot create an index-organized table of object types. For example, the following statement would not be valid:


See Also: For more details about the CREATE INDEX statement, see the Oracle SQL Reference.

Using the Overflow Clause

The overflow clause specified in the preceding example indicates that any non-key columns of rows exceeding 20% of the block size are placed in a data segment stored in the TEXT_COLLECTION_OVERFLOW tablespace. The key columns should fit the specified threshold.

If an update of a non-key column causes the row to decrease in size, Oracle identifies the row piece (head or tail) to which the update is applicable and rewrites that piece.

If an update of a non-key column causes the row to increase in size, Oracle identifies the piece (head or tail) to which the update is applicable and rewrites that row piece. If the update's target turns out to be the head piece, note that this piece may again be broken into 2 to keep the row size below the specified threshold.

The non-key columns that fit in the index leaf block are stored as a row head-piece that contains a ROWID field linking it to the next row piece stored in the overflow data segment. The only columns that are stored in the overflow area are those that do not fit.

Choosing and Monitoring a Threshold Value

You should choose a threshold value that can accommodate your key columns, as well as the first few non-key columns (if they are frequently accessed).

After choosing a threshold value, you can monitor tables to verify that the value you specified is appropriate. You can use the ANALYZE TABLE LIST CHAINED ROWS statement to determine the number and identity of rows exceeding the threshold value.

To analyze index-organized tables, you must create a separate CHAINED ROWS table for each and every index-organized table (to accommodate the primary key storage of index-organized tables). You can use the SQL scripts DBMSIOTC.SQL and PRVTIOTC.PLB to define the BUILD_CHAIN_ROWS_TABLE package definition, and then execute this procedure to create an IOT_CHAINED_ROWS table for an index-organized table.

You must execute both DBMSIOTC.SQL and PRVTIOTC.PLB in the 'SYS' schema to create the package definition. PUBLIC users have EXECUTE privileges for the procedures defined in the packages, so users in any schema can use them to create the LIST_CHAIN_ROW table.

See Also: For more information about the ANALYZE command and SQL scripts, see the Oracle8 SQL Reference.

Using the INCLUDING clause

In addition to specifying PCTTHRESHOLD, you can use the INCLUDING <COLUMN_NAME> clause to control which non-key columns are stored with the key columns. Oracle accommodates all non-key columns up to the column specified in the INCLUDING clause in the index leaf block, provided it does not exceed the specified threshold. All non-key columns beyond the column specified in the INCLUDING clause are stored in the overflow area.

For example, you can modify the previous example where an index-organized table was created so that it always has the TOKEN_OCCURRENCE_DATA column value stored in the overflow area:

      ( token char(20), 
        doc_oid NUMBER,
        token_frequency NUMBER,
        token_occurrence_data varchar2(512),
        CONSTRAINT pk_docindex 
        PRIMARY KEY (token, doc_oid))
        INCLUDING token_frequency
     OVERFLOW TABLESPACE text_col_overflow;

Here, only non-key columns up to TOKEN_FREQUENCY (in this case a single column only) are stored with the key column values in the index leaf block.

Maintaining Index-Organized Tables

You can use an index-organized table in place of a regular table in INSERT, SELECT, DELETE, and UPDATE statements. Note that the rows for index-organized tables are stored in the B*tree, and do not have a row identity (ROWID). Thus, you cannot perform ROWID based-retrieval on index-organized tables.


You cannot partition index-organized tables.


Altering Index-Organized Tables

Index-organized tables differ only in physical organization; logically, they are manipulated the same as regular tables. Hence, you manipulate index-organized tables as you would regular tables. However, there is one difference when using the ALTER TABLE statement. In addition to other defined clauses, you can use the following:

You can alter the threshold value for index-organized tables only when the table is empty or the specified threshold is larger than the current threshold value.

See Also: For details about the ALTER TABLE statement, see the Oracle8 SQL Reference.

Scenario: Using the ORDER BY Clause with Index-Organized Tables

If an ORDER BY clause only references the primary key column or a prefix of it, then the optimizer avoids the sorting overhead as the rows are returned sorted on the primary key columns.

For example, you create the following table:


The following 2 queries avoid sorting overhead because the data is already sorted on the primary key:


If, however, you have an ORDER BY clause on a suffix of the primary key column, additional sorting is required.


Scenario: Updating the Key Column

A key column update is logically equivalent to deleting the row with the old key value and inserting the row with the new key value at the appropriate place to maintain the primary key order.

In the following example, the employee row for DEPT_ID 20 and E_ID 10 are deleted and the employee row for DEPT_ID 23 and E_ID 10 are inserted:

   WHERE DEPT_ID=20 and E_ID=10;

Converting Index-Organized Tables to Regular Tables

You can convert index-organized tables to regular tables using the Oracle IMPORT/EXPORT utilities, or the CREATE TABLE AS SELECT statement.

To convert an index-organized table to a regular table:

See Also: For more details about using IMPORT/EXPORT, see Oracle8 Utilities.


Copyright © 1997 Oracle Corporation.

All Rights Reserved.