sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle数据库创建index索引前统计出索引大小

时间:2016-11-29 22:42   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库index索引的文章,该文章详细介绍Oracle数据库创建index索引前统计出索引大小的方法。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 创建index之前如何确定其大小

我们在为某个表创建索引之前,如何估算已将创建的索引需要占据多少空间?

下面看我的测试.

SQL> CREATE TABLE t AS SELECT * FROM dba_objects;

TABLE created.

SQL>  BEGIN
  2     FOR i IN 1..1000 loop
  3       INSERT /*+ append */INTO t SELECT * FROM t;
  4       commit;
  5     END loop;
  6   END;
  7   /
BEGIN
*
ERROR at line 1:
ORA-01013: USER requested cancel OF CURRENT operation
ORA-06512: at line 3

SQL>  BEGIN
  2     FOR i IN 1..1000 loop
  3       INSERT /*+ append */INTO t SELECT * FROM t;
  4       commit;
  5     END loop;
  6   END;
  7   /
BEGIN
*
ERROR at line 1:
ORA-01013: USER requested cancel OF CURRENT operation
ORA-06512: at line 3

SQL> SELECT COUNT(*) FROM t;

  COUNT(*)
----------
   1629312

SQL> analyze TABLE t compute statistics;

TABLE analyzed.

SQL> SELECT bytes/1024/1024 FROM dba_segments WHERE segment_name='T';

BYTES/1024/1024
---------------
            272

SQL> CREATE INDEX t_idx_id ON t(object_id);

INDEX created.

SQL> analyze INDEX t_idx_id compute statistics;

INDEX analyzed.

SQL> SELECT bytes/1024/1024 FROM dba_segments WHERE segment_name='T_IDX_ID';

BYTES/1024/1024
---------------
             29


SQL> SELECT file_id,segment_name,EXTENT_ID,BLOCK_ID,BYTES/1024/1024
  2    FROM dba_extents
  3   WHERE segment_name='T_IDX_ID';

   FILE_ID SEGMENT_NAME          EXTENT_ID   BLOCK_ID BYTES/1024/1024
---------- -------------------- ---------- ---------- ---------------
         5 T_IDX_ID                      1        457           .0625
         5 T_IDX_ID                      3        465           .0625
         5 T_IDX_ID                      5        473           .0625
         5 T_IDX_ID                      7        489           .0625
         5 T_IDX_ID                      9        497           .0625
         5 T_IDX_ID                     11        505           .0625
         5 T_IDX_ID                     13        513           .0625
         5 T_IDX_ID                     15       1161           .0625
         5 T_IDX_ID                     17      14473               1
         5 T_IDX_ID                     19      14601               1
         5 T_IDX_ID                     21      14729               1
         5 T_IDX_ID                     23      27017               1
         5 T_IDX_ID                     25      27145               1
         5 T_IDX_ID                     27      27273               1
         5 T_IDX_ID                     29      27401               1
         5 T_IDX_ID                     31      27529               1
         5 T_IDX_ID                     33      28425               1
         5 T_IDX_ID                     35      28553               1
         5 T_IDX_ID                     37      28681               1
         5 T_IDX_ID                     39      33033               1
         5 T_IDX_ID                     41      33161               1
         5 T_IDX_ID                     43      33289               1
         6 T_IDX_ID                      0        593           .0625
         6 T_IDX_ID                      2        601           .0625
         6 T_IDX_ID                      4        609           .0625
         6 T_IDX_ID                      6        617           .0625
         6 T_IDX_ID                      8        625           .0625
         6 T_IDX_ID                     10        633           .0625
         6 T_IDX_ID                     12        641           .0625
         6 T_IDX_ID                     14       9609           .0625
         6 T_IDX_ID                     16       9353               1
         6 T_IDX_ID                     18       9481               1
         6 T_IDX_ID                     20      29705               1
         6 T_IDX_ID                     22      29833               1
         6 T_IDX_ID                     24      30729               1
         6 T_IDX_ID                     26      30857               1
         6 T_IDX_ID                     28      30985               1
         6 T_IDX_ID                     30      31113               1
         6 T_IDX_ID                     32      33417               1
         6 T_IDX_ID                     34      33545               1
         6 T_IDX_ID                     36      33673               1
         6 T_IDX_ID                     38      33801               1
         6 T_IDX_ID                     40      33929               1
         6 T_IDX_ID                     42      34057               1

44 ROWS selected.

++++++ index block 结构 ++++++

BBED> set file 5 block 457

        FILE#           5
        BLOCK#          457

BBED> map /v

File: /home/ora10g/oradata/roger/roger01.dbf (5)
Block: 457                                   Dba:0x014001c9
------------------------------------------------------------
KTB Data Block (Index Leaf)

struct kcbh, 20 bytes                      @0      
    ub1 type_kcbh                           @0      
    ub1 frmt_kcbh                           @1      
    ub1 spare1_kcbh                         @2      
    ub1 spare2_kcbh                         @3      
    ub4 rdba_kcbh                           @4      
    ub4 bas_kcbh                            @8      
    ub2 wrp_kcbh                            @12     
    ub1 seq_kcbh                            @14     
    ub1 flg_kcbh                            @15     
    ub2 chkval_kcbh                         @16     
    ub2 spare3_kcbh                         @18     

