评估创建索引所需要的空间大小

原创

jsj_007 2021-08-10 17:04:30 博主文章分类：ORACLE ©著作权

©著作权归作者所有：来自51CTO博客作者jsj_007的原创作品，请联系作者获取转载授权，否则将追究法律责任

创建测试数据:

create table cust(
 cust_id number
,last_name varchar2(30)
,first_name varchar2(30));

insert into cust
select level + 1
  ,dbms_random.string('U',dbms_random.value(3,15)) rand_last_name
  ,dbms_random.string('U',dbms_random.value(3,15)) rand_first_name
from dual
connect by level <= 100000;

估算

SQL> set serverout on
exec dbms_stats.gather_table_stats(user,'CUST');
variable used_bytes number
variable alloc_bytes number
exec dbms_space.create_index_cost( 'create index cust_idx2 on cust(first_name)', -
               :used_bytes, :alloc_bytes );
print :used_bytes
print :alloc_bytesSQL>
PL/SQL procedure successfully completed.

SQL> SQL> SQL> >
PL/SQL procedure successfully completed.

SQL>
USED_BYTES
----------
   1000000

SQL>

ALLOC_BYTES
-----------
    3145728   <<<创建索引需要分配的空间大小

实际使用：

SQL> create index cust_idx2 on cust(first_name);

Index created.

SQL> select segment_name,bytes,segment_type from dba_segments where owner='SCOTT' and segment_name='CUST_IDX2';

SEGMENT_NAME                   BYTES SEGMENT_TYPE
------------------------- ---------- ------------------
CUST_IDX2                    3145728 INDEX