Estimating the Size of an Index Before Creation- Tables and Constraints

If you do not work with large databases, then you do not need to worry about estimating the amount of space an index will initially consume. However, for large databases, you absolutely need an estimate on how much space it will take to create an index. If you have a large table in a data warehouse environment, a corresponding index could easily be hundreds of gigabytes in size. In this situation, you need to ensure that the database has adequate disk space available.

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. However, since it might be difficult to build a test environment complete replica of production data, a subset can be used to extrapolate the size required in production. Another way to estimate the size of an index is using the DBMS_SPACE.CREATE_INDEX_COST procedure.

For reference, here is the table creation script that the index used in the subsequent examples is based on:

SQL> CREATE TABLE cust (cust_id NUMBER , last_name VARCHAR2(30) , first_name VARCHAR2(30) ) TABLESPACE users;

Next, several thousand records are inserted into the prior table. Here is a snippet of the insert statement (note that multivalue inserts are available starting in 23c):

SQL> insert into cust values(7, ‘ACER’, ‘SCOTT’), (5, ‘STARK’, ‘JIM’),

(3, ‘GREY’, ‘BOB’), (11, ‘KAHN’, ‘BRAD’), (21, ‘DEAN’, ‘ANN’), …

Now, suppose you want to create an index on the CUST table like this:

SQL> create index cust_idx1 on cust(last_name);

Here is the procedure for estimating the amount of space the index will initially consume:

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_idx1 on cust (last_name)’, :used_bytes, :alloc_bytes);

SQL> print :used_bytes

Here is some sample output for this example:

USED_BYTES ———-19800000

SQL> print :alloc_bytes ALLOC_BYTES

———–33554432

Statistics need to be gathered to give better results, and it of course depends on the number of records. Indexes will continue to grow as rows are inserted into the tables, and because some table might have multiple indexes, this is where the index space can grow quickly.

Creating Indexes and Temporary Tablespace Space

Related to space usage, sometimes DBAs forget that Oracle often requires space in either memory or disk to sort an index as it is created. If the available memory area is consumed, then Oracle allocates disk space as required within the default temporary tablespace. If you are creating a large index, you may need to increase the size of your temporary tablespace.

Another approach is to create an additional temporary tablespace and then assign it to be the default temporary tablespace of the user creating the index. After the index is created, reassign the user’s default temporary tablespace to the original temporary tablespace.