[20121028]IOT的第2索引-NULL的问题.txt-低调大师

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

2012-10-28 760

[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值。

微信关注我们

原文链接：https://yq.aliyun.com/articles/281635

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

【强烈强烈推荐】《ORACLE PL/SQL编程详细》--系列文章目录导航

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/chinahuyong/article/details/7800634 《ORACLEPL/SQL编程详细》系列文章目录导航 ——通过知识共享树立个人品牌。觉得对你有帮助，请留言与猛点推荐，谢谢。 [推荐]ORACLEPL/SQL编程详解之一：PL/SQL程序设计简介(千里之行，始于足下) 本篇主要内容如下：第一章PL/SQL程序设计简介1.1SQL与PL/SQL1.1.1什么是PL/SQL?1.2PL/SQL的优点或特征1.2.1有利于客户/服务器环境应用的运行1.2.2适合于客户环境1.2.3过程化1.2.4模块化1.2.5运行错误的可处理性1.2.6提供大量内置程序包1.3PL/SQL可用的SQL语句1.4运行PL/SQL程序阅读全文 [顶]ORACLEPL/SQL编程详解之二：PL/SQL块结构和组成元素(为山九仞，岂一日之功) 本篇主要内容如下：2.1PL/SQL块2.2PL/SQL结构2.3标识符2.4PL/SQL变量类型2.4.1变量类型2.4.2复合类型2.4...

2012-07-30

843

[20121028]IOT字段定义顺序的问题.txt 如果在IOT中定义的主键不在第1字段，实际的存贮会出现什么情况呢？看了一些blog： http://richardfoote.wordpress.com/2012/01/18/index-organized-tables-overflow-segment-part-ii-the-loneliest-guy/ 自己做一些测试，加强记忆与理解： 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 ( b varchar2(10),vc varchar2(1200),a varchar2(10), const...

2012-10-29

744

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

JDK

JDK是 Java 语言的软件开发工具包，主要用于移动设备、嵌入式设备上的java应用程序。JDK是整个java开发的核心，它包含了JAVA的运行环境（JVM+Java系统类库）和JAVA工具。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。