ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120509]IOT索引组织表相关信息的学习(四).txt

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

原创 Linux操作系统 作者:lfree 时间:2012-05-09 15:35:48 0 删除 编辑
[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字段没有重复保存。

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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2282
  • 访问量
    6021326