[20120509]IOT索引组织表相关信息的学习(四).txt

今天看了一个有关IOT的介绍：
http://richardfoote.wordpress.com/2012/04/11/iot-secondary-indexes-primary-key-considerations-beauty-and-the-beast/

    If we create a secondary index on a column that forms part of the PK, Oracle can be a lit bit cleverer. Following, we create an
index on the COUNTRY_ID column, which is the second column of our PK (album_id, country_id):

SQL> create index album_sales_iot_country_id_i on album_sales_iot(country_id);
Index created.

    We notice that for this new index, Oracle has eliminated "redundant" PK columns from the secondary index, as there's no need to
store the entire PK again as the indexed column itself already forms part of the PK:

SQL> select index_name, iot_redundant_pkey_elim from dba_indexes where table_name = 'ALBUM_SALES_IOT';

INDEX_NAME                     IOT_REDUNDANT_PKEY_ELIM
------------------------------ ------------------------
ALBUM_SALES_IOT_PK             NO
ALBUM_SALES_IOT_TOTAL_SALES_I  NO
ALBUM_SALES_IOT_COUNTRY_ID_I   YES

    上面提到如果建立的第2索引中包含主键的一部分，不会在第2索引的进行冗余保存。自己做一个测试看看。

1.测试环境:

SQL> select * from v$version ;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> create table t_iot ( a varchar2(10),b varchar2(10),c varchar2(10),vc varchar2(1200), constraint t_iot_pk PRIMARY KEY(a,b)) ORGANIZATION INDEX;

SQL> create index i_t_iot_b_c on t_iot(b,c);

SQL> create index i_t_iot_c_b on t_iot(c,b);

insert into t_iot values ('1'        ,'a','s1','a');

insert into t_iot values ('22'       ,'b','s2','a');

insert into t_iot values ('333'      ,'c','s3','a');

insert into t_iot values ('4444'     ,'d','s4','a');

insert into t_iot values ('55555'    ,'e','s5','a');

insert into t_iot values ('666666'   ,'f','s6','a');

insert into t_iot values ('7777777'  ,'g','s7','a');

insert into t_iot values ('88888888' ,'h','s8','a');

insert into t_iot values ('999999999','i','s9','a');

commit ;

SQL> exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'T_iot');

2.检查建立索引:

SQL> select index_name, iot_redundant_pkey_elim from dba_indexes where table_name = 'T_IOT';

INDEX_NAME                     IOT

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

T_IOT_PK                       NO

I_T_IOT_B_C                    YES

I_T_IOT_C_B                    YES

--发现确实如此!

SQL> select column_id, segment_column_id, column_name from dba_tab_cols where table_name = 'T_IOT' order by column_id;

 COLUMN_ID SEGMENT_COLUMN_ID COLUMN_NAME

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

         1                 1 A

         2                 2 B

         3                 3 C

         4                 4 VC

3.转储第2索引的信息:

SQL> SELECT index_name, table_name, blevel, leaf_blocks,iot_redundant_pkey_elim FROM dba_indexes  WHERE table_name = 'T_IOT';

INDEX_NAME                     TABLE_NAME                         BLEVEL LEAF_BLOCKS IOT

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

T_IOT_PK                       T_IOT                                   0           1 NO

I_T_IOT_B_C                    T_IOT                                   0           1 YES

I_T_IOT_C_B                    T_IOT                                   0           1 YES

--可以发现索引很小，blevel=0,leaf_blocks=1.

SQL> select header_file,header_block from dba_segments where segment_name='I_T_IOT_B_C';

HEADER_FILE HEADER_BLOCK

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

          4         2570

SQL> select object_id,data_object_id from dba_objects where object_name='I_T_IOT_B_C';

 OBJECT_ID DATA_OBJECT_ID

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

     91490          91490

SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 91490';

----- begin tree dump

leaf: 0x1000a0b 16779787 (0: nrow: 9 rrow: 9)

----- end tree dump

仅仅占用1个块。HEADER_BLOCK=2570,根节点=2571.

SQL> alter system dump datafile 4 block 2571 ;

Block header dump:  0x01000a0b

 Object id on Block? Y

 seg/obj: 0x16562  csc: 0x00.9dc409  itc: 2  flg: E  typ: 2 - INDEX

     brn: 0  bdba: 0x1000a08 ver: 0x01 opc: 0

     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

0x02   0x0009.010.0000137f  0x00c0110b.09dd.43  --U-    9  fsc 0x0000.009dc423

Leaf block dump

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

header address 182924563044=0x2a97275264

kdxcolev 0

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x90: pcode=0: iot flags=I-- is converted=Y

kdxconco 3

kdxcosdc 0

kdxconro 9

kdxcofbo 54=0x36

kdxcofeo 7843=0x1ea3

kdxcoavs 7789

kdxlespl 0

kdxlende 0

kdxlenxt 0=0x0

kdxleprv 0=0x0

kdxledsz 0

kdxlebksz 8032

row#0[8015] flag: K-----, lock: 2, len=17

col 0; len 1; (1):  61

col 1; len 2; (2):  73 31

col 2; len 1; (1):  31

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]  01 00 0a 03

row#1[7997] flag: K-----, lock: 2, len=18

col 0; len 1; (1):  62

col 1; len 2; (2):  73 32

col 2; len 2; (2):  32 32

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]  01 00 0a 03

row#2[7978] flag: K-----, lock: 2, len=19

col 0; len 1; (1):  63

col 1; len 2; (2):  73 33

col 2; len 3; (3):  33 33 33

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]  01 00 0a 03

row#3[7958] flag: K-----, lock: 2, len=20

col 0; len 1; (1):  64

col 1; len 2; (2):  73 34

col 2; len 4; (4):  34 34 34 34

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]  01 00 0a 03

row#4[7937] flag: K-----, lock: 2, len=21

col 0; len 1; (1):  65

col 1; len 2; (2):  73 35

col 2; len 5; (5):  35 35 35 35 35

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]  01 00 0a 03

row#5[7915] flag: K-----, lock: 2, len=22

col 0; len 1; (1):  66

col 1; len 2; (2):  73 36

col 2; len 6; (6):  36 36 36 36 36 36

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]  01 00 0a 03

row#6[7892] flag: K-----, lock: 2, len=23

col 0; len 1; (1):  67

col 1; len 2; (2):  73 37

col 2; len 7; (7):  37 37 37 37 37 37 37

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]  01 00 0a 03

row#7[7868] flag: K-----, lock: 2, len=24

col 0; len 1; (1):  68

col 1; len 2; (2):  73 38

col 2; len 8; (8):  38 38 38 38 38 38 38 38

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]  01 00 0a 03

row#8[7843] flag: K-----, lock: 2, len=25

col 0; len 1; (1):  69

col 1; len 2; (2):  73 39

col 2; len 9; (9):  39 39 39 39 39 39 39 39 39

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]  01 00 0a 03

----- end of leaf block dump -----

End dump data blocks tsn: 4 file#: 4 minblk 2571 maxblk 2571

-- 可以看出col1对应字段b的值，col2对应字段c的值,col2对应字段a的值。
--确实b字段没有重复保存。