Guidelines for Managing Tables

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 establishing the attributes of the underlying tablespace that will hold the application tables. Either the DBA or the applications developer, or both working jointly, can be responsible for the actual creation of the tables, depending upon the practices for a site.

Working with the application developer, consider the following guidelines when 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 data type for each column.

• Consider whether your applications would benefit from adding one or more virtual columns to some tables.

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

Specify the Type of Table to Create

Here are the types of tables that you can create:

Specify the Location of Each Table

It is advisable to specify the TABLESPACE clause in a CREATE TABLE statement to identify the tablespace that is to store the new table. For partitioned tables, you can optionally identify the tablespace that is to store each partition. Ensure that you have the appropriate privileges and quota on any tablespaces that you use. 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, ensure that you understand implications of your selection. By properly specifying a tablespace during the creation of each table, you can increase the performance of the database system and decrease the time needed for database administration.

The following situations illustrate how not specifying a tablespace, or specifying an inappropriate one, can affect performance:

• If users' objects are created in the SYSTEM tablespace, the performance of the database can suffer, since both data dictionary objects and user objects must contend for the same datafiles. Users' objects should not be stored in the SYSTEM tablespace. To avoid this, ensure that all users are assigned default tablespaces when they are created in the database.

• If application-associated tables are arbitrarily stored in various tablespaces, the time necessary to complete administrative operations (such as backup and recovery) for the data of that application can be increased.

Consider Parallelizing Table Creation

You can use parallel execution when creating tables using a subquery (AS SELECT) in the CREATE TABLE statement. Because multiple processes work together to create the table, performance of the table creation operation is improved.

Parallelizing table creation is discussed in the section "Parallelizing Table Creation".

Consider Using NOLOGGING When Creating Tables

To create a table most efficiently use the NOLOGGING clause in the CREATE TABLE...AS SELECT statement. The NOLOGGING clause causes minimal redo information to be generated during the table creation. This has the following benefits:

• Space is saved in the redo log files.

• The time it takes to create the table is decreased.

• Performance improves for parallel creation of large tables.

The NOLOGGING clause also specifies that subsequent direct loads using SQL*Loader and direct load INSERT operations are not logged. Subsequent DML statements (UPDATE, DELETE, and conventional path insert) are unaffected by the NOLOGGING attribute of the table and generate redo.

If you cannot afford to lose the table after you have created it (for example, you will no longer have access to the data used to create the table) you should take a backup immediately after the table is created. In some situations, such as for tables that are created for temporary use, this precaution may not be necessary.

In general, the relative performance improvement of specifying NOLOGGING is greater for larger tables than for smaller tables. For small tables, NOLOGGING has little effect on the time it takes to create a table. However, for larger tables the performance improvement can be significant, especially when also parallelizing the table creation.

Consider Using Table Compression

As your database grows in size, consider using table compression. Compression saves disk space, reduces memory use in the database buffer cache, and can significantly speed query execution during reads. Compression has a cost in CPU overhead for data loading and DML. However, this cost may be offset by reduced I/O requirements.

Table compression is completely transparent to applications. It is useful in both decision support systems (DSS) and online transaction processing (OLTP) systems.

You can specify compression for a tablespace, a table, or a partition. If specified at the tablespace level, then all tables created in that tablespace are compressed by default.

Compression can occur while data is being inserted, updated, or bulk loaded into a table. Operations that permit compression include:

• Single-row or array inserts and updates

• The following direct-path INSERT methods:

  • Direct path SQL*Loader

  • CREATE TABLE AS SELECT statements

  • Parallel INSERT statements

  • INSERT statements with an APPEND or APPEND_VALUES hint

Oracle Database support two methods of table compression. They are summarized in Table 20-1.

^Footnote 1 COMPRESS and COMPRESS BASIC are equivalent

^Footnote 2 Inserted and updated rows are uncompressed

You specify table compression with the COMPRESS clause of the CREATE TABLE statement. You can enable compression for an existing table by using these clauses in an ALTER TABLE statement. In this case, only data that is inserted or updated after compression is enabled is compressed. Similarly, you can disable table compression for an existing compressed table with the ALTER TABLE...NOCOMPRESS statement. In this case, all data that was already compressed remains compressed, and new data is inserted uncompressed.

