开发者社区> lfreeali> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

[20121028]IOT的第2索引-NULL的问题.txt

简介: [20121028]IOT的第2索引-NULL的问题.txt IOT表实际上时索引结构,如果第2索引的键值为NULL,会是什么情况呢? 因为第2索引包含主键,而主键是不能为NULL的,这样即使第2索引的键值为NULL,会包括在第2索引中吗? 自己做一些测试验证看看: 1.
+关注继续查看
[20121028]IOT的第2索引-NULL的问题.txt

IOT表实际上时索引结构,如果第2索引的键值为NULL,会是什么情况呢?
因为第2索引包含主键,而主键是不能为NULL的,这样即使第2索引的键值为NULL,会包括在第2索引中吗?

自己做一些测试验证看看:

1.测试环境:
SQL> select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> create table t_iot ( a varchar2(10),b varchar2(10),vc varchar2(1200), constraint t_iot_pk PRIMARY KEY(a)) ORGANIZATION INDEX;
SQL> create index i_t_iot_b on t_iot(b);
insert into t_iot values ('1'        ,'a','a');
insert into t_iot values ('22'       ,'b','a');
insert into t_iot values ('333'      ,'c','a');
insert into t_iot values ('4444'     ,'d','a');
insert into t_iot values ('55555'    ,'e','a');
insert into t_iot values ('666666'   ,'f','a');
insert into t_iot values ('7777777'  ,'g','a');
insert into t_iot values ('88888888' ,'h','a');
commit ;

SQL> exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'T_iot');

2.看看主键为NULL,可以插入吗?
insert into t_iot values (NULL,'j','a');
                          *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T_IOT"."A")

SQL> insert into t_iot values ('999999999',NULL,'b');
1 row created.
SQL> commit ;
Commit complete.

3.查询测试:
SQL> select /*+ index(t1,i_t_iot_b) */ a,b,substr(vc,1,40) from t_iot where b is null;

A          B          SUBSTR(VC,1,40)
---------- ---------- ---------------
999999999             b

SQL> @dpc

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  6wzax9paxk4ag, child number 0
-------------------------------------
select /*+ index(t1,i_t_iot_b) */ a,b,substr(vc,1,40) from t_iot where
b is null

Plan hash value: 2901191065

-----------------------------------------------------------
| Id  | Operation        | Name     | E-Rows | Cost (%CPU)|
-----------------------------------------------------------
|   0 | SELECT STATEMENT |          |        |     1 (100)|
|*  1 |  INDEX FULL SCAN | T_IOT_PK |      1 |     1   (0)|
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("B" IS NULL)

--可以发现并不使用第2索引i_t_iot_b.

SQL> select /*+ index(t1,i_t_iot_b) */ a,b,substr(vc,1,40) from t_iot where b='a';

A          B          SUBSTR(VC,1,40)
---------- ---------- ---------------
1          a          a

SQL> @dpc

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9631w0zracpn8, child number 0
-------------------------------------
select /*+ index(t1,i_t_iot_b) */ a,b,substr(vc,1,40) from t_iot where
b='a'

Plan hash value: 1095339046

-------------------------------------------------------------
| Id  | Operation         | Name      | E-Rows | Cost (%CPU)|
-------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |        |     1 (100)|
|*  1 |  INDEX UNIQUE SCAN| T_IOT_PK  |      1 |     1   (0)|
|*  2 |   INDEX RANGE SCAN| I_T_IOT_B |      1 |     1   (0)|
-------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"='a')
   2 - access("B"='a')

4.转储第2索引 i_t_iot_b:

SQL> select header_file,header_block from dba_segments where segment_name='I_T_IOT_B';
HEADER_FILE HEADER_BLOCK
----------- ------------
          4          522

SQL> select object_id,data_object_id from dba_objects where object_name='I_T_IOT_B';
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    117537         117537

SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 117537';
----- begin tree dump
leaf: 0x100020b 16777739 (0: nrow: 8 rrow: 8)
----- end tree dump

仅仅占用1个块。HEADER_BLOCK=533,根节点=523.

SQL> alter system dump datafile 4 block 523 ;
Block header dump:  0x0100020b
 Object id on Block? Y
 seg/obj: 0x1cb21  csc: 0x00.b3748a14  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x1000208 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.00c.00002319  0x00c041b1.1296.50  --U-    8  fsc 0x0000.b3748a1e
Leaf block dump
===============
header address 182924685412=0x2a97293064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x90: pcode=0: iot flags=I-- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 8
kdxcofbo 52=0x34
kdxcofeo 7892=0x1ed4
kdxcoavs 7840
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8018] flag: K-----, lock: 2, len=14
col 0; len 1; (1):  61
col 1; len 1; (1):  31
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 00 a3
row#1[8003] flag: K-----, lock: 2, len=15
col 0; len 1; (1):  62
col 1; len 2; (2):  32 32
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 00 a3
row#2[7987] flag: K-----, lock: 2, len=16
col 0; len 1; (1):  63
col 1; len 3; (3):  33 33 33
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 00 a3
row#3[7970] flag: K-----, lock: 2, len=17
col 0; len 1; (1):  64
col 1; len 4; (4):  34 34 34 34
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 00 a3
row#4[7952] flag: K-----, lock: 2, len=18
col 0; len 1; (1):  65
col 1; len 5; (5):  35 35 35 35 35
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 00 a3
row#5[7933] flag: K-----, lock: 2, len=19
col 0; len 1; (1):  66
col 1; len 6; (6):  36 36 36 36 36 36
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 00 a3
row#6[7913] flag: K-----, lock: 2, len=20
col 0; len 1; (1):  67
col 1; len 7; (7):  37 37 37 37 37 37 37
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 00 a3
row#7[7892] flag: K-----, lock: 2, len=21
col 0; len 1; (1):  68
col 1; len 8; (8):  38 38 38 38 38 38 38 38
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 00 a3
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 523 maxblk 523

