0
回答
Oracle 的 NULL 值在复合索引中对性能的影响
科大讯飞通用文字识别100000次/天免费使用。立即申请   
大家都知道Null值在oracle里面排序时被当作是最大的。就因为这一个小小的,看起来并不是很有用的知识点,在复合索引中对性能影响在某些情况下却是较大的。

下面是我们碰到的一个例子:
set">ops$admin@AEPDB>set autotrace on
select">ops$admin@AEPDB>select * from (select row_.*, rownum rownum_ from (
2 SELECT *
3 FROM udb.xxxx h
4 WHERE h.STATUS = '0' and h.LOCK_USER is not null order by h.GMT_REGISTER DESC) row_
5 where rownum <= 6) where rownum_ >= 1;

该SQL语句的作用是根据gmt_register排序取最近的6条记录。
在xxxx表上面有个复合索引(status,lock_user,gmt_register),其中lock_user和gmt_register都是可空的。

Execution Plan
----------------------------------------------------------
Plan hash value: 607461757

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 432 | 36 (0)| 00:00:01 |
|* 1 | VIEW | | 6 | 432 | 36 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 7 | 413 | 36 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | UDB_RANDOM_PASSPORT | 78 | 780 | 36 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN DESCENDING| UDB_RANDOM_PASSPORT_SGRLU_IND | 7 | | 35 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

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

1 - filter("ROWNUM_">=1)
2 - filter(ROWNUM<=6)
5 - access("H"."STATUS"='0')
filter("H"."LOCK_USER" IS NOT NULL AND "H"."STATUS"='0')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
338 consistent gets
0 physical reads
0 redo size
894 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

执行计划是正确的,但是逻辑读却有338个。其原因就是因为gmt_register有大量为null的值,null值又是最大的,
按desc排序的话,则需要先扫描大量的Status=0,gmt_register为null的块,然后才能找到符合要求的6条记录。

select">ops$admin@AEPDB>select * from (select row_.*, rownum rownum_ from (
SELECT *
FROM udb.xxxx h
WHERE h.STATUS = '0' and h.LOCK_USER is not null and h.gmt_register>to_date('1900-1-1','yyyymmdd')
--> (多增加了这个条件)
order by h.GMT_REGISTER DESC) row_ where rownum <= 6) where rownum_ >= 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 607461757

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 72 | 4 (0)| 00:00:01 |
|* 1 | VIEW | | 1 | 72 | 4 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 1 | 59 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | UDB_RANDOM_PASSPORT | 1 | 10 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN DESCENDING| UDB_RANDOM_PASSPORT_SGRLU_IND | 1 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

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

1 - filter("ROWNUM_">=1)
2 - filter(ROWNUM<=6)
5 - access("H"."STATUS"='0' AND "GMT_REGISTER">SYSDATE@!-360 AND "GMT_REGISTER" IS NOT NULL)
filter("H"."LOCK_USER" IS NOT NULL AND "H"."STATUS"='0' AND "GMT_REGISTER">SYSDATE@!-360)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
894 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

新增加的条件,对结果集是没有任何影响的,执行计划也没有改变,但是逻辑读却降到了10。
这是因为增加这个条件后索引扫描时直接过滤掉了null的块,直接跳到符合条件的块上面了。

在某些情况下利用字段可空,可以降低索引的大小,提高SQL的性能,但是在这个例子中,如果不用null值,gmt_register
字段给个很小的默认值,那这条SQL的逻辑读也只需要10左右。
所以一个字段是否可为空,还是有讲究的。

文章转自:http://logzgh.itpub.net/post/3185/458398

举报
鉴客
发帖于7年前 0回/680阅
顶部