Managing Tables

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 Oracle7 Server Administrator's Guide for more suggestions. You should also refer to a text on relational database or table design.

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

Creating Tables

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

• 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

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)

• 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

Your goal is to find a satisfactory tradeoff 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

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

• usually keeps blocks fuller than a lower PCTUSED

• improves space efficiency

• increases processing cost during INSERTs and UPDATEs

Choosing Associated PCTUSED and PCTFREE Values

• 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

Example 1

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.

Example 2

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.

Example 3

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

Altering Tables

• 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

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

Dropping Tables

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.

Privileges Required to Drop a Table