[20180608]Wrong Results with IOT, Added Column and Secondary Index.txt

[20180608]Wrong Results with IOT, Added Column and Secondary Index.txt

--//链接:http://db-oriented.com/2018/06/05/wrong-results-with-iot-added-column-and-secondary-index/
--//我记得ITPUB上也有一位网友遇到类似的问题.

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 (
  x number,
  y number,
  constraint iot_pk primary key (x,y)
) organization index;

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

--//插入测试数据:
insert into iot select rownum,mod(rownum,3) from dual connect by level<=7;
commit;

--//分析表略.

--//增加1列:
alter table iot add z number;
update iot set z=42 where x=1;
commit;

--//查看IOT表信息:
SCOTT@test01p> select * from iot;
      X          Y          Z
------- ---------- ----------
      1          1         42
      2          2
      3          0
      4          1
      5          2
      6          0
      7          1
7 rows selected.

--//Note that z contains the value 42 in the first row and null in all the other rows.

SCOTT@test01p> select * from iot where y=1;
      X          Y          Z
------- ---------- ----------
      1          1         42
      4          1
      7          1

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2r4bmd68zgj5u, child number 0
-------------------------------------
select * from iot where y=1
Plan hash value: 1517155850
----------------------------------------------------------------------------
| Id  | Operation        | Name   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |        |       |     1 (100)|          |
|*  1 |  INDEX SKIP SCAN | IOT_PK |      2 |    12 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / IOT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("Y"=1)
       filter("Y"=1)
--//我的测试是ok的,不过12c使用IOT_Pk 索引.走的是INDEX SKIP SCAN.没有选择secondary_idx索引.
--//使用提示/*+index(iot secondary_idx) */看看.

SCOTT@test01p> select /*+index(iot secondary_idx) */ * from iot where y=1;
      X          Y          Z
------- ---------- ----------
      1          1         42
      4          1         42
      7          1         42

--//对比前面可以发现走第2索引出现问题,显示结果错误.


SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  07mgh5fbhmc5q, child number 0
-------------------------------------
select /*+index(iot secondary_idx) */ * from iot where y=1
Plan hash value: 177722221
------------------------------------------------------------------------------------
| Id  | Operation         | Name          | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |        |       |     1 (100)|          |
|*  1 |  INDEX UNIQUE SCAN| IOT_PK        |      2 |    12 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| SECONDARY_IDX |      2 |       |     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("Y"=1)
   2 - access("Y"=1)

SCOTT@test01p> select /*+ index (iot secondary_idx) */ * from iot where y=2 ;
      X          Y          Z
------- ---------- ----------
      2          2
      5          2
--//这个正确.
   
It seems that the problem is not with the secondary index itself, as we get the correct rows, but something with
accessing the table via the secondary index causes the wrong results.

If we hint the query to access the table directly we get the correct results:

select /*+ index_ffs (iot) */ * from iot where y=1;

SCOTT@test01p> select /*+ index_ffs (iot) */ * from iot where y=1;
      X          Y          Z
------- ---------- ----------
      1          1         42
      4          1
      7          1

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9k5qc6yx1ju0m, child number 0
-------------------------------------
select /*+ index_ffs (iot) */ * from iot where y=1
Plan hash value: 3252171408
--------------------------------------------------------------------------------
| Id  | Operation            | Name   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |        |       |     2 (100)|          |
|*  1 |  INDEX FAST FULL SCAN| IOT_PK |      2 |    12 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / IOT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("Y"=1)

3.作者没有给出为什么?自己分析看看.
SCOTT@test01p> select dump(rowid,16) c50 ,rowid,dump(x,16) c20 ,dump(y,16)c20  ,x,y from iot where y=1;
C50                                            ROWID             C20               C20               X  Y
---------------------------------------------- ----------------- ----------------- ----------------- - --
Typ=208 Len=13: 2,4,2,40,0,f3,2,c1,2,2,c1,2,fe *BAJAAPMCwQICwQL+ Typ=2 Len=2: c1,2 Typ=2 Len=2: c1,2 1  1
Typ=208 Len=13: 2,4,2,40,0,f3,2,c1,5,2,c1,2,fe *BAJAAPMCwQUCwQL+ Typ=2 Len=2: c1,5 Typ=2 Len=2: c1,2 4  1
Typ=208 Len=13: 2,4,2,40,0,f3,2,c1,8,2,c1,2,fe *BAJAAPMCwQgCwQL+ Typ=2 Len=2: c1,8 Typ=2 Len=2: c1,2 7  1

--//以上查询仅仅扫描secondary_idx就ok,因为主键x,y的信息保存在rowid中,这种rowid叫逻辑rowid.

