[20120509]IOT索引組織表相關資訊的學習(四).txt

lfree發表於2012-05-09
原文網址 : http://blog.itpub.net/267265/viewspace-723140/
索引
[20120509]IOT索引組織表相關資訊的學習(四).txt

今天看了一個有關IOT的介紹：


    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欄位沒有重複儲存。

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

相關文章