Jul 24

oracle创建index之前如何确定其大小(收藏于killdb) 不指定

admin , 22:50 , 数据库 » 数据库(Oracle管理) , 评论(0) , 引用(0) , 阅读(1233) , Via 本站原创 | |
搜索
我已经获得阿里云幸运券,准备分享给您。请点击获取  
该问题也源于某位网友的提问,其实这个问题也非常的现实,有用,具体的问题是:  我们在为某个表创建索引之前,如何估算已将创建的索引需要占据多少空间?下面看我的测试. 
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,那么即将创建的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 #map /v 是一个bbed的命令,通过这命令可以清楚的看到index leaf block的结构。   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
Tags: ,