How Are Storage Parameter Tables Used?

Extent Sizes

When you use the File Size Definition form to define the initial and next extent sizes for tables, you do so by specifying the number of rows.

To directly specify extent sizes in kilobytes rather than rows, use law_dba_table and law_dba_index. For example, to define the extent sizes for the APCOMMENTS table, issue the following command from a SQL utility provided by the database vendor.

update LAW_DBA_TABLE
    set initial_extent = 24,
    next_extent = 12
    where table_name = ‘APCOMMENTS';

After you change the value in law_dba_table, run the bldora11ddl / bldora12ddl utility to drop and rebuild the APCOMMENTS table using the new definition. Because the utility drops all data with the table, make sure to unload and reload any data that you need to keep. The utility recreates the APCOMMENTS table with an initial extent size of 24K and a next extent size of 12K. For more information on extents and extent sizing, see your Oracle documentation.

Multiple Index Locations

When you use the Database Space Definition form to define a database space, you also define a separate location to hold the indexes in that database space. This method restricts you to one location for all indexes on a table.

If a table has multiple indexes that are frequently accessed simultaneously, putting them on different disks could speed up physical input and output. Also, a large table with many indexes can make it impossible to put all indexes on that table on a single disk drive. In either of these cases, it helps to separate the indexes on different drives.

The law_dba_index table lets you override the settings on the Database Space Definition form and separate the indexes from a single table to different database spaces. For example, to move the first index on the APCOMMENTS table to a new Oracle table space, issue the following command from a SQL utility provided by the database vendor:

update LAW_DBA_TABLE
    set initial_extent = 24,
    next_extent = 12
    where table_name = ‘APCOMMENTS';

After you change the value in law_dba_index, run the bldora11ddl / bldora12ddl utility to drop and rebuild the APCSET1 index using the new definition. At the command prompt, type:

bldora11ddl / bldora12ddl -URI dataarea apcomments

All indexes on APCOMMENTS now reside in the table spaces specified in law_dba_index. If an index has no value for the table space field in law_dba_index, the index defaults to the location specified on the Database Space Definition form.

Fragmented Table or Index Repair

A fragmented table or index can cause performance problems, primarily because the disk head must search over multiple sectors to retrieve data. If a fragmented table or index occurs because of sequentially loaded data, simply rebuild the index using the bldora11ddl / bldora12ddl utility to defragment the index.