联合索引知多少？trouble shooting on 联合索引

这是一个实验总结帖，包括联合索引的实验 和总结。
我们是以邮件的形式发送和回复的 所以只能隐去真实姓名 请见谅。

请自底到顶阅读 。
=============================================================
我X ，这个帖子好，前一段时间还因为判断数据的问题伤神呢，算来算去都算不明白。

 

发件人: A(运维部\DBA\XXX)
发送时间: 2011年3月10日 17:43
收件人: B（开发部\XXX）; C(运维部\DBA\XXX); D(运维部\DBA\XXX); DBA(数据库管理员组)
主题: 答复: 组合索引 小实验

 

还原ORACLE DUMP 的值

 

 

还原DUMP出来的数字
SQL> select dump(2000,16) from dual;

DUMP(2000,16)
------------------
Typ=2 Len=2: c2,15


SQL> declare n number;
  2  begin
  3   dbms_stats.convert_raw_value('c215',n);
  4  dbms_output.put_line(n);
  5  end;
  6  /
2000


declare n number;
     begin
     dbms_stats.convert_raw_value('c215',n);
     dbms_output.put_line(n);
     end;
  /
还原DUMP的字符串

SQL> select dump('robinson',16) from dual;

DUMP('ROBINSON',16)
-------------------------------------
Typ=96 Len=8: 72,6f,62,69,6e,73,6f,6e

SQL> declare n varchar2(2000);
  2       begin
  3       dbms_stats.convert_raw_value('726f62696e736f6e',n);
  4       dbms_output.put_line(n);
  5       end;
  6     /
robinson

SQL> select dump('robinson') from dual;

DUMP('ROBINSON')
--------------------------------------------
Typ=96 Len=8: 114,111,98,105,110,115,111,110

其实这个时候DUMP出来的是以10进制显示的，也就是说114相对于72，也就是r,后面的字幕同样

SQL> select to_char(114,'xxx') from dual;

TO_C
----
  72

SQL> declare n varchar2(2000);
  2       begin
  3       dbms_stats.convert_raw_value('72',n);
  4       dbms_output.put_line(n);
  5       end;
  6     /

 

 

发件人: B（开发部\XXX）
发送时间: 2011年3月10日 17:40
收件人: A(运维部\DBA\XXX); C(运维部\DBA\XXX); D(运维部\DBA\XXX); DBA(数据库管理员组)
主题: 答复: 组合索引 小实验

 

其实这个吧就是leading column的问题，

由于后面的3列都是唯一的 所以ORACLE 认为 只需要取2列的column 就可以定位row id。

为了明确分清这一点 所以把N2 column 改为了varchar 这样在dump中就可以很清晰的看出。

create table test1(n1 number, n2 VARCHAR2(50), n3 number, n4 number)

create index t_i11 on test1(n1,n2,n3,n4)

create index t_i21 on test1(n1,n3,n4,n2)

 

所以 这是T1 的branch记录 其中并没有包含 n3 column 的数据

 

 

 

T1 INDEX

header address 46975231222860=0x2ab945f8644c

kdxcolev 1

kdxbr2urrc 0

row#0[7996] dba: 21264925=0x1447a1d

col 0; len 1; (1):  80

col 1; len 3; (3):  31 31 34                  这个是很明显的字符型数据

col 2; TERM

row#1[7985] dba: 21264926=0x1447a1e

col 0; len 1; (1):  80

col 1; len 3; (3):  31 32 38

col 2; TERM

row#2[7974] dba: 21264927=0x1447a1f

col 0; len 1; (1):  80

col 1; len 3; (3):  31 34 32

col 2; TERM

 

T2 INDEX

*** 2011-03-10 17:54:19.632

----- begin tree dump

branch: 0x10d2b7c 17640316 (0: nrow: 61, level: 1)

   leaf: 0x10d2b7f 17640319 (-1: nrow: 158 rrow: 158)

   leaf: 0x1447a1d 21264925 (0: nrow: 155 rrow: 155)

 

kdxbrlmc 17640327=0x10d2b87

kdxbrsno 31

kdxbrbksz 8056

kdxbr2urrc 0

row#0[8045] dba: 17640328=0x10d2b88

col 0; len 1; (1):  80

col 1; len 3; (3):  c2 04 22         这个则是很明显的number 数据。