SCOTT@test01p> select header_file,header_block from dba_segments where segment_name='IOT_PK';
HEADER_FILE HEADER_BLOCK
----------- ------------
          9          242
--//IOT_PK索引的根节点就是dba=9,243.

SCOTT@test01p> @ convrdba.sql 9 243
RDBA16          RDBA
--------- ----------
024000f3    37748979

--//关于IOT的rowid相关信息参考:http://blog.itpub.net/267265/viewspace-717272/
--//再次总结如下:

1.开头的2,4，以及结尾fe实在不好猜测，不过好像是固定不变的。
2.中间的2,40,0,f3正好对应的就是IOT的根节点，也就是对应数据IOT的数据块。
3.2,c1,8,2,c1,2实际上就是主键信息,2中2表示主键x的长度，c1,8就是主键x的信息=7,
  2,c1,2 中2表示主键y的长度,c1,2就是主键y的信息.

--//以前也提过这种情况可能出现保存在rowid的块可能不是真实的数据块,因为iot的本质就是索引,可能索引分裂导致
--//相关信息不再原来的块中,这种情况叫物理猜.参考:http://blog.itpub.net/267265/viewspace-723115/

--//现在再来看看select /*+index(iot secondary_idx) */ * from iot where y=1;为什么错误.

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

Block header dump:  0x024000f3
 Object id on Block? Y
 seg/obj: 0x1a436  csc: 0x00.1a10c8b  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   0x000a.003.00006526  0x01400b0a.0664.17  --U-    1  fsc 0x0000.01a10c90
Leaf block dump
===============
header address 187256932=0xb295064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x90: opcode=0: iot flags=I-- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 7
kdxcofbo 50=0x32
kdxcofeo 7968=0x1f20
kdxcoavs 7926
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
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
row#1[7990] flag: -------, lock: 0, len=8
col 0; len 2; (2):  c1 03
col 1; len 2; (2):  c1 03
row#2[7998] flag: -------, lock: 0, len=7
col 0; len 2; (2):  c1 04
col 1; len 1; (1):  80
row#3[8005] flag: -------, lock: 0, len=8
col 0; len 2; (2):  c1 05
col 1; len 2; (2):  c1 02
row#4[8013] flag: -------, lock: 0, len=8
col 0; len 2; (2):  c1 06
col 1; len 2; (2):  c1 03
row#5[8021] flag: -------, lock: 0, len=7
col 0; len 2; (2):  c1 07
col 1; len 1; (1):  80
row#6[8028] flag: -------, lock: 0, len=8
col 0; len 2; (2):  c1 08
col 1; len 2; (2):  c1 02
----- end of leaf block Logical dump -----
--//视乎也没看出什么问题.

--//再修改1条看看:

SCOTT@test01p> update iot set z=43 where x=4;
1 row updated.

SCOTT@test01p> commit ;
Commit complete.

SCOTT@test01p> select /*+ index (iot secondary_idx) */ * from iot where y=1 ;
      X          Y          Z
------- ---------- ----------
      1          1         42
      4          1         43
      7          1         43

--//x=7 那行变成z=43.也是错误的.看来这个是一个bug.
--//视乎这样的错误重建索引应该也没有用.

SCOTT@test01p> alter index secondary_idx rebuild ;
Index altered.

SCOTT@test01p> select /*+ index (iot secondary_idx) */ * from iot where y=1 ;
      X          Y          Z
------- ---------- ----------
      1          1         42
      4          1         43
      7          1         43
--//换一种方式也应该一样.

SCOTT@test01p> alter index secondary_idx update block references;
Index altered.

SCOTT@test01p> select /*+ index (iot secondary_idx) */ * from iot where y=1 ;
      X          Y          Z
------- ---------- ----------
      1          1         42
      4          1         43
      7          1         43

SCOTT@test01p> alter index iot_pk rebuild ;
alter index iot_pk rebuild
*
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt
--//不能这样操作.

SCOTT@test01p> alter table iot move tablespace users;
Table altered.

SCOTT@test01p> select /*+ index (iot secondary_idx) */ * from iot where y=1 ;
      X          Y          Z
------- ---------- ----------
      1          1         42
      4          1         43
      7          1
--//这样显示正确,为什么实际上这个时候物理猜失败.另外对应IOT表(实际上是索引),移动表空间其他索引依旧有效.

