[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字段没有重复保存。
时间： 2024-08-03 08:54:03