[20121028]IOT的第2索引-NULL的问题.txt
IOT表实际上时索引结构，如果第2索引的键值为NULL，会是什么情况呢？
因为第2索引包含主键，而主键是不能为NULL的，这样即使第2索引的键值为NULL，会包括在第2索引中吗?
自己做一些测试验证看看：
1.测试环境:
SQL> select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> create table t_iot ( a varchar2(10),b varchar2(10),vc varchar2(1200), constraint t_iot_pk PRIMARY KEY(a)) ORGANIZATION INDEX;
SQL> create index i_t_iot_b on t_iot(b);
insert into t_iot values ('1' ,'a','a');
insert into t_iot values ('22' ,'b','a');
insert into t_iot values ('333' ,'c','a');
insert into t_iot values ('4444' ,'d','a');
insert into t_iot values ('55555' ,'e','a');
insert into t_iot values ('666666' ,'f','a');
insert into t_iot values ('7777777' ,'g','a');
insert into t_iot values ('88888888' ,'h','a');
commit ;
SQL> exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'T_iot');
2.看看主键为NULL,可以插入吗？
insert into t_iot values (NULL,'j','a');
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T_IOT"."A")
SQL> insert into t_iot values ('999999999',NULL,'b');
1 row created.
SQL> commit ;
Commit complete.
3.查询测试：
SQL> select /*+ index(t1,i_t_iot_b) */ a,b,substr(vc,1,40) from t_iot where b is null;
A B SUBSTR(VC,1,40)
---------- ---------- ---------------
999999999 b
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6wzax9paxk4ag, child number 0
-------------------------------------
select /*+ index(t1,i_t_iot_b) */ a,b,substr(vc,1,40) from t_iot where
b is null
Plan hash value: 2901191065
-----------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)|
|* 1 | INDEX FULL SCAN | T_IOT_PK | 1 | 1 (0)|
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B" IS NULL)
--可以发现并不使用第2索引i_t_iot_b.
SQL> select /*+ index(t1,i_t_iot_b) */ a,b,substr(vc,1,40) from t_iot where b='a';
A B SUBSTR(VC,1,40)
---------- ---------- ---------------
1 a a
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9631w0zracpn8, child number 0
-------------------------------------
select /*+ index(t1,i_t_iot_b) */ a,b,substr(vc,1,40) from t_iot where
b='a'
Plan hash value: 1095339046
-------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)|
|* 1 | INDEX UNIQUE SCAN| T_IOT_PK | 1 | 1 (0)|
|* 2 | INDEX RANGE SCAN| I_T_IOT_B | 1 | 1 (0)|
-------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"='a')
2 - access("B"='a')
4.转储第2索引 i_t_iot_b：
SQL> select header_file,header_block from dba_segments where segment_name='I_T_IOT_B';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 522
SQL> select object_id,data_object_id from dba_objects where object_name='I_T_IOT_B';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
117537 117537
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 117537';
----- begin tree dump
leaf: 0x100020b 16777739 (0: nrow: 8 rrow: 8)
----- end tree dump
仅仅占用1个块。HEADER_BLOCK=533,根节点=523.
SQL> alter system dump datafile 4 block 523 ;
Block header dump: 0x0100020b
Object id on Block? Y
seg/obj: 0x1cb21 csc: 0x00.b3748a14 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000208 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 0x0007.00c.00002319 0x00c041b1.1296.50 --U- 8 fsc 0x0000.b3748a1e
Leaf block dump
===============
header address 182924685412=0x2a97293064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x90: pcode=0: iot flags=I-- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 8
kdxcofbo 52=0x34
kdxcofeo 7892=0x1ed4
kdxcoavs 7840
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8018] flag: K-----, lock: 2, len=14
col 0; len 1; (1): 61
col 1; len 1; (1): 31
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 00 a3
row#1[8003] flag: K-----, lock: 2, len=15
col 0; len 1; (1): 62
col 1; len 2; (2): 32 32
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 00 a3
row#2[7987] flag: K-----, lock: 2, len=16
col 0; len 1; (1): 63
col 1; len 3; (3): 33 33 33
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 00 a3
row#3[7970] flag: K-----, lock: 2, len=17
col 0; len 1; (1): 64
col 1; len 4; (4): 34 34 34 34
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 00 a3
row#4[7952] flag: K-----, lock: 2, len=18
col 0; len 1; (1): 65
col 1; len 5; (5): 35 35 35 35 35
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 00 a3
row#5[7933] flag: K-----, lock: 2, len=19
col 0; len 1; (1): 66
col 1; len 6; (6): 36 36 36 36 36 36
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 00 a3
row#6[7913] flag: K-----, lock: 2, len=20
col 0; len 1; (1): 67
col 1; len 7; (7): 37 37 37 37 37 37 37
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 00 a3
row#7[7892] flag: K-----, lock: 2, len=21
col 0; len 1; (1): 68
col 1; 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 00 a3
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 523 maxblk 523
--可以发现并没有NULL.这点有点奇怪,oracle的第2索引包含主键,而主键是非空的.
5.建立函数索引看看.
SQL> create index if_t_iot_b on t_iot(b,0);
Index created.
SQL> select header_file,header_block from dba_segments where segment_name='IF_T_IOT_B';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 530
SQL> alter system dump datafile 4 block 531 ;
System altered.
Block header dump: 0x01000213
Object id on Block? Y
seg/obj: 0x1cb1b csc: 0x00.b374789f itc: 3 flg: E typ: 1 - DATA
"/u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_10830.trc" 1276L, 65346C 551,1 42%
kdxconro 9
kdxcofbo 54=0x36
kdxcofeo 7853=0x1ead
kdxcoavs 7799
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8016] flag: K-----, lock: 0, len=16
col 0; len 1; (1): 61
col 1; len 1; (1): 80
col 2; len 1; (1): 31
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 00 a3
row#1[7999] flag: K-----, lock: 0, len=17
col 0; len 1; (1): 62
col 1; len 1; (1): 80
col 2; len 2; (2): 32 32
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 00 a3
row#2[7981] flag: K-----, lock: 0, len=18
col 0; len 1; (1): 63
col 1; len 1; (1): 80
col 2; len 3; (3): 33 33 33
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 00 a3
row#3[7962] flag: K-----, lock: 0, len=19
col 0; len 1; (1): 64
col 1; len 1; (1): 80
col 2; len 4; (4): 34 34 34 34
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 00 a3
row#4[7942] flag: K-----, lock: 0, len=20
col 0; len 1; (1): 65
col 1; len 1; (1): 80
col 2; len 5; (5): 35 35 35 35 35
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 00 a3
row#5[7921] flag: K-----, lock: 0, len=21
col 0; len 1; (1): 66
col 1; len 1; (1): 80
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 00 a3
row#6[7899] flag: K-----, lock: 0, len=22
col 0; len 1; (1): 67
col 1; len 1; (1): 80
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 00 a3
row#7[7876] flag: K-----, lock: 0, len=23
col 0; len 1; (1): 68
col 1; len 1; (1): 80
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 00 a3
row#8[7853] flag: K-----, lock: 0, len=23
col 0; NULL
col 1; len 1; (1): 80
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 00 a3
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 531 maxblk 531
--可以发现函数包括NULL,因为我建立的函数索引包含一个常量0.
SQL> select /*+ index(t1 IF_T_IOT_B ) */ a,b,substr(vc,1,40) from t_iot where b is null;
A B SUBSTR(VC,1,40)
---------- ---------- ----------------
999999999 b
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9s3ryp5xzyyjs, child number 0
-------------------------------------
select /*+ index(t1 IF_T_IOT_B ) */ a,b,substr(vc,1,40) from t_iot
where b is null
Plan hash value: 2568267667
--------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)|
|* 1 | INDEX UNIQUE SCAN| T_IOT_PK | 1 | 0 (0)|
|* 2 | INDEX RANGE SCAN| IF_T_IOT_B | 1 | 0 (0)|
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B" IS NULL)
2 - access("B" IS NULL)
总结：
IOT的第2索引也不包括NULL值。
时间： 2024-07-31 11:08:20