Databases Reference
In-Depth Information
These decision points are detailed in the following subsections.
Estimating the Size of an Index Before Creation
Before creating an index on a large table, you may want to estimate the space it will consume. The best
way to predict the size of an index is to create it in a test environment that has a representative set of
production data. If you can't build a complete replica of production data, a subset of data can often be
used to extrapolate the size required in production. If you don't have the luxury of using a cut of
production data, you can also estimate the size of an index using the
DBMS_SPACE.CREATE_INDEX_COST
procedure. For example, this code estimates the size of creating an index on the
FIRST_NAME
column of
the
CUST
table:
SQL> set serverout on
SQL> exec dbms_stats.gather_table_stats(user,'CUST');
SQL> variable used_bytes number
SQL> variable alloc_bytes number
SQL> exec dbms_space.create_index_cost( 'create index cust_idx2 on cust(first_name)', -
:used_bytes, :alloc_bytes );
SQL> print :used_bytes
Here is some sample output for this example:
USED_BYTES
----------
2890104
SQL> print :alloc_bytes
ALLOC_BYTES
-----------
4440064
The
used_bytes
variable gives you an estimate of how much room is required for the index data. The
alloc_bytes
variable provides an estimate of how much space will be allocated within the tablespace.
Next, go ahead and create the index.
SQL> create index cust_idx2 on cust(first_name);
The actual amount of space consumed is shown by this query:
SQL> select bytes from user_segments where segment_name='CUST_IDX2';
The output indicates that the estimated amount of allocated bytes is in the ballpark of the amount
of space actually consumed.
BYTES
----------
4718592
