[20180609]Wrong Results with IOT, Added Column and Secondary Index2.txt

--//链接:http://db-oriented.com/2018/06/05/wrong-results-with-iot-added-column-and-secondary-index/
--//我记得ITPUB上也有一位网友遇到类似的问题.
--//昨天测试很晚,上午又看一下测试,我发现对方建立的IOT表非常特殊,开始建立的表的字段全是主键.然后再增加
--//字段.

create table iot (
  x number,
  y number,
  constraint iot_pk primary key (x,y)
) organization index;
--//全部是主键.

alter table iot add z number;

row#0[7968] flag: K------, lock: 2, len=14
col 0; len 2; (2):  c1 02
col 1; len 2; (2):  c1 02
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
col  0: [ 2]  c1 2b          => 对应数字42

--//这样在IOT对应的索引块中没有如下信息(注意看下画线内容)
--//也就是对方建立的IOT表非常特殊.我决定自己建立例子再测试看看.

1.环境:
SCOTT@test01p> @ ver1
PORT_STRING          VERSION    BANNER                                                                       CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production      0

2.测试:
create table iot (
  a number,
  b number,
  c number,
  constraint iot_pk primary key (a,b)
) organization index;

--//建立第二索引:
create index i_iot_b on iot (b);

--//插入测试数据:
insert into iot select rownum,trunc(rownum/3)+1,null from dual connect by level<=7;
insert into iot values(8,3,0);
commit;

--//分析表略.
--//然后转储IOT信息:
SCOTT@test01p> select header_file,header_block from dba_segments where segment_name='IOT_PK';
HEADER_FILE HEADER_BLOCK
----------- ------------
          9          242

SCOTT@test01p> alter system dump datafile 9 block 243 ;
System altered.

Block header dump:  0x024000f3
 Object id on Block? Y
 seg/obj: 0x1a441  csc: 0x00.1a1ae27  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x24000f0 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.014.00005ede  0x01400638.0661.05  --U-    1  fsc 0x0000.01a1ae29
Leaf block dump
===============
header address 730818660=0x2b8f6864
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x90: opcode=0: iot flags=I-- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 8
kdxcofbo 52=0x34
kdxcofeo 7946=0x1f0a
kdxcoavs 7894
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[7959] flag: K------, lock: 0, len=11
col 0; len 2; (2):  c1 02
col 1; len 2; (2):  c1 02
tl: 3 fb: --H-FL-- lb: 0x0  cc: 0
row#1[7970] flag: K------, lock: 0, len=11
col 0; len 2; (2):  c1 03
col 1; len 2; (2):  c1 02
tl: 3 fb: --H-FL-- lb: 0x0  cc: 0
row#2[7981] flag: K------, lock: 0, len=11
col 0; len 2; (2):  c1 04
col 1; len 2; (2):  c1 03
tl: 3 fb: --H-FL-- lb: 0x0  cc: 0
row#3[7992] flag: K------, lock: 0, len=11
col 0; len 2; (2):  c1 05
col 1; len 2; (2):  c1 03
tl: 3 fb: --H-FL-- lb: 0x0  cc: 0
row#4[8003] flag: K------, lock: 0, len=11
col 0; len 2; (2):  c1 06
col 1; len 2; (2):  c1 03
tl: 3 fb: --H-FL-- lb: 0x0  cc: 0
row#5[8014] flag: K------, lock: 0, len=11
col 0; len 2; (2):  c1 07
col 1; len 2; (2):  c1 04
tl: 3 fb: --H-FL-- lb: 0x0  cc: 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
row#6[8025] flag: K------, lock: 0, len=11
col 0; len 2; (2):  c1 08
col 1; len 2; (2):  c1 04
tl: 3 fb: --H-FL-- lb: 0x0  cc: 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
row#7[7946] flag: K------, lock: 2, len=13
col 0; len 2; (2):  c1 09
col 1; len 2; (2):  c1 04
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 1]  80
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
End dump data blocks tsn: 3 file#: 9 minblk 243 maxblk 243

--//即使插入c字段是NULL,可以发现也存在如下信息:tl: 3 fb: --H-FL-- lb: 0x0  cc: 0
--//我估计这样就不存在作者前面提到的情况.
--//增加1列:
alter table iot add d number;
update iot set d=42 where a=1;
commit;

--//查看IOT表信息:
SCOTT@test01p> select * from iot;
         A          B          C          D
---------- ---------- ---------- ----------
         1          1                    42
         2          1
         3          2
         4          2
         5          2
         6          3
         7          3
         8          3          0

8 rows selected.

--//查询b=1的情况:
SCOTT@test01p> select /*+index(iot i_iot_b) */ * from iot where b=1;
         A          B          C          D
---------- ---------- ---------- ----------
         1          1                    42
         2          1

--//OK,没有问题.
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  033za1108jg5u, child number 0
-------------------------------------
select /*+index(iot i_iot_b) */ * from iot where b=1
Plan hash value: 1774368001
------------------------------------------------------------------------------
| Id  | Operation         | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |        |       |     1 (100)|          |
|*  1 |  INDEX UNIQUE SCAN| IOT_PK  |      3 |    21 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| I_IOT_B |      3 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / IOT@SEL$1
   2 - SEL$1 / IOT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("B"=1)
   2 - access("B"=1)

--//这也再次说明作者讲的例子非常特殊而出现的一种特殊情况.