Posted by decipherinfosys on July 2, 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.
Here is the syntax of the procedure.
ddl IN VARCHAR2,
used_bytes OUT NUMBER,
alloc_bytes OUT NUMBER,
plan_table IN VARCHAR2 DEFAULT NULL
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.
CREATE TABLE SAMPLE
INSERT INTO SAMPLE
Method_Opt=>’For Table For All Indexes For All Indexed Columns’,
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:
SET SERVEROUTPUT ON
‘CREATE INDEX SAMPLE_IND_1 ON SAMPLE(User_ID) ‘,
DBMS_OUTPUT.PUT_LINE(‘Used Bytes: ‘ || TO_CHAR(v_used_Bytes));
DBMS_OUTPUT.PUT_LINE(‘Allocated Bytes: ‘ || TO_CHAR(v_Allocated_Bytes));
Following is the output of the procedure.
Used Bytes: 452889
Allocated Bytes: 2097152
PL/SQL procedure successfully completed.
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.
Sorry, the comment form is closed at this time.