struct ktbbh, 72 bytes                     @20     
    ub1 ktbbhtyp                            @20     
    union ktbbhsid, 4 bytes                 @24     
    struct ktbbhcsc, 8 bytes                @28     
    b2 ktbbhict                             @36     
    ub1 ktbbhflg                            @38     
    ub1 ktbbhfsl                            @39     
    ub4 ktbbhfnx                            @40     
    struct ktbbhitl[2], 48 bytes            @44     

struct kdxle, 32 bytes                     @100    
    struct kdxlexco, 16 bytes               @100    
    b2 kdxlespl                             @116    
    sb2 kdxlende                            @118    
    ub4 kdxlenxt                            @120    
    ub4 kdxleprv                            @124    
    ub1 kdxledsz                            @128    
    ub1 kdxleunuse                          @129    

b2 kd_off[512]                             @132    

ub1 freespace[828]                         @1156   

ub1 rowdata[6144]                          @1984   

ub4 tailchk                                @8188
SQL> SELECT TABLE_NAME,column_name,AVG_COL_LEN
  2    FROM user_tab_columns
  3   WHERE TABLE_NAME='T';

TABLE_NAME COLUMN_NAME                    AVG_COL_LEN
---------- ------------------------------ -----------
T          OWNER                                    5
T          OBJECT_NAME                             24
T          SUBOBJECT_NAME                           2
T          OBJECT_ID                                4
T          DATA_OBJECT_ID                           2
T          OBJECT_TYPE                              8
T          CREATED                                  7
T          LAST_DDL_TIME                            7
T          TIMESTAMP                               19
T          STATUS                                   5
T          TEMPORARY                                1
T          GENERATED                                1
T          SECONDARY                                1

13 ROWS selected.

SQL> SELECT dbms_metadata.get_ddl('TABLE','T','ROGER') FROM dual;