col 2; TERM

row#1[8034] dba: 17640325=0x10d2b85

col 0; len 1; (1):  80

col 1; len 3; (3):  c2 07 36

col 2; TERM

row#2[8023] dba: 17640326=0x10d2b86

col 0; len 1; (1):  80

col 1; len 3; (3):  c2 0a 4a

col 2; TERM

发件人: A(运维部\DBA\XXX)
发送时间: 2011年3月10日 17:04
收件人: C(运维部\DBA\XXX); B（开发部\XXX）; D(运维部\DBA\XXX); DBA(数据库管理员组)
主题: 答复: 组合索引 小实验

 

因为索引时有序的，INDEX RANGE SCAN ，会从小到大一个一个进行扫描，

猜想t_i2只进行一次扫描就完成（从 root>leaf>datablock  3 consistent gets）。

而t_i1需进行多次扫描：

 

 

两个索引的level 都是1 ，但是

T_I2  tree dump:

 

----- begin tree dump

branch: 0x28aa13c 42639676 (0: nrow: 35, level: 1)

   leaf: 0x28aa13d 42639677 (-1: nrow: 299 rrow: 299)

   leaf: 0x28aa13e 42639678 (0: nrow: 287 rrow: 287)

   leaf: 0x28aa13f 42639679 (1: nrow: 287 rrow: 287)

   leaf: 0x28aa140 42639680 (2: nrow: 287 rrow: 287)

   leaf: 0x3416978 54618488 (3: nrow: 287 rrow: 287)

   leaf: 0x3416979 54618489 (4: nrow: 287 rrow: 287)

   leaf: 0x341697a 54618490 (5: nrow: 287 rrow: 287)

   leaf: 0x341697b 54618491 (6: nrow: 287 rrow: 287)

   leaf: 0x341697c 54618492 (7: nrow: 287 rrow: 287)

   leaf: 0x341697d 54618493 (8: nrow: 287 rrow: 287)

   leaf: 0x341697e 54618494 (9: nrow: 287 rrow: 287)

   leaf: 0x341697f 54618495 (10: nrow: 287 rrow: 287)

   leaf: 0x10d0f8a 17633162 (11: nrow: 287 rrow: 287)

   leaf: 0x10d0f8b 17633163 (12: nrow: 287 rrow: 287)

   leaf: 0x10d0f8c 17633164 (13: nrow: 287 rrow: 287)

 

*** 2011-03-10 16:44:38.865

   leaf: 0x10d0f8d 17633165 (14: nrow: 287 rrow: 287)

   leaf: 0x10d0f8e 17633166 (15: nrow: 287 rrow: 287)

   leaf: 0x10d0f8f 17633167 (16: nrow: 287 rrow: 287)

   leaf: 0x10d0f90 17633168 (17: nrow: 287 rrow: 287)

   leaf: 0x1447791 21264273 (18: nrow: 287 rrow: 287)

   leaf: 0x1447792 21264274 (19: nrow: 287 rrow: 287)

   leaf: 0x1447793 21264275 (20: nrow: 287 rrow: 287)

   leaf: 0x1447794 21264276 (21: nrow: 287 rrow: 287)

   leaf: 0x1447795 21264277 (22: nrow: 287 rrow: 287)

   leaf: 0x1447796 21264278 (23: nrow: 287 rrow: 287)

   leaf: 0x1447797 21264279 (24: nrow: 287 rrow: 287)

   leaf: 0x1447798 21264280 (25: nrow: 287 rrow: 287)

   leaf: 0x1849b5a 25467738 (26: nrow: 287 rrow: 287)

   leaf: 0x1849b5b 25467739 (27: nrow: 287 rrow: 287)

   leaf: 0x1849b5c 25467740 (28: nrow: 287 rrow: 287)

   leaf: 0x1849b5d 25467741 (29: nrow: 287 rrow: 287)

   leaf: 0x1849b5e 25467742 (30: nrow: 287 rrow: 287)

   leaf: 0x1849b5f 25467743 (31: nrow: 287 rrow: 287)

   leaf: 0x1849b60 25467744 (32: nrow: 287 rrow: 287)

   leaf: 0x28aa141 42639681 (33: nrow: 230 rrow: 230)

----- end tree dump

 

 

 