To enable OLTP table compression, you must set the COMPATIBLE initialization parameter to 11.1.0 or higher.

Examples

The following example enables OLTP table compression on the table orders:

CREATE TABLE orders  ...  COMPRESS FOR OLTP;

Data for the orders table is compressed during both direct-path INSERT and conventional DML.

The next two examples, which are equivalent, enable basic table compression on the sales_history table, which is a fact table in a data warehouse. Frequent queries are run against this table, but no DML is expected.

CREATE TABLE sales_history  ...  COMPRESS BASIC;

CREATE TABLE sales_history  ...  COMPRESS;

The next example demonstrates using the APPEND hint to insert rows into the sales_history table using direct-path INSERT.

INSERT /*+ APPEND */ INTO sales_history SELECT * FROM sales WHERE cust_id=8890;
COMMIT;

Compression and Partitioned Tables

A table can have both compressed and uncompressed partitions, and different partitions can use different compression methods. If the compression settings for a table and one of its partitions disagree, the partition setting has precedence for the partition.

To change the compression method for a partition, do one of the following:

• To change the compression method for new data only, use ALTER TABLE ... MODIFY PARTITION ... COMPRESS ...

• To change the compression method for both new and existing data, use either ALTER TABLE ... MOVE PARTITION ... COMPRESS ... or online table redefinition.

Determining If a Table Is Compressed

In the *_TABLES data dictionary views, compressed tables have ENABLED in the COMPRESSION column. For partitioned tables, this column is null, and the COMPRESSION column of the *_TAB_PARTITIONS views indicates the partitions that are compressed. In addition, the COMPRESS_FOR column indicates the compression method in use for the table or partition.

SELECT table_name, compression, compress_for FROM user_tables;
 
TABLE_NAME       COMPRESSION COMPRESS_FOR
---------------- ----------- ------------------
T1               DISABLED
T2               ENABLED     BASIC
T3               ENABLED     OLTP

SELECT table_name, partition_name, compression, compress_for
  FROM user_tab_partitions;

TABLE_NAME  PARTITION_NAME   COMPRESSION COMPRESS_FOR
----------- ---------------- ----------- ------------------------------
SALES       Q4_2008          ENABLED     OLTP
SALES       Q1_2009          ENABLED     OLTP
SALES       Q2_2009          ENABLED     OLTP

Adding and Dropping Columns in Compressed Tables

The following restrictions apply when adding columns to compressed tables:

• Basic compression—You cannot specify a default value for an added column.

• OLTP compression—If a default value is specified for an added column, the column must be NOT NULL. Added nullable columns with default values are not supported.

The following restrictions apply when dropping columns in compressed tables:

• Basic compression—Dropping a column is not supported.

• OLTP compression—DROP COLUMN is supported, but internally the database sets the column UNUSED to avoid long-running decompression and recompression operations.

Notes and Other Restrictions for Compressed Tables

• Online segment shrink is not supported for compressed tables.

• The table compression methods described in this section do not apply to SecureFile large objects (LOBs). SecureFile LOBs have their own compression methods. See Oracle Database SecureFiles and Large Objects Developer's Guide for more information.

• Compression technology uses CPU. You should ensure that you have enough available CPU to handle the additional load.

• Tables created with basic compression have the PCT_FREE parameter automatically set to 0 unless you specify otherwise.

Packing Compressed Tables

If you use conventional DML on a table compressed with basic compression, then all inserted and updated rows are stored uncompressed. To "pack" the compressed table such that these rows are compressed, you can use an ALTER TABLE MOVE statement. This operation takes an exclusive lock on the table, and therefore prevents any updates and loads until it completes. If this is not acceptable, you can use online table redefinition.

Consider Encrypting Columns That Contain Sensitive Data

You can encrypt individual table columns that contain sensitive data. Examples of sensitive data include social security numbers, credit card numbers, and medical records. Column encryption is transparent to your applications, with some restrictions.

Although encryption is not meant to solve all security problems, it does protect your data from users who try to circumvent the security features of the database and access database files directly through the operating system file system.

Column encryption uses the transparent data encryption feature of Oracle Database, which requires that you create an Oracle wallet to store the master encryption key for the database. The wallet must be open before you can create a table with encrypted columns and before you can store or retrieve encrypted data. When you open the wallet, it is available to all sessions, and it remains open until you explicitly close it or until the database is shut down.