SCOTT@test01p> select dump(rowid,16) c50 ,rowid,dump(x,16) c20 ,dump(y,16)c20  ,x,y from iot where y=1;
C50                                            ROWID             C20                  C20                        X          Y
---------------------------------------------- ----------------- -------------------- -------------------- ------- ----------
Typ=208 Len=13: 2,4,2,40,0,f3,2,c1,2,2,c1,2,fe *BAJAAPMCwQICwQL+ Typ=2 Len=2: c1,2    Typ=2 Len=2: c1,2          1          1
Typ=208 Len=13: 2,4,2,40,0,f3,2,c1,5,2,c1,2,fe *BAJAAPMCwQUCwQL+ Typ=2 Len=2: c1,5    Typ=2 Len=2: c1,2          4          1
Typ=208 Len=13: 2,4,2,40,0,f3,2,c1,8,2,c1,2,fe *BAJAAPMCwQgCwQL+ Typ=2 Len=2: c1,8    Typ=2 Len=2: c1,2          7          1
--//对比前面2,40,0,f3没变.

SCOTT@test01p> select header_file,header_block from dba_segments where segment_name='IOT_PK';
HEADER_FILE HEADER_BLOCK
----------- ------------
          9          250

--//而实际上现在IOT的根节点是dba=9,251.如果现在rebuild secondary_idx问题应该会再现吗?

SCOTT@test01p> alter index secondary_idx rebuild ;
Index altered.

SCOTT@test01p> select dump(rowid,16) c50 ,rowid,dump(x,16) c20 ,dump(y,16)c20  ,x,y from iot where y=1;
C50                                                ROWID             C20                  C20                        X          Y
-------------------------------------------------- ----------------- -------------------- -------------------- ------- ----------
Typ=208 Len=13: 2,4,2,40,0,fb,2,c1,2,2,c1,2,fe     *BAJAAPsCwQICwQL+ Typ=2 Len=2: c1,2    Typ=2 Len=2: c1,2          1          1
Typ=208 Len=13: 2,4,2,40,0,fb,2,c1,5,2,c1,2,fe     *BAJAAPsCwQUCwQL+ Typ=2 Len=2: c1,5    Typ=2 Len=2: c1,2          4          1
Typ=208 Len=13: 2,4,2,40,0,fb,2,c1,8,2,c1,2,fe     *BAJAAPsCwQgCwQL+ Typ=2 Len=2: c1,8    Typ=2 Len=2: c1,2          7          1

SCOTT@test01p> @ convrdba.sql 9 251
RDBA16          RDBA
--------- ----------
024000fb    37748987

SCOTT@test01p> select /*+ index (iot secondary_idx) */ * from iot where y=1 ;
      X          Y          Z
------- ---------- ----------
      1          1         42
      4          1         43
      7          1
--//现在显示正确的.

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

Block header dump:  0x024000fb
 Object id on Block? Y
 seg/obj: 0x1a43c  csc: 0x00.1a13f09  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x24000f8 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   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.01a13f09
Leaf block dump
===============
header address 187256932=0xb295064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x90: opcode=0: iot flags=I-- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 7
kdxcofbo 50=0x32
kdxcofeo 7955=0x1f13
kdxcoavs 7905
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8022] flag: K------, lock: 0, 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
row#1[8011] flag: K------, lock: 0, len=11
col 0; len 2; (2):  c1 03
col 1; len 2; (2):  c1 03
tl: 3 fb: --H-FL-- lb: 0x0  cc: 0
row#2[8001] flag: K------, lock: 0, len=10
col 0; len 2; (2):  c1 04
col 1; len 1; (1):  80
tl: 3 fb: --H-FL-- lb: 0x0  cc: 0
row#3[7987] flag: K------, lock: 0, len=14
col 0; len 2; (2):  c1 05
col 1; len 2; (2):  c1 02
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 2]  c1 2c
row#4[7976] 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[7966] flag: K------, lock: 0, len=10
col 0; len 2; (2):  c1 07
col 1; len 1; (1):  80
tl: 3 fb: --H-FL-- lb: 0x0  cc: 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
row#6[7955] flag: K------, lock: 0, len=11
col 0; len 2; (2):  c1 08
col 1; len 2; (2):  c1 02
tl: 3 fb: --H-FL-- lb: 0x0  cc: 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ //前面tl: 3应该表示长度.
--//注意看下划线信息,前面没有,也许就是这样导致异常.因为z字段是增加的字段,前面的测试并没有修改索引块.

4.按照这样的测试,如果在IOT增加字段,修改值都有可能出现查询异常的情况.
--//这个应该在实际的工作引起重视,不过在国内许多应用中在业务表使用IOT的情况很少,^_^.
--//补充说明:可以发现作者建立的iot非常特殊,开始仅仅都是主键.然后在增加字段,估计这种情况很少见.

5.附上convrdba.sql的脚本:
select
 TO_CHAR (dbms_utility.make_data_block_address(&1,&2), 'FM0xxxxxxxx') rdba16,
dbms_utility.make_data_block_address(&&1,&&2) rdba
from dual;