--可以发现并没有NULL.这点有点奇怪,oracle的第2索引包含主键,而主键是非空的.

5.建立函数索引看看.
SQL> create index if_t_iot_b on t_iot(b,0);
Index created.

SQL> select header_file,header_block from dba_segments where segment_name='IF_T_IOT_B';

HEADER_FILE HEADER_BLOCK
----------- ------------
          4          530

SQL> alter system dump datafile 4 block 531 ;
System altered.

Block header dump:  0x01000213
 Object id on Block? Y
 seg/obj: 0x1cb1b  csc: 0x00.b374789f  itc: 3  flg: E  typ: 1 - DATA
"/u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_10830.trc" 1276L, 65346C                                                                                                         551,1         42%
kdxconro 9
kdxcofbo 54=0x36
kdxcofeo 7853=0x1ead
kdxcoavs 7799
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8016] flag: K-----, lock: 0, len=16
col 0; len 1; (1):  61
col 1; len 1; (1):  80
col 2; len 1; (1):  31
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 00 a3
row#1[7999] flag: K-----, lock: 0, len=17
col 0; len 1; (1):  62
col 1; len 1; (1):  80
col 2; len 2; (2):  32 32
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 00 a3
row#2[7981] flag: K-----, lock: 0, len=18
col 0; len 1; (1):  63
col 1; len 1; (1):  80
col 2; len 3; (3):  33 33 33
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 00 a3
row#3[7962] flag: K-----, lock: 0, len=19
col 0; len 1; (1):  64
col 1; len 1; (1):  80
col 2; len 4; (4):  34 34 34 34
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 00 a3
row#4[7942] flag: K-----, lock: 0, len=20
col 0; len 1; (1):  65
col 1; len 1; (1):  80
col 2; len 5; (5):  35 35 35 35 35
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 00 a3
row#5[7921] flag: K-----, lock: 0, len=21
col 0; len 1; (1):  66
col 1; len 1; (1):  80
col 2; len 6; (6):  36 36 36 36 36 36
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 00 a3
row#6[7899] flag: K-----, lock: 0, len=22
col 0; len 1; (1):  67
col 1; len 1; (1):  80
col 2; len 7; (7):  37 37 37 37 37 37 37
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 00 a3
row#7[7876] flag: K-----, lock: 0, len=23
col 0; len 1; (1):  68
col 1; len 1; (1):  80
col 2; len 8; (8):  38 38 38 38 38 38 38 38
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 00 a3
row#8[7853] flag: K-----, lock: 0, len=23
col 0; NULL
col 1; len 1; (1):  80
col 2; len 9; (9):  39 39 39 39 39 39 39 39 39
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 00 a3
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 531 maxblk 531

--可以发现函数包括NULL,因为我建立的函数索引包含一个常量0.

SQL> select /*+ index(t1 IF_T_IOT_B ) */ a,b,substr(vc,1,40) from t_iot where b is  null;

A          B          SUBSTR(VC,1,40)
---------- ---------- ----------------
999999999             b

SQL> @dpc

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9s3ryp5xzyyjs, child number 0
-------------------------------------
select /*+ index(t1 IF_T_IOT_B ) */ a,b,substr(vc,1,40) from t_iot
where b is  null

Plan hash value: 2568267667

--------------------------------------------------------------
| Id  | Operation         | Name       | E-Rows | Cost (%CPU)|
--------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |        |     1 (100)|
|*  1 |  INDEX UNIQUE SCAN| T_IOT_PK   |      1 |     0   (0)|
|*  2 |   INDEX RANGE SCAN| IF_T_IOT_B |      1 |     0   (0)|
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B" IS NULL)
   2 - access("B" IS NULL)

总结:
IOT的第2索引也不包括NULL值。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
C#项目””是针对”.NETFramework,Version=v4.5.2”但此计算机没有,需要修改为v4.5.1.
C#项目””是针对”.NETFramework,Version=v4.5.2”但此计算机没有,需要修改为v4.5.1.
97 0
Jerry的通过CDS view + Smart Template 开发Fiori应用的blog合集
S4/HANA里有一个新的UI框架叫做Smart template, 配合ABAP后台的CDS view技术,能够让developer以Metadata driven的方式来开发Fiori应用, 这种开发方式几乎不需要或者只需要很少量的JavaScript开发。
1816 0
Codeforces Round #345 (Div. 2)【A.模拟,B,暴力,C,STL,容斥原理】
A. Joysticks time limit per test:1 second memory limit per test:256 megabytes input:standard input output:standard output Friends are going to play console.
1125 0
【并非原创】SAP Fiori 2.0: The Ideal Overview
SAP Fiori 2.0: The Ideal Overview http://scn.sap.com/community/fiori/blog/2016/08/25/sap-fiori-20-the-ideal-overview ...
1456 0
boost库thread.hpp编译警告(type attributes are honored only at type definition)已修复
请浏览:https://svn.boost.org/trac/boost/ticket/7874 #7874: compile warning: thread.
779 0
[20120508]bad sql.txt
[20120508]bad sql.txt1.昨天在优化一个程序外的sql,遇到这个帖子:http://www.itpub.net/thread-1495845-1-1.
526 0
[20120319]一条sql语句的优化.txt
前天检查数据库,发现一天sql语句执行如下:SELECT MAX (undrug_code)   FROM undrug_info  WHERE SUBSTR (undrug_code, 1, 1) = 'F';undrug_code是表undrug_info的主键.
781 0
+关注
lfreeali
熟悉oracle数据库性能优化,rman备份,数据库恢复技术.
2121
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载