Transparent data encryption supports industry-standard encryption algorithms, including the following Advanced Encryption Standard (AES) and Triple Data Encryption Standard (3DES) algorithms:

• AES256

• AES192

• AES128

• 3DES168

You choose the algorithm to use when you create the table. All encrypted columns in the table use the same algorithm. The default is AES192. The encryption key length is implied by the algorithm name. For example, the AES128 algorithm uses 128-bit keys.

If you plan on encrypting many columns in one or more tables, you may want to consider encrypting an entire tablespace instead and storing these tables in that tablespace. Tablespace encryption, which also uses the transparent data encryption feature but encrypts at the physical block level, can perform better than encrypting many columns. Another reason to encrypt at the tablespace level is to address the following limitations of column encryption:

• If the COMPATIBLE initialization parameter set to 10.2.0, which is the minimum setting to enable transparent data encryption, data from encrypted columns that is involved in a sort or hash-join and that must be written to a temporary tablespace is written in clear text, and thus exposed to attacks. You must set COMPATIBLE to 11.1.0 or higher to ensure that encrypted data written to a temporary tablespace remains encrypted. Note that as long as COMPATIBLE is set to 10.2.0 or higher, data from encrypted columns remains encrypted when written to the undo tablespace or the redo log.

• Certain data types, such as object data types, are not supported for column encryption.

• You cannot use the transportable tablespace feature for a tablespace that includes tables with encrypted columns.

• Other restrictions, which are detailed in Oracle Database Advanced Security Administrator's Guide.

Understand Deferred Segment Creation

Beginning with Oracle Database 11g Release 2, when you create heap-organized tables in a locally managed tablespace, the database defers table segment creation until the first row is inserted.

In addition, segment creation is deferred for any LOB columns of the table, any indexes created implicitly as part of table creation, and any indexes subsequently explicitly created on the table.

The advantages of this space allocation method are the following:

• It saves a significant amount of disk space in applications that create hundreds or thousands of tables upon installation, many of which might never be populated.

• It reduces application installation time.

There is a small performance penalty when the first row is inserted, because the new segment must be created at that time.

To enable deferred segment creation, compatibility must be set to '11.2.0' or higher.

The new clauses for the CREATE TABLE statement are:

• SEGMENT CREATION DEFERRED

• SEGMENT CREATION IMMEDIATE

These clauses override the default setting of the DEFERRED_SEGMENT_CREATION initialization parameter, TRUE, which defers segment creation. To disable deferred segment creation, set this parameter to FALSE.

Note that when you create a table with deferred segment creation, the new table appears in the *_TABLES views, but no entry for it appears in the *_SEGMENTS views until you insert the first row.

You can verify deferred segment creation by viewing the SEGMENT_CREATED column in *_TABLES, *_INDEXES, and *_LOBS views for nonpartitioned tables, and in *_TAB_PARTITIONS, *_IND_PARTITIONS, and *_LOB_PARTITIONS views for partitioned tables.

The following example creates two tables to demonstrate deferred segment creation. The first table uses the SEGMENT CREATION DEFERRED clause. No segments are created for it initially. The second table uses the SEGMENT CREATION IMMEDIATE clause and, therefore, segments are created for it immediately.

CREATE TABLE part_time_employees (
    empno NUMBER(8),
    name VARCHAR2(30),
    hourly_rate NUMBER (7,2)
    )   
    SEGMENT CREATION DEFERRED;
 
CREATE TABLE hourly_employees (
    empno NUMBER(8),
    name VARCHAR2(30),
    hourly_rate NUMBER (7,2)
    ) 
   SEGMENT CREATION IMMEDIATE
   PARTITION BY RANGE(empno)
    (PARTITION empno_to_100 VALUES LESS THAN (100),
    PARTITION empno_to_200 VALUES LESS THAN (200));

The following query against USER_SEGMENTS returns two rows for HOURLY_EMPLOYEES, one for each partition, but returns no rows for PART_TIME_EMPLOYEES because segment creation for that table was deferred.

SELECT segment_name, partition_name FROM user_segments;
 
SEGMENT_NAME         PARTITION_NAME                                
-------------------- ------------------------------                             
HOURLY_EMPLOYEES     EMPNO_TO_100                       
HOURLY_EMPLOYEES     EMPNO_TO_200       

