CHAPTER 11. 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, "Guidelines for Managing Schema Objects."
See Also: This chapter contains several references to Oracle Server Manager. For more information about performing specific tasks using Server Manager/GUI or Server Manager/LineMode, see the Oracle SNMP Support Reference Guide.
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. A DBA is 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:
- Each column is of the proper datatype.
- Columns that allow nulls are defined last, to conserve storage space.
- Tables are clustered whenever appropriate, to conserve storage space and optimize performance of SQL statements.
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 the Space Usage of 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:
- increase the performance of the database system
The following examples show how incorrect storage locations of schema objects can affect a database:
- decrease the time needed for database administration
- If users' objects are created in the SYSTEM tablespace, the performance of Oracle can be reduced, since both data dictionary objects and user objects must contend for the same datafiles.
See Also: For information about specifying tablespaces, see "Assigning Tablespace Quotas" .
- If an application's associated tables are arbitrarily stored in various tablespaces, the time necessary to complete administrative operations (such as backup and recovery) for that application's data can be increased.
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 Oracle7 Server Tuning guide.
For information about the CREATE TABLE command, see the Oracle7 Server SQL Reference.
Consider Creating UNRECOVERABLE Tables
You can create a table unrecoverably 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 a table unrecoverably has the following benefits:
- Space is saved in the redo log files.
- The time it takes to create the table is decreased.
In general when creating a table unrecoverably, the relative performance improvement is greater for larger tables than for smaller tables. Creating small tables unrecoverably 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.
- Performance improves for parallel creation of large tables.
When you create a table unrecoverably 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.
Estimate Table Size and Set Storage Parameters
Estimating the sizes of tables before creating them is useful for the following reasons:
- You can use the combined estimated size of tables, along with estimates for indexes, rollback segments, 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 and other decisions.
- You can use the estimated size of an individual table to better manage the disk space that the table will use. When a table is created, you can set appropriate storage parameters and improve I/O performance of applications that use the table.
For example, assume that you estimate the maximum size of a table before creating it. If you then set the storage parameters when you create the table, fewer extents will be allocated for the table's data segment, and all of the table's data will be stored in a relatively contiguous section of disk space. This decreases the time necessary for disk I/O operations involving this table.
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.
Note: 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. (Note that temporary segments always use the default STORAGE settings for their tablespaces.)
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,
hiredate DATE DEFAULT (sysdate),
deptno NUMBER(3) NOT NULL
CONSTRAINT dept_fkey REFERENCES dept)
STORAGE ( INITIAL 50K
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 20. For more information about tablespace quotas, see Chapter 19.
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:
- 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)
- to enable or disable integrity constraints or triggers associated with the table
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.
- to drop integrity constraints associated with the table
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:
ALTER TABLE emp
Warning: 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.
See Also: See page 16 - 18 for information about how Oracle manages dependencies.
- If a view or PL/SQL program unit depends on a base table, the alteration of the base table may affect the dependent object.
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 the Oracle7 Parallel Server Concepts & Administration guide.
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:
DROP TABLE emp;
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:
DROP TABLE emp CASCADE CONSTRAINTS;
Warning: Before dropping a table, familiarize yourself with the consequences of doing so: