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

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

--//連結:
--//我記得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;