Using dbms_space.create_table_cost Oracle Database Tips by Donald BurlesonMay 26, 2015

For basic table and index sizing estimates see these notes on Tablespace and Datafile Sizing, and note that some Oracle professionals offer Oracle database tablespace sizing spreadsheets

Oracle provides the dbms_space built-in package to facilitate computing and estimating the space used by database objects (table).  Let's take a close look at how we can use the dbms_space package to estimate the required storage for tables and indexes.

Using the create_table_cost procedure

The dbms_space.create_table_cost procedure is used for estimating the final size of a table or index (with dbms_space.create_index_cost) by accepting input sizing parameters and computing the estimated used bytes and allocated bytes for a table or index.  The Oracle docs note the sizing input columns for create_table_cost:

avg_row_size	The anticipated average row size in the table
colinfos	The description of the columns
row_count	The anticipated number of rows in the table
pct_free	The percentage of free space in each block for future expansion of existing rows due to updates
used_bytes	The space used by user data
alloc_bytes	The size of the object taking into account the tablespace extent characteristics

Here is an example invocation of dbms_space.create_table_cost:

CREATE TABLE SAMPLE
(
ID NUMBER(9),
Name VARCHAR(50),
Create_Date_Time DATE,
Mod_Date_Time DATE,
User_ID VARCHAR(15)
)
Here is the code snippet:

SET SERVEROUTPUT ON

DECLARE
v_used_bytes NUMBER(10);
v_Allocated_Bytes NUMBER(10);
v_type sys.create_table_cost_columns;
BEGIN
v_Type := sys.create_table_cost_columns
(
sys.create_table_cost_colinfo(?NUMBER?,9),
sys.create_table_cost_colinfo(?VARCHAR2′,50),
sys.create_table_cost_colinfo(?VARCHAR2′,15),
sys.create_table_cost_colinfo(?DATE?,NULL),
sys.create_table_cost_colinfo(?DATE?,NULL)
);

DBMS_SPACE.CREATE_TABLE_COST(?USERS?,v_Type,10000,7,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;/

Using create_index_cost procedure to estimate index storage

For dbms_space.create_index_cost, the procedure accepts the DDL for a "create index" statement and outputs the storage needed to create the index.  The Oracle documentation notes these input parameters for dbms_space.create_index_cost:

ddl	The create index DDL statement
used_bytes	The number of bytes representing the actual index data
alloc_bytes	Size of the index when created in the tablespace
plan_table	Which plan table to use, default NULL

Note:  Using dbms_space.create_index_cost requires that the table already exist and that the table have current metadata statistics (as computed with dbms_stats).

See my related notes on estimating Oracle table and index sizes:

 

---