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

[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.测试:

  a number,

  b number,

  c number,

  constraint iot_pk primary key (a,b)

--//建立第二索引:

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

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

row#2[7981] flag: K------, lock: 0, len=11

col 0; len 2; (2):  c1 04

col 1; len 2; (2):  c1 03

row#3[7992] flag: K------, lock: 0, len=11

col 0; len 2; (2):  c1 05

row#4[8003] flag: K------, lock: 0, len=11

col 0; len 2; (2):  c1 06

row#5[8014] flag: K------, lock: 0, len=11

col 0; len 2; (2):  c1 07

col 1; len 2; (2):  c1 04

row#6[8025] flag: K------, lock: 0, len=11

col 0; len 2; (2):  c1 08

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

row#7[7946] flag: K------, lock: 2, len=13

col 0; len 2; (2):  c1 09

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;

--//查看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;

--//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)

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