ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引与null(一):单列索引

索引与null(一):单列索引

原创 Linux操作系统 作者:space6212 时间:2019-03-04 22:54:06 0 删除 编辑

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


一、单列索引
SQL> create table t(a int,b int);

Table created

SQL> create index idx_t on t(a);

Index created

SQL> select * from t;

A B
---------- ----------
1 1
2 2
3

SQL> analyze index idx_t compute statistics;

Index analyzed



1、单列是否索引null
SQL> select index_name,NUM_ROWS,DISTINCT_KEYS from user_indexes where index_name='IDX_T';

INDEX_NAME NUM_ROWS DISTINCT_KEYS
------------------------------ ---------- -------------
IDX_T 2 2

从以上可以看出,索引不索引null值,只索引了a=1和a=2的值
也可以用把索引所在block dump出来查看索引情况(索引所在文件位置可由dba_extents的file_id,block_id确定)
trace文件主要部分如下:
-----------------------------------------------------
row#0[8024] flag: -----, lock: 2
col 0; len 2; (2): c1 02 --这是列值
col 1; len 6; (6): 01 80 79 b2 00 00 --这是rowid
row#1[8012] flag: -----, lock: 2
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 80 79 b2 00 01
-----------------------------------------------------
我们可以通过如下方法验证:
SQL> select to_number('c1','xxx'),to_number('02','xxxx'),to_number('c1','xxx'),to_number('03','xxxx') from dual;

TO_NUMBER('C1','XXX') TO_NUMBER('02','XXXX') TO_NUMBER('C1','XXX') TO_NUMBER('03','XXXX')
--------------------- ---------------------- --------------------- ----------------------
193 2 193 3

SQL> select dump(1),dump(2) from dual;

DUMP(1) DUMP(2)
------------------ ------------------
Typ=2 Len=2: 193,2 Typ=2 Len=2: 193,3

可以看出,只有a=1和a=2的记录被索引了。

2、为什么hints不起作用

suk@ORACLE9I> select * from t;

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


我们在某些时候需要使用hints来调整sql的执行计划,比如我们可以使用/*+ index(table_name index_name) */来强制使用索引:

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

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=82 Bytes=2132
)

1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=82 Bytes=2132)

suk@ORACLE9I> select /*+ index(t idx_t) */ * from t where b=2;

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


这个执行计划与我们预期不一样,它并没有按照hints使用索引来存取数据。这是为什么呢?
上面的第一个sql的目的是返回t表的所有数据,第二个是要返回满足条件b=2的所有数据。我们知道,B*tree索引是不会null值的,在以上的查询中如果用索引的话,无论是直接从索引返回数据,还是根据索引扫描进而通过rowid获取数据,都无法保证返回数据的正确性。oracle意识到这一点,忽略了强制使用索引的hints,通过全表扫描来返回数据。

3、用hints强制使用索引的前提

上文我们知道,是因为a列存在null值导致不能使用索引,那么我们把a is null的记录删除了,是否可以使用hints呢?suk@ORACLE9I> delete from t where a is null;

已删除 1 行。

suk@ORACLE9I> commit;

提交完成。

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

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=82 Bytes=2132
)

1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=82 Bytes=2132)

从执行计划看出,即使表t的a列已经不存在null的情况,但仍然使用了全表扫描。这是因为oracle并不知道a列是否会存在null的情况(oracle不可能做到每次执行都遍历一次表的数据查看数据的分布情况),为了保证返回数据的正确行,oracle选择了全表扫描。
我们接着看:

suk@ORACLE9I> select /*+ index(t idx_t) */ * from t where a 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' (NON-UNIQUE) (Cost=1 Card=2
)

现在的执行计划已经如我们所愿,走索引了。这是因为我们限定了a不能为null,这样就可以保证使用索引就可以返回所有需要的数据。

接着看:suk@ORACLE9I> select /*+ index(t idx_t) */ * from t where a=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' (NON-UNIQUE) (Cost=1 Card=
1)

执行计划也可以走索引了,因为设置了a=1,已经限定了a肯定是一个非空的值了,可以从索引返回数据了。

再考察两个例子:suk@ORACLE9I> select /*+ index(t idx_t) */ * from t where a<>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 (FULL SCAN) OF 'IDX_T' (NON-UNIQUE) (Cost=1 Card=1
)


suk@ORACLE9I> select /*+ index(t idx_t) */ * from t where a=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' (NON-UNIQUE) (Cost=1 Card=
1)

在oracle中,null=null和null<>null都不成立,null不能与任何值作大小比较。
由于null的这个特殊性,在条件a<>1中,已经忽略了a is null的情况,相当于已经隐含了a is not null条件了;
由于判断某一个列是否为null只能用is [not] null,在a=null中,这里的null被看作是一个常数(虽然它永远为false),所以这个条件除了返回数据不一样以外,其它作用与a=1是一样的,也可以通过索引来获取所有符合条件的数据。

再看下一个例子:

suk@ORACLE9I> alter table t modify(a not null);

表已更改。

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

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' (NON-UNIQUE) (Cost=1 Card=2
)

我们对表t的a列加上一个not null约束,orace知道索引会包含了a列的所有值,用索引可以返回所有符合条件的数据,索引hints起作用了。

从上面的例子可以看出,B*TREE索引不会索引null是整个问题的关键。因此,如果想要使用索引扫描,甚至是强制用/*+ index(table_name index_name) */强制使用索引,必须满足以下三个条件中的一个或者多个:
(1)在想要用到的索引对应的字段上有not null约束就可以用到索引。如果索引列有not null约束,无论条件怎么写都可能用到索引(包括col is null)
(2)如果索引列没有not null约束,在where条件中限定了用到的索引对应的列为not null可以用到索引。如col1 is not null
(3)如果索引列没有not null约束,且在条件中没有指明索引列为not null,则在where条件中包含了用到的索引对应的列与其他值的比较(除col1 is null外)可以用到索引。假如索引对应列为a,则包含以下条件的查询都可能用到索引:
a=b/a<>b/a>b/a

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

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

注册时间:2005-01-25

  • 博文量
    118
  • 访问量
    85552