T_I1 tree dump :

 

*** 2011-03-10 16:44:41.786

----- begin tree dump

branch: 0x1847504 25457924 (0: nrow: 32, level: 1)

   leaf: 0x1847505 25457925 (-1: nrow: 332 rrow: 332)

   leaf: 0x1847506 25457926 (0: nrow: 320 rrow: 320)

   leaf: 0x1847507 25457927 (1: nrow: 320 rrow: 320)

   leaf: 0x1847508 25457928 (2: nrow: 320 rrow: 320)

   leaf: 0x28aa129 42639657 (3: nrow: 320 rrow: 320)

   leaf: 0x28aa12a 42639658 (4: nrow: 320 rrow: 320)

   leaf: 0x28aa12d 42639661 (5: nrow: 320 rrow: 320)

   leaf: 0x28aa12e 42639662 (6: nrow: 320 rrow: 320)

   leaf: 0x28aa12f 42639663 (7: nrow: 320 rrow: 320)

   leaf: 0x28aa130 42639664 (8: nrow: 320 rrow: 320)

   leaf: 0x28aa12b 42639659 (9: nrow: 320 rrow: 320)

   leaf: 0x28aa12c 42639660 (10: nrow: 320 rrow: 320)

   leaf: 0x341696c 54618476 (11: nrow: 320 rrow: 320)

   leaf: 0x341696d 54618477 (12: nrow: 320 rrow: 320)

   leaf: 0x341696e 54618478 (13: nrow: 320 rrow: 320)

   leaf: 0x341696f 54618479 (14: nrow: 320 rrow: 320)

   leaf: 0x3416969 54618473 (15: nrow: 320 rrow: 320)

   leaf: 0x341696a 54618474 (16: nrow: 320 rrow: 320)

   leaf: 0x341696b 54618475 (17: nrow: 320 rrow: 320)

   leaf: 0x104e9f1 17099249 (18: nrow: 320 rrow: 320)

   leaf: 0x104e9f2 17099250 (19: nrow: 320 rrow: 320)

   leaf: 0x104e9f5 17099253 (20: nrow: 320 rrow: 320)

   leaf: 0x104e9f6 17099254 (21: nrow: 320 rrow: 320)

   leaf: 0x104e9f7 17099255 (22: nrow: 320 rrow: 320)

   leaf: 0x104e9f8 17099256 (23: nrow: 320 rrow: 320)

   leaf: 0x104e9f3 17099251 (24: nrow: 320 rrow: 320)

   leaf: 0x104e9f4 17099252 (25: nrow: 320 rrow: 320)

   leaf: 0x1446f25 21262117 (26: nrow: 320 rrow: 320)

   leaf: 0x1446f26 21262118 (27: nrow: 320 rrow: 320)

   leaf: 0x1446f27 21262119 (28: nrow: 320 rrow: 320)

   leaf: 0x1446f28 21262120 (29: nrow: 320 rrow: 320)

   leaf: 0x1446f22 21262114 (30: nrow: 68 rrow: 68)

----- end tree dump

 

 

 

row#30[7716] dba: 25467742=0x1849b5e

col 0; len 1; (1):  80

col 1; len 3; (3):  c2 5a 0b

col 2; TERM

row#31[7705] dba: 25467743=0x1849b5f

col 0; len 1; (1):  80

col 1; len 3; (3):  c2 5c 62

col 2; TERM

row#32[7694] dba: 25467744=0x1849b60

col 0; len 1; (1):  80

col 1; len 3; (3):  c2 5f 55

col 2; TERM

row#33[7683] dba: 42639681=0x28aa141

col 0; len 1; (1):  80

col 1; len 3; (3):  c2 62 48

col 2; TERM

----- end of branch block dump -----

End dump data blocks tsn: 4 file#: 10 minblk 696633 maxblk 696636

 

 

Block header dump:  0x01847504

 Object id on Block? Y

 seg/obj: 0x13f7f  csc: 0x02.733b0d3f  itc: 1  flg: E  typ: 2 - INDEX

     brn: 0  bdba: 0x1847501 ver: 0x01 opc: 0

     inc: 0  exflg: 0

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x000b.021.0001b43b  0x00c28adc.116e.02  C---    0  scn 0x0002.733b0d3f

