Oracle Database Administration: The Essential Refe

Tables

Tables are the basic units of data storage in an Oracle database, so their configuration and resulting performance will have a large impact on overall database performance. Some guidelines for table configuration are as follows:

• Try to estimate how big a table will be and allocate a sufficiently large INITIAL extent to hold the entire table. However, if you are using Parallel Query, allocate the total space across as many extents in different datafiles as the degree of parallelism for the table.

• Consider using multiple tablespaces for tables, each for a different size or type of table. For example, you might have three tablespaces: LARGE_DATA, MEDIUM_DATA, and SMALL_DATA, each of which would be used to hold tables of a particular size. If you are using multiple tablespaces for tables, be sure to allocate each table in the appropriate tablespace.

• Be sure to assign a DEFAULT TABLESPACE to each user. If one is not assigned, Oracle will use the SYSTEM tablespace by default.

• If possible, always allocate INITIAL and NEXT extents in multiples of the same size units; for example, allocate in multiples of 512K. This way, extents will be of uniform size and it will be easier to allocate additional extents without fragmenting the tablespace. Where possible, consider making all extents in a tablespace the same size.

• Set the PCTINCREASE parameter to 0, in order to prevent runaway extent allocation and to preserve uniform extent sizes.

• Set MAXEXTENTS to UNLIMITED. This will prevent running out ...