Systems Engineering and RDBMS

DBMS_SPACE.CREATE_INDEX_COST

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.

CREATE_INDEX_COST:

Here is the syntax of the procedure.

DBMS_SPACE.CREATE_INDEX_COST
(
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
(
ID NUMBER(9),
Name VARCHAR(50),
Create_Date_Time DATE,
Mod_Date_Time DATE,
User_ID VARCHAR(15)
)
/

INSERT INTO SAMPLE
SELECT ROWNUM,Object_Name,SYSDATE,NULL,’DECIPHER’
FROM all_objects
/

COMMIT;

Begin
Dbms_Stats.Gather_Table_Stats(User,’SAMPLE’,
Method_Opt=>’For Table For All Indexes For All Indexed Columns’,
Cascade=>True);
End;
/

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

DECLARE
v_used_bytes NUMBER(10);
v_Allocated_Bytes NUMBER(10);
BEGIN

DBMS_SPACE.CREATE_INDEX_COST
(
‘CREATE INDEX SAMPLE_IND_1 ON SAMPLE(User_ID) ‘,
v_used_Bytes,
v_Allocated_Bytes
);

DBMS_OUTPUT.PUT_LINE(‘Used Bytes: ‘ || TO_CHAR(v_used_Bytes));
DBMS_OUTPUT.PUT_LINE(‘Allocated Bytes: ‘ || TO_CHAR(v_Allocated_Bytes));

END;
/

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.

About these ads

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers

%d bloggers like this: