ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引与null(二):组合索引

索引与null(二):组合索引

原创 Linux操作系统 作者:space6212 时间:2019-06-01 09:57:07 0 删除 编辑

这篇文章主要讨论组合索引与null的关系。


二、组合索引

SQL> create table t(a int,b int,c int);

Table created

SQL> create unique index idx_t on t(b,c);

Index created

SQL> select * from t;

A B C
---------- ---------- ----------
1 1 1
2 2
3 3
4

SQL>
1、索引是否索引null值

SQL> analyze table t compute statistics for table for all indexes for all indexed columns;

Table analyzed

SQL> select index_name,table_name,num_rows from user_indexes where table_name='T';

INDEX_NAME TABLE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
IDX_T T 3

--索引包含了3行,显然,字段不全为空的行可以被索引。
把索引的block dump出来,主要部分如下:
-------------------------------------------------------------------
row#0[8022] flag: -----, lock: 0, data:(6): 01 80 74 52 00 00
col 0; len 2; (2): c1 02
col 1; len 2; (2): c1 02
row#1[8010] flag: -----, lock: 0, data:(6): 01 80 74 52 00 01
col 0; len 2; (2): c1 03
col 1; NULL
row#2[7998] flag: -----, lock: 0, data:(6): 01 80 74 52 00 02
col 0; NULL
col 1; len 2; (2): c1 04
------------------------------------------------------------------
--可见,唯一性索引并不包含rowid

2、null和null是否相等

SQL> select * from t;

A B C
---------- ---------- ----------
1 1 1
2 2
3 3
4

SQL> insert into t values(5,2,null);

insert into t values(5,2,null)

ORA-00001: 违反唯一约束条件 (SUK.IDX_T)

--在(b,c)上建有唯一性索引,这里违反了唯一性约束是不是表示(2,null)=(2,null),也就是null=null呢?其实这样理解是错误的,在oracle中,null=null、null<>null永远都不会成立,oracle在处理唯一性约束时也不例外。在这里例子中,null=null只是表象,从这个表象得到的结论是错误的。()
--我们知道,oracle索引不索引空值,唯一行索引索引值由列值组成,不包含rowid,在(2,null)这行中,索引的键值是2(null不被索引),如果再查入一行(2,null),键值也为2,违反了唯一性所以约束,也就是上例报错的真正原因。
--实际上,在唯一性组合索引中,如果两行中相同的列的值相同,则会违反唯一性约束,如:
(2,null)和(2,null)会违反唯一性约束,但
(2,null)和(null,2)不会违反唯一性约束。

3、为什么hints不起作用

我们做一系列与单列索引时类似的测试:
测试一、
suk@ORACLE9I> select /*+ index(t idx_t) */ * from t;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=36)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=4 Bytes=36)

--hint不起作用

测试二、suk@ORACLE9I> select /*+ index(t idx_t) */ * from t where b is not null;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=18)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=2 Bytes=
18)

2 1 INDEX (FULL SCAN) OF 'IDX_T' (UNIQUE) (Cost=1 Card=2)

--指明组合索引的其中一个列不为空后可以走索引

测试三、suk@ORACLE9I> select /*+ index(t idx_t) */ * from t where b=1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=9)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
9)

2 1 INDEX (RANGE SCAN) OF 'IDX_T' (UNIQUE) (Cost=1 Card=1)

--指明组合索引其中一列为某一个值后可以走索引

测试四、suk@ORACLE9I> select /*+ index(t idx_t) */ * from t where c=null;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=18)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=2 Bytes=
18)

2 1 INDEX (FULL SCAN) OF 'IDX_T' (UNIQUE) (Cost=1 Card=2)

--指明组合索引的其中一列为某一个值后可以走索引(这里把null看成一个常数)

测试五、suk@ORACLE9I> select /*+ index(t idx_t) */ * from t where b=1 and c=1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=9)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=
9)

2 1 INDEX (UNIQUE SCAN) OF 'IDX_T' (UNIQUE)

--同时指定b、c的值后可以走索引


测试六、suk@ORACLE9I> select /*+ index(t idx_t) */ * from t where b is null and c is not null;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=9)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
9)

2 1 INDEX (RANGE SCAN) OF 'IDX_T' (UNIQUE) (Cost=1 Card=1)
--指定组合索引中的一列为空,另一列不能为空,可以走索引

测试八、
SQL> delete from t where b is null or c is null;

3 rows deleted

SQL> alter table t modify b not null;

Table altered

suk@ORACLE9I> select /*+ index(t idx_t) */ * from t;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=36)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=4 Bytes=
36)

2 1 INDEX (FULL SCAN) OF 'IDX_T' (UNIQUE) (Cost=1 Card=4)

--为组合索引的其中一个字段加上not null约束可以走索引

测试九、suk@ORACLE9I> alter table t modify b null;

表已更改。

suk@ORACLE9I> alter table t modify c not null;

表已更改。

suk@ORACLE9I> select /*+ index(t idx_t) */ * from t where b is null and c is null;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=18)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=2 Bytes=
18)

2 1 INDEX (RANGE SCAN) OF 'IDX_T' (UNIQUE) (Cost=1 Card=2)

--组合索引的其中一个字段限定了不能为空,即使条件都是is null也可以走索引
--oracle在执行sql的时候会查看表的设计,然后得到的表的结构信息忽略一些无效的调教,上例就是这样。
--这也说明了网上常说的col is null不能用到索引的说法是不完全准确的,在某些条件下,col is null也可以用到索引。

4、用hints强制使用索引的前提
假如有组合索引(a,b):
(1)在想要用到的组合索引对应的其中一个或者多个字段上有not null约束就可以用到索引。如果索引列其中一个或者多个有not null约束,无论条件怎么写都可能用到索引(包括a is null and b is null)
(2)如果索引列没有not null约束,在where条件中限定了用到的索引对应的其中一个或者多个列为not null就可以用到索引,如a is not null(甚置 a is not null and b is null 也可以用到索引)
(3)如果索引列没有not null约束,且在条件中没有指明索引列为not null,在where条件中包含了用到的索引对应的一个或者多个列与其他值的比较(除a is null外)可以用到索引。甚至 a=1 and b is null 也可以用到索引

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/231499/viewspace-63751/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    245
  • 访问量
    163410