[20180609]Wrong Results with IOT, Added Column and Secondary Index2.txt
[20180609]Wrong Results with IOT, Added Column and Secondary Index2.txt
--//連結:
--//我記得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)
--//這也再次說明作者講的例子非常特殊而出現的一種特殊情況.
--//連結:
--//我記得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)
--//這也再次說明作者講的例子非常特殊而出現的一種特殊情況.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2156069/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20180608]Wrong Results with IOT, Added Column and Secondary Index.txtIndex
- mysql 1558 - Column count of mysql.proc is wrong 的解決MySql
- ORA-00600[QERXTAGENTOPEN_911] OCCURS IN IMPDP AFTER COLUMN TO TABLE IS ADDED
- What is wrong?
- Examples of Secondary IndexIndex
- MySQL 5.7 建立使用者報錯 ERROR 1805 (HY000): Column count of mysql.user is wrongMySqlError
- Elasticsearch——Filter search resultsElasticsearchFilter
- The virtual machine could not be added. The virtual machine configuration could not be added. A conf...Mac
- Creating Secondary IndexesIndex
- Oracle:On ROWNUM and Limiting ResultsOracleMIT
- SQL can execute in wrong SchemaSQL
- Error: no such columnError
- What is meant by Primary Index and Secondary IndexIndex
- 再見!onActivityResult!你好,Activity Results API!API
- NFS mount results in "vmount: operation not permitted" errorNFSMITError
- 結果分析碼( results analysis key )
- iDLHCGP_Numerical_results
- iDLHCG_Numerical_results
- Column Monitoring
- js 原生獲取 url地址引數 const query = new URLSearchParams(window.location.search); const added = query.get("added")JS
- OCR integrity results are inconsistent amongst the nodes.
- Numerical Results of adf-mDFP
- java.lang.IllegalArgumentException: Wrong state classsJavaException
- Index column size too large. The maximum column size is 767 bytesIndex
- Hibernate 註解@Column(nullable = false) 和 @Column(unique=true)NullFalse
- MongoDB系列四:解決secondary的讀操作MongoDB
- Mongodb在replicaset的secondary上補建索引MongoDB索引
- ORA-29857: domain indexes and/or secondary objects exist in the tablespaceAIIndexObject
- Secondary Indexes on Index-Organized Tables (231)IndexZed
- CSS column-spanCSS
- CSS column-gapCSS
- CSS column-ruleCSS
- Oracle Column Group StatisticsOracle
- MAX or MIN of Indexed ColumnIndex
- [PT]Column Histogram StatisticsHistogram
- 揭開OKR (Objectives and Key Results) 的面紗OKRObject
- How can I get the followling results?
- Results of T2DFP and iT2DFP methods