oracle 估算一個索引所需要的空間

darren__chan發表於2015-11-25
Oracle索引
在建立一個索引之前，可以透過DBMS_SPACE.CREATE_INDEX_COST儲存過程來估算所需的空間,但僅僅是估算。                   

This procedure determines the cost of creating an index on an existing table. The input is the DDL statement that will be used to create the index. The procedure will output the storage required to create the index.
 

DBMS_SPACE.CREATE_INDEX_COST (
   ddl             IN    VARCHAR2,
   used_bytes      OUT   NUMBER,
   alloc_bytes     OUT   NUMBER,
   plan_table      IN    VARCHAR2 DEFAULT NULL);
Parameter Description

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



  1. SQL> exec dbms_stats.gather_table_stats('HR','T1');

  3. PL/SQL procedure successfully completed.

  5. SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1' and owner='HR';

  7. SUM(BYTES)/1024/1024
  8. --------------------
  9.                   72

  11. SQL> select a.tablespace_name,total,free,total-free used from
  12.   2 ( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
  13.   3 group by tablespace_name) a,
  14.   4 ( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
  15.   5 group by tablespace_name) b
  16.   6 where a.tablespace_name=b.tablespace_name and a.tablespace_name='USERS';

  18. TABLESPACE_NAME TOTAL FREE USED
  19. ------------------------------ ---------- ---------- ----------
  20. USERS 483.75 380.0625 103.6875

  1. SQL> declare
  2.   2 v1 number;
  3.   3 v2 number;
  4.   4 begin
  5.   5 DBMS_SPACE.CREATE_INDEX_COST('create index hr.idx_t1_jobid on hr.t1(job_id)',v1,v2);
  6.   6 dbms_output.put_line('create the index need actually use: '||v1/1024/1024||' MB ,and allocalte: '||v2/1024/1024||' MB in the tablespace!');
  7.   7 end;
  8.   8 /
  9. create the index need actually use: 5.9375 MB ,and allocalte: 18 MB in the
  10. tablespace!

  12. PL/SQL procedure successfully completed.


  1. SQL> create index hr.idx_t1_jobid on hr.t1(job_id);

  3. Index created.

  5. SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_JOBID';

  7. SUM(BYTES)/1024/1024
  8. --------------------
  9.                   17

  11. SQL> select a.tablespace_name,total,free,total-free used from
  12.   2 ( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
  13.   3 group by tablespace_name) a,
  14.   4 ( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
  15.   5 group by tablespace_name) b
  16.   6 where a.tablespace_name=b.tablespace_name and a.tablespace_name='USERS';

  18. TABLESPACE_NAME TOTAL FREE USED
  19. ------------------------------ ---------- ---------- ----------
  20. USERS 483.75 363.0625 120.6875




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29863023/viewspace-1846376/，如需轉載，請註明出處，否則將追究法律責任。

相關文章