The USER_TABLES view shows that PART_TIME_EMPLOYEES has no segments:

SELECT table_name, segment_created FROM user_tables;
 

TABLE_NAME                     SEGMENT_CREATED
------------------------------ ----------------------------------------
PART_TIME_EMPLOYEES            NO
HOURLY_EMPLOYEES               N/A

For the HOURLY_EMPLOYEES table, which is partitioned, the segment_created column is N/A because the USER_TABLES view does not provide that information for partitioned tables. It is available from the USER_TAB_PARTITIONS view, shown below.

SELECT table_name, segment_created, partition_name
 FROM user_tab_partitions;

TABLE_NAME           SEGMENT_CREATED      PARTITION_NAME
-------------------- -------------------- ------------------------------
HOURLY_EMPLOYEES     YES                  EMPNO_TO_100
HOURLY_EMPLOYEES     YES                  EMPNO_TO_200

The following statements add employees to these tables.

INSERT INTO hourly_employees VALUES (99, 'FRose', 20.00);
INSERT INTO hourly_employees VALUES (150, 'LRose', 25.00);
 
INSERT INTO part_time_employees VALUES (50, 'KReilly', 10.00);

Repeating the same SELECT statements as before shows that PART_TIME_EMPLOYEES now has a segment, due to the insertion of row data. HOURLY_EMPLOYEES remains as before.

SELECT segment_name, partition_name FROM user_segments;
 
SEGMENT_NAME         PARTITION_NAME
-------------------- ------------------------------
PART_TIME_EMPLOYEES
HOURLY_EMPLOYEES     EMPNO_TO_100
HOURLY_EMPLOYEES     EMPNO_TO_200

SELECT table_name, segment_created FROM user_tables;
 
TABLE_NAME           SEGMENT_CREATED
-------------------- --------------------
PART_TIME_EMPLOYEES  YES
HOURLY_EMPLOYEES     N/A
                                         

The USER_TAB_PARTITIONS view does not change.

Materializing Segments

Beginning with Oracle Database 11g release 2 (11.2.0.2), the DBMS_SPACE_ADMIN package includes the MATERIALIZE_DEFERRED_SEGMENTS() procedure, which enables you to materialize segments for tables, table partitions, and dependent objects created with deferred segment creation enabled.

You can add segments as needed, rather than starting with more than you need and using database resources unnecessarily.

The following example materializes segments for the EMPLOYEES table in the HR schema.

BEGIN
  DBMS_SPACE_ADMIN.MATERIALIZE_DEFERRED_SEGMENTS(
    schema_name  => 'HR',
    table_name   => 'EMPLOYEES');
END;

Estimate Table Size and Plan Accordingly

Estimate the sizes of tables before creating them. Preferably, do this as part of database planning. Knowing the sizes, and uses, for database tables is an important part of database planning.

You can use the combined estimated size of tables, along with estimates for indexes, undo space, and redo log files, to determine the amount of disk space that is required to hold an intended database. From these estimates, you can make correct hardware purchases.

You can use the estimated size and growth rate of an individual table to better determine the attributes of a tablespace and its underlying datafiles that are best suited for the table. This can enable you to more easily manage the table disk space and improve I/O performance of applications that use the table.

Restrictions to Consider When Creating Tables

Here are some restrictions that may affect your table planning and usage:

• Tables containing object types cannot be imported into a pre-Oracle8 database.

• You cannot merge an exported table into a preexisting table having the same name in a different schema.

• You cannot move types and extent tables to a different schema when the original data still exists in the database.

• Oracle Database has a limit on the total number of columns that a table (or attributes that an object type) can have. See Oracle Database Reference for this limit.

  Further, when you create a table that contains user-defined type data, the database maps columns of user-defined type to relational columns for storing the user-defined type data. This causes additional relational columns to be created. This results in "hidden" relational columns that are not visible in a DESCRIBE table statement and are not returned by a SELECT * statement. Therefore, when you create an object table, or a relational table with columns of REF, varray, nested table, or object type, be aware that the total number of columns that the database actually creates for the table can be more than those you specify.

  See Also:

  Oracle Database Object-Relational Developer's Guide for more information about user-defined types