Developing and Implementing Applications, continued - Estimating Space Requirements for Indexes (Page 6 of 8 )
As of Oracle Database 10g, you can use the CREATE_INDEX_COST procedure of the DBMS_ SPACE package to estimate the space required by an index. The procedure determines the space required for a table based on attributes such as the tablespace storage parameters, the tablespace block size, the number of rows, and the average row length. The procedure is valid for both dictionary-managed and locally managed tablespaces.
For index space estimations, the input variables include the DDL commands executed to create the index and the name of the local plan table (if one exists). The index space estimates rely on the statistics for the related table. You should be sure those statistics are correct before starting the space-estimation process; otherwise, the results will be skewed.
The variables for the CREATE_INDEX_COST procedure are described in the following table:
Parameter | Description |
Ddl | The create index command |
used_bytes | The number of bytes used by the index’s data |
alloc_bytes | The number of bytes allocated for the index’s extents |
plan_table | The plan table to use (the default is NULL) |
Because the CREATE_INDEX_COST procedure bases its results on the table’s statistics, you cannot use this procedure until the table has been created, loaded, and analyzed. The following example estimates the space required for a new index on the BOOKSHELF table. The tablespace designation is part of the create index command passed to the CREATE_INDEX_COST procedure as part of the ddl variable value.
declare
calc_used_bytes NUMBER;
calc_alloc_bytes NUMBER;
begin
DBMS_SPACE.CREATE_INDEX_COST (
ddl => 'create index BOOK_CAT on BOOKSHELF '||
'(CategoryName) tablespace BOOKS_INDEX',
used_bytes => calc_used_bytes,
alloc_bytes => calc_alloc_bytes
);
DBMS_OUTPUT.PUT_LINE('Used bytes = '||calc_used_bytes);
DBMS_OUTPUT.PUT_LINE('Allocated bytes = '||calc_alloc_bytes);
end;
/
The output of the script will show the used and allocated bytes values for the proposed index.
Estimating the Proper pctfree
The pctfree value represents the percentage of each data block that is reserved as free space. This space is used when a row that has already been stored in that data block grows in length, either by updates of previously NULL fields or by updates of existing values to longer values.
There is no single value for pctfree that will be adequate for all tables in all databases. To simplify space management, choose a consistent set of pctfree values:
- For indexes whose key values are rarely changed: 2
- For tables whose rows seldom change: 2
- For tables whose rows frequently change: 10 to 30
Why maintain free space in a table or index even if the rows seldom change? Oracle needs space within blocks to perform block maintenance functions. If there is not enough free space available (for example, to support a large number of transaction headers during concurrent inserts), Oracle will temporarily allocate part of the block’s pctfree area. You should choose a pctfree value that supports this allocation of space. To reserve space for transaction headers in insert-intensive tables, set the initrans parameter to a nondefault value. In general, your pctfree area should be large enough to hold several rows of data.
NOTE
Oracle automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.
Because pctfree is tied to the way in which updates occur in an application, determining the adequacy of its setting is a straightforward process. The pctfree setting controls the number of records that are stored in a block in a table. To see if pctfree has been set correctly, first determine the number of rows in a block. You can use the DBMS_STATS package to gather statistics. If the pctfree setting is too low, the number of chained rows will steadily increase. You can monitor the database’s V$SYSSTAT view for increasing values of the “table fetch continued row” action; these indicate the need for the database to access multiple blocks for a single row.
NOTE
When rows are moved due to inadequate space in the pctfree area, the move is called a row migration. Row migration will impact the performance of your transactions.
NOTE
For indexes that will support a large number of inserts, pctfree may need to be as high as 50 percent.
Determining the Proper pctused
The pctused value determines when a used block is re-added to the list of blocks into which rows can be inserted. For example, consider a table that has a pctfree value of 20 and a pctused value of 50. When rows are inserted into the table, Oracle will keep 20 percent of each block free (for use by later updates of the inserted records). If you now begin to delete records from the block, Oracle will not automatically reuse the freed space inside the block. New rows will not be inserted into the block until the block’s used space falls below its pctused percentage—in this case, 50 percent.
The pctused value, by default, is set to 40. If your application features frequent deletions and you use the default value for pctused, you may have many blocks in your table that are only 40 percent used.
For most systems, you can set pctused so that pctused plus pctfree equals 80. If your pctfree setting is 20 percent, for example, set your pctused value to 60 percent. That way, at least 60 percent of each block will be used, saving 20 percent of the block for updates and row extensions.
When creating tablespaces, you can enable Automatic Segment Space Management, in which case Oracle will manage the freelist dynamically. When Automatic Segment Space Management is enabled, you do not set the pctused setting.
Reverse Key Indexes
In a reverse key index, the values are stored backward—for example, a value of 2201 is stored as 1022. If you use a standard index, consecutive values are stored near each other. In a reverse key index, consecutive values are not stored near each other. If your queries do not commonly perform range scans and you are concerned about I/O contention in your indexes, reverse key indexes may be a tuning solution to consider. When sizing a reverse key index, follow the same method used to size a standard index, as shown in the prior sections of this chapter.
Sizing Bitmap Indexes
If you create a bitmap index, Oracle will dynamically compress the bitmaps generated. The compression of the bitmap may result in substantial storage savings. To estimate the size of a bitmap index, estimate the size of a standard (B*tree) index on the same columns using the methods provided in the preceding sections of this chapter. After calculating the space requirements for the B*tree index, divide that size by 10 to determine the most likely maximum size of a bitmap index for those columns. In general, bitmap indexes will be between 2 and 10 percent of the size of a comparable B*tree index. The size of the bitmap index will depend on the variability and number of distinct values in the indexed columns.
Sizing Index-Organized Tables
An index-organized table is stored sorted by its primary key. The space requirements of an index-organized table closely mirror those of an index on all of the table’s columns. The difference in space estimation comes in calculating the space used per row, because an index-organized table does not have RowIDs.
The following listing gives the calculation for the space requirement per row for an index-organized table (note that this storage estimate is for the entire row, including its out-of-line storage):
Row length for sizing = Average row length
+ number of columns
+ number of long columns
+ 2 header bytes
Enter this value as the row length when using the CREATE_TABLE_COST procedure for the index-organized table.
Next: Sizing Tables That Contain Large Objects (LOBs) >>
More Oracle Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter five of the book Oracle Database 10g DBA Handbook, written by Kevin Loney and Bob Bryla (McGraw-Hill/Osborne, 2005; ISBN: 0072231459). Check it out today at your favorite bookstore. Buy this book now.
|
|