Branch block dump

=================

header address 47791955346508=0x2b776e85844c

kdxcolev 1

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y

kdxconco 5

kdxcosdc 1

kdxconro 31

kdxcofbo 90=0x5a

kdxcofeo 7715=0x1e23

kdxcoavs 7625

kdxbrlmc 25457925=0x1847505

kdxbrsno 30

kdxbrbksz 8056

kdxbr2urrc 0

row#0[8045] dba: 25457926=0x1847506

col 0; len 1; (1):  80

col 1; len 3; (3):  c2 04 22

col 2; TERM

row#1[8034] dba: 25457927=0x1847507

col 0; len 1; (1):  80

col 1; len 3; (3):  c2 07 36

col 2; TERM

row#2[8023] dba: 25457928=0x1847508

col 0; len 1; (1):  80

col 1; len 3; (3):  c2 0a 4a

col 2; TERM

row#3[8012] dba: 42639657=0x28aa129

col 0; len 1; (1):  80

col 1; len 3; (3):  c2 0d 5e

col 2; TERM

 

发件人: C(运维部\DBA\XXX)
发送时间: 2011年3月10日 16:25
收件人: B（开发部\XXX）; D(运维部\DBA\XX); DBA(数据库管理员组)
主题: 答复: 组合索引 小实验

 

Excellent test!


Thanks B

 

发件人: B（开发部\XXX）
发送时间: 2011年3月10日 16:15
收件人: D(运维部\DBA\XXX); DBA(XXX)
主题: 答复: 组合索引 小实验

 

JI guess column  n1,n2 on the branch block for index t_i1 ,and n1,n3 on the branch of index_t2 .Cuz  two columns can locate the rowid for this style(0,i,i,i).

 

does it？

 

发件人: D(运维部\DBA\XXX)
发送时间: 2011年3月10日 16:06
收件人: DBA(数据库管理员组)
主题: 组合索引 小实验

 

create table t(n1 number, n2 number, n3 number, n4 number);

create index t_i1 on t (n1,n2,n3,n4);

create index t_i2 on t (n1,n3,n4,n2);

Table has 10000 rows, row like ( 0, i ,i , i )   i = 1.. 10000

select * from t where n1=0 and n2 between 1 and 10000 and n3=1 and n4=1;

Use index t_i1              

                    -- 37 buffer gets

Use index t_i2              

                    -- 3 buffer gets

 

 

验证小实验

 

SQL> create table test(n1 number, n2 number, n3 number, n4 number);

 

Table created

 

SQL> create index t_i1 on test(n1,n2,n3,n4);

 

Index created

 

SQL> create index t_i2 on test (n1,n3,n4,n2);

 

Index created.

 

SQL> declare

  2    v_cnt number := 0;

  3  begin

  4    for i in 1..10000 loop

  5      insert into test values(0,i,i,i);

  6      v_cnt := v_cnt + 1;

  7      if mod(v_cnt,2000) = 0 then

  8        commit;

  9      end if;

 10    end loop;

 11    commit;

 12  end;

 13  /

 

 

PL/SQL procedure successfully completed

 

实验结论： 与ppt描述的十分吻合，而采用t_i1索引的cost比全表扫描还要高些

 

使用索引t_i1的执行代价

SQL> select /*+ index(test,t_i1) */* from test where n1=0 and n2 between 1 and 10000 and n3=1 and n4=1;

----------------------------------------------------------

          0  recursive calls

          0  db block gets

         34  consistent gets

          0  physical reads

          0  redo size

        720  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

1   rows processed

 

全表扫描的代价

SQL> select /*+ full(test) */* from test where n1=0 and n2 between 1 and 10000 and n3=1 and n4=1;

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    52 |     9   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST |     1 |    52 |     9   (0)| 00:00:01 |

--------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

         32  consistent gets

          0  physical reads

          0  redo size

        720  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

1   rows processed

 

使用索引t_i2的代价

SQL> select * from test where n1=0 and n2 between 1 and 10000 and n3=1 and n4=1;

-------------------------------------------------------------------------

| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------

|   0 | SELECT STATEMENT |      |     1 |    52 |     2   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| T_I2 |     1 |    52 |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

        720  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)
1   rows processed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21818314/viewspace-693214/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21818314/viewspace-693214/