Archive for July 2nd, 2007

Yesterday, we had discussed about cost (in terms of disk space usage) associated with creating a table. DBMS_SPACE package provides yet another procedure to identify the cost of space usage for a specific index. If we want to create a new index or want to know the effect of changing existing index parameters, this procedure will let us know the impact of it on storage. Prior to 10g, there was no straight forward way of knowing this.

This procedure takes actual index creation statement as an argument with appropriate storage parameters and spits out the storage required for an index. Certain points to keep in mind while running this procedure:

• Table must exist otherwise ‘table does not exist’ error will be displayed upon execution of the procedure.
• Statistics on the table should be up to date as procedure estimates the size of an index based on the available statistics.
• Procedure will not give any error, if recent statistics is not available but in that scenario, result may not be accurate.

Let us create table first. We will also populate it with some data and will collect the statistics for new table. Connect to SQL*Plus and run following SQLs.

We would like to create index on the NAME column but before creating an index, we want to check how much storage will be required for an index. Run the following code snippet to execute the stored procedure:

To gauge the impact on the storage space for new indexes, this procedure becomes very useful. It definitely helps us in estimating the space required before issuing create command so that we don’t run into space issues when the actual create statement is executed.