[20121028]IOT的第2索引-NULL的問題.txt

lfree發表於2012-10-29
索引Null
[20121028]IOT的第2索引-NULL的問題.txt

IOT表實際上時索引結構，如果第2索引的鍵值為NULL，會是什麼情況呢？
因為第2索引包含主鍵，而主鍵是不能為NULL的，這樣即使第2索引的鍵值為NULL，會包括在第2索引中嗎?

自己做一些測試驗證看看：

1.測試環境:
SQL> select * from v$version where rownum<=1;
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值。

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

相關文章