估算创建索引的空间大小

首先要保障统计信息准确

收集表的信息：

set timing on
begin
dbms_stats.gather_table_stats(
  ownname =>'&OWN',
  tabname => '&TAB',
  no_invalidate => false,
  degree=>16,
  method_opt=>'for all columns size auto',
  cascade => true
);
end;
/


估算索引大小：
set serveroutput on
declare
 l_index_ddl varchar2(1000);
 l_used_bytes number;
 l_allocated_bytes number;
begin
 dbms_space.create_index_cost (
 ddl =>'create index idx_t on dbsnmp.T1(NAME) ', --执行的ddl，根据需要修改
 used_bytes => l_used_bytes,
 alloc_bytes => l_allocated_bytes );
 dbms_output.put_line ('used= ' || round(l_used_bytes/1024/1024,1) || 'Mbytes'|| '  allocated= ' || round(l_allocated_bytes/1024/1024,1) || 'Mbytes');
end;
/

最终大小参考 allocated 的值


或者

explain plan for create index idx_t on dbsnmp.t1 (name) ;
select * from table(dbms_xplan.display());

很准确。