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

Estimate the index size (dbms_space.create_index_cost) before creating an index for a field in a table. 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: www.2cto.com DBMS_SPACE.CREATE_INDEX_COST (ddl IN VARCHAR2, used_bytes out number, alloc_bytes out number, plan_table IN VARCHAR2 default null); example: [SQL] SQL> create table xyc_test (id int, name varchar (32); the table has been created. Www.2cto.com SQL> begin 2 for I in 1 .. 100000 loop 3 insert into xyc_test values (I, ''); 4 end loop; 5 commit; 6 end; 7/PL/SQL process completed successfully. SQL> exec dbms_stats.gather_table_stats (user, 'xyc _ test'); PL/SQL process completed successfully. 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 allocation size SQL> print: alloc_bytes 3145728 SQL> create index x_idx on xyc_test (id); index created. ---- The actual allocation size, which is quite different from the estimation here, may be related to the data volume. SQL> select bytes from user_segments where segment_name = 'x _ idx '; 2097152 or use the following method to facilitate [SQL] SQL> declare 2 v_ddl varchar (1024); 3 v_used_bytes number; 4 v_alloc_bytes number; 5 begin 6 dbms_space.create_index_cost (7 ddl => 'create index x_idx on xyc_test (id) ', used_bytes => v_used_bytes, alloc_bytes => v_alloc_bytes ); 8 dbms_output.put_line ('used _ bytes = '| v_used_bytes |' Bytes '| 'alloc_bytes =' | v_alloc_bytes | 'bytes '); 9 end; 10/used_bytes = 500000 bytes alloc_bytes = 3145728 bytes PL/SQL process completed successfully.