This section describes guidelines to follow when managing tables. Following these guidelines can make the management of your tables easier and can improve performance when creating the table, as well as when loading, updating, and querying the table data.
The following topics are discussed:
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
Normalize each table.
Select the appropriate datatype 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.
What types of tables can you create? Here are some choices:
|Type of Table||Description|
|Ordinary (heap-organized) table||This is the basic, general purpose type of table which is the primary subject of this chapter. Its data is stored as an unordered collection (heap)|
|Clustered table||A clustered table is a table that is part of a cluster. A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.
Clusters and clustered tables are discussed in Chapter 20, "Managing Clusters".
|Index-organized table||Unlike an ordinary (heap-organized) table, data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Besides storing the primary key column values of an index-organized table row, each index entry in the B-tree stores the nonkey column values as well.
Index-organized tables are discussed in "Managing Index-Organized Tables".
|Partitioned table||Partitioned tables allow your data to be broken down into smaller, more manageable pieces called partitions, or even subpartitions. Each partition can be managed individually, and can operate independently of the other partitions, thus providing a structure that can be better tuned for availability and performance.
Partitioned tables are discussed in Oracle Database VLDB and Partitioning Guide.
It is advisable to specify the
TABLESPACE clause in a
CREATE TABLE statement to identify the tablespace that is to store the new table. 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.
You can utilize 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".
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.
NOLOGGING clause also specifies that subsequent direct loads using SQL*Loader and direct load
INSERT operations are not logged. Subsequent DML statements (
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 you are also parallelizing the table creation.
As your database grows in size to gigabytes or terabytes and beyond, consider using table compression. Table compression saves disk space and reduces memory use in the buffer cache. Table compression can also speed up query execution during reads. There is, however, a cost in CPU overhead for data loading and DML. Table compression is completely transparent to applications. It is especially useful in online analytical processing (OLAP) systems, where there are lengthy read-only operations, but can also be used in online transaction processing (OLTP) systems.
You specify table compression with the
COMPRESS clause of the
TABLE statement. You can enable compression for an existing table by using this clause in an
TABLE statement. In this case, the only data that is compressed is the data inserted or updated after compression is enabled. Similarly, you can disable table compression for an existing compressed table with the
NOCOMPRESS statement. In this case, all data the was already compressed remains compressed, and new data is inserted uncompressed.
You can enable compression for all table operations or you can enable it for direct-path inserts only. When compression is enabled for all operations, compression occurs during all DML statements and when data is inserted with a bulk (direct-path) insert operation. To enable compression for conventional DML, you must set the
COMPATIBLE initialization parameter to 11.1.0 or higher.
To enable compression for all operations you must use the
OPERATIONS clause. To enable compression for direct-path inserts only, you use the
OPERATIONS clause. The keyword
COMPRESS by itself is the same as the clause
OPERATIONS, and invokes the same compression behavior as previous database releases.
When you enable compression for all operations on a table, you can add and drop table columns. If you enable compression for direct-path inserts only, you cannot drop columns, and you can add columns only if you do not specify default values.
The following example enables compression for all operations on the table
transaction, which is used in an OLTP application:
CREATE TABLE transaction ( ... ) COMPRESS FOR ALL OPERATIONS;
The next two examples enable compression for direct-path insert only on the
sales_history table, which is a fact table in a data warehouse:
CREATE TABLE sales_history ( ... ) COMPRESS FOR DIRECT_LOAD OPERATIONS; CREATE TABLE sales_history ( ... ) COMPRESS;
You can enable or disable compression at the partition level. You can therefore have a table with both compressed and uncompressed partitions. If the compression settings for a table and one of its partitions disagree, the partition setting has precedence for the partition. In the following example, all partitions except the
northeast partition are compressed.
CREATE TABLE sales (saleskey number, quarter number, product number, salesperson number, amount number(12, 2), region varchar2(10)) COMPRESS PARTITION BY LIST (region) (PARTITION northwest VALUES ('NORTHWEST'), PARTITION southwest VALUES ('SOUTHWEST'), PARTITION northeast VALUES ('NORTHEAST') NOCOMPRESS, PARTITION southeast VALUES ('SOUTHEAST'), PARTITION western VALUES ('WESTERN'));
*_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 data dictionary view indicates the partitions that are compressed. In addition, the
COMPRESS_FOR column indicates whether the table is compressed
OPERATIONS or for
SQL> SELECT table_name, compression, compress_for FROM user_tables; TABLE_NAME COMPRESS COMPRESS_FOR ---------------- -------- ------------------ T1 DISABLED T2 ENABLED DIRECT LOAD ONLY T3 ENABLED FOR ALL OPERATIONS
Oracle Database SQL Language Reference for more details on the
COMPRESS statements, including restrictions
Oracle Database VLDB and Partitioning Guide for more information on table partitioning
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:
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:
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.
Oracle Database Advanced Security Administrator's Guide for more information about transparent data encryption and for instructions for creating and opening wallets
Oracle Database SQL Language Reference for information about the
Oracle Real Application Clusters Administration and Deployment Guide for information on using an Oracle wallet in an Oracle Real Application Clusters environment
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.
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