Estimate the index size before creating an Oracle index (dbms_space.create_index_cost)

Estimate the index size before creating an Oracle Index

Estimate the size of a field in a table before creating an index. The best way is to create an index in the test database for prediction. If no test environment is available, you can use dbms_space.create_index_cost to estimate its size.

Syntax:

DBMS_SPACE.CREATE_INDEX_COST (
Ddl IN VARCHAR2,
Used_bytes out number,
Alloc_bytes out number,
Plan_table IN VARCHAR2 default null );

Example:

SQL> create table xyc_test (id int, name varchar (32 ));

The table has been created.

SQL> begin
2 for I in 1 .. 100000 loop
3 insert into xyc_test values (I, 'yundun ');
4 end loop;
5 commit;
6 end;
7/

The PL/SQL process is successfully completed.

SQL> exec dbms_stats.gather_table_stats (user, 'xyc _ test ');

The PL/SQL process is successfully completed.

SQL> variable used_bytes number;
SQL> variable alloc_bytes number;
SQL> exec DBMS_SPACE.CREATE_INDEX_COST ('create index x_idx on xyc_test (id) ',: used_bytes,: alloc_bytes );

The PL/SQL process is successfully completed.

SQL> print: used_bytes
500000
---- Estimate the allocation size
SQL> print: alloc_bytes
3145728

SQL> create index x_idx on xyc_test (id );

The index has been created.
---- The actual allocation size, which may be related to the amount of data.
SQL> select bytes from user_segments where segment_name = 'x _ idx ';
2097152

1 2 Next Page