DBMS_METADATA.GET_DDL('TABLE','T','ROGER')
--------------------------------------------------------------------------------

  CREATE TABLE "ROGER"."T"
   (    "OWNER" VARCHAR2(30),
        "OBJECT_NAME" VARCHAR2(128),
        "SUBOBJECT_NAME" VARCHAR2(30),
        "OBJECT_ID" NUMBER,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(19),
        "CREATED" DATE,
        "LAST_DDL_TIME" DATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "ROGER"

从bbed的输出信息我们可以清楚的了解到index block的结构,这里我们并不需要了解每个机构的具体含义,有个整理的轮廓就行了,我们可以这样想:

index block也是数据块,假如我们把10w条的信息存到索引block中,要想知道索引的大小,那么我们就要知道一共占据了多少个index block?再深入一点,那就是如果我们知道了每个index block所能存放的数据条数不就行了吗?

总数据条数 / 每个index block所容纳的数据条数 = index block总数

这里我们开始进行计算:

假如 每个index block最大能容纳Y条:


block size(8192) = kcbh + ktbbh + kdxle + kd_off + DATA + block_size * Pctfree + 4
                 = 20 + 72 + 32 + 2 * Y + Y * ( 4 + rowid ) + 8192 * 0.1 + 4
            8192 = 92 + 32 + 16y + 819.2 + 4

SQL> SELECT (8192-819.2-92-32-4)/16 FROM dual;

(8192-819.2-92-32-4)/16
-----------------------
                  452.8

这里计算出来的结果是,如果我们在object_id列上创建index的话,单个index block能容纳453.05条信息。

那么我们需要多少个 index block 呢? 很简单,如下:
SQL> SELECT (1629312/452.8) FROM dual;

(1629312/452.8)
---------------
     3598.30389

到这里,我们计算即将创建的index的大小是:
SQL> SELECT (1629312/452.8)*8192/1024/1024 FROM dual;

(1629312/452.8)*8192/1024/1024
------------------------------
                    28.1117491

从上面的计算结果来看,应该是29m大小。跟我们前面的查询结果完全一致。

最后还有个问题,我们这里是针对单列index而言,如果是复合index呢?
SQL> SELECT TABLE_NAME,column_name,AVG_COL_LEN
  2    FROM user_tab_columns
  3   WHERE TABLE_NAME='T';

TABLE_NAME COLUMN_NAME                    AVG_COL_LEN
---------- ------------------------------ -----------
T          OWNER                                    5
T          OBJECT_NAME                             24
T          SUBOBJECT_NAME                           2
T          OBJECT_ID                                4
T          DATA_OBJECT_ID                           2
T          OBJECT_TYPE                              8
T          CREATED                                  7
T          LAST_DDL_TIME                            7
T          TIMESTAMP                               19
T          STATUS                                   5
T          TEMPORARY                                1
T          GENERATED                                1
T          SECONDARY                                1

假如我们要给(owner,object_id) 创建一个复合index,那么即将创建http://www.oracleplus.net的index的大小是多少呢?

这里我假设每个index block此时最大能容纳Y条信息:

对于8k的block:

block_size(8192)= = kcbh + ktbbh + kdxle + kd_off + DATA + block_size * Pctfree + 4
                  = 20 + 72 + 32 + 2 * Y + Y * ( 4 + 5 + rowid ) + 8192 * 0.1 + 4
             8192 = 92 + 32 + 21Y + 819.2 + 4

SQL> SELECT (8192-92-32-819.2-4)/21 FROM dual;

(8192-92-32-819.2-4)/21
-----------------------
             344.990476
此时 Y = 344.990476

那么新创建的复合index为多大呢?
SQL> SELECT 1629312/((8192-92-32-819.2-4)/21) *8192 /1024/1024 FROM dual;

1629312/((8192-92-32-819.2-4)/21)*8192/1024/1024
------------------------------------------------
                                      36.8966707
大概估算为37M左右。

我们来看看实际情况如何:
SQL> conn roger/roger
Connected.

SQL> CREATE INDEX t_idx_owner_id ON t(owner,object_id);

INDEX created.

SQL> analyze INDEX t_idx_owner_id compute statistics;

INDEX analyzed.

SQL> SELECT bytes/1024/1024 FROM dba_segments WHERE segment_name='T_IDX_OWNER_ID';

BYTES/1024/1024
---------------
             39

这里需要说明一下的是,我这里是以leaf block为计算,因为还涉及到branch block,其结构是不同的,所以最终的结果有微小的差异,不过我想这已经实现我们的目的了吧!

下面分别是index branch block 和 leaf block的结构:

BBED> map /v

File: /home/ora10g/oradata/roger/roger02.dbf (6)
Block: 28671                                 Dba:0x01806fff
------------------------------------------------------------
KTB Data Block (Index Leaf)

struct kcbh, 20 bytes                      @0      
    ub1 type_kcbh                           @0      
    ub1 frmt_kcbh                           @1      
    ub1 spare1_kcbh                         @2      
    ub1 spare2_kcbh                         @3      
    ub4 rdba_kcbh                           @4      
    ub4 bas_kcbh                            @8      
    ub2 wrp_kcbh                            @12     
    ub1 seq_kcbh                            @14     
    ub1 flg_kcbh                            @15     
    ub2 chkval_kcbh                         @16     
    ub2 spare3_kcbh                         @18     

struct ktbbh, 72 bytes                     @20     
    ub1 ktbbhtyp                            @20     
    union ktbbhsid, 4 bytes                 @24     
    struct ktbbhcsc, 8 bytes                @28     
    b2 ktbbhict                             @36     
    ub1 ktbbhflg                            @38     
    ub1 ktbbhfsl                            @39     
    ub4 ktbbhfnx                            @40     
    struct ktbbhitl[2], 48 bytes            @44     

struct kdxle, 32 bytes                     @100    
    struct kdxlexco, 16 bytes               @100    
    b2 kdxlespl                             @116    
    sb2 kdxlende                            @118    
    ub4 kdxlenxt                            @120    
    ub4 kdxleprv                            @124    
    ub1 kdxledsz                            @128    
    ub1 kdxleunuse                          @129    

b2 kd_off[336]                             @132    

ub1 freespace[836]                         @804    

ub1 rowdata[6488]                          @1640   

ub4 tailchk                                @8188   


BBED> set file 6 block 34700

        FILE#           6
        BLOCK#          34700

BBED> map /v

File: /home/ora10g/oradata/roger/roger02.dbf (6)
Block: 34700                                 Dba:0x0180878c
------------------------------------------------------------
KTB Data Block (Index Branch)

struct kcbh, 20 bytes                      @0      
    ub1 type_kcbh                           @0      
    ub1 frmt_kcbh                           @1      
    ub1 spare1_kcbh                         @2      
    ub1 spare2_kcbh                         @3      
    ub4 rdba_kcbh                           @4      
    ub4 bas_kcbh                            @8      
    ub2 wrp_kcbh                            @12     
    ub1 seq_kcbh                            @14     
    ub1 flg_kcbh                            @15     
    ub2 chkval_kcbh                         @16     
    ub2 spare3_kcbh                         @18     

struct ktbbh, 48 bytes                     @20     
    ub1 ktbbhtyp                            @20     
    union ktbbhsid, 4 bytes                 @24     
    struct ktbbhcsc, 8 bytes                @28     
    b2 ktbbhict                             @36     
    ub1 ktbbhflg                            @38     
    ub1 ktbbhfsl                            @39     
    ub4 ktbbhfnx                            @40     
    struct ktbbhitl[1], 24 bytes            @44     

struct kdxbr, 24 bytes                     @76     
    struct kdxbrxco, 16 bytes               @76     
    ub4 kdxbrlmc                            @92     
    sb2 kdxbrsno                            @96     

b2 kd_off[368]                             @100    

ub1 freespace[15]                          @836    

ub1 rowdata[7277]                          @851    

ub4 tailchk                                @8188

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle数据库创建index索引前统计出索引大小

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1341.html

Oracle研究中心

关键词:

创建index之前如何确定其大小

统计Oracle index大小的方法

如何计算出index索引占用空间