缥缈游侠-logzgh : not null对sql tunning的影响

===========================================================

not null对sql tunning的影响

===========================================================

作者: logzgh(http://logzgh.itpub.net)
发表于: 2007.09.09 22:02
分类: Oracle技术
出处: http://logzgh.itpub.net/post/3185/393950
---------------------------------------------------------------

由于普通索引中是不包含null记录的，这就意味着null与not null对sql tunning肯定在某些情况下会有影响。比如说如果某个字段允许为null的话，那么这个字段上面就没法走 INDEX (FAST FULL SCAN) 。但是在某些情况下，我们知道这个字段上面不可能有null值的，只是表设计时没有对这个字段加上not null的约束，此时单独访问这个字段时oracle也不会走index fast full scan。当然只需增加not null约束或者修改sql，增加is not null的子条件是可以解决的。具体见下面的例子：

在test表的domain_user_id字段上面建个索引。

该字段是允许为null的，但是事实上这个字段却不可能为null的。

create">c2c@c2c>create index idx_test on test (domain_user_id);

Index created.

我们看下面sql语句的执行计划：

set">c2c@c2c>set autotrace traceonly exp
c2c@c2c> SELECT /*+ordered use_hash(a b)*/
2 b.ROWID, b.user_type, '0' state
3 FROM (SELECT
4 DISTINCT domain_user_id duid
5 FROM test d) a,
6 md_user b
7 WHERE a.duid = b.domain_user_id;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=155008 Card=41986540
Bytes=2351246240)

1 0 HASH JOIN (Cost=155008 Card=41986540 Bytes=2351246240)
2 1 VIEW (Cost=64 Card=9557 Bytes=172026)
3 2 SORT (UNIQUE) (Cost=64 Card=9557 Bytes=172026)
4 3 TABLE ACCESS (FULL) OF 'TEST' (Cost=13 Card=9557 Byt
es=172026)

5 1 TABLE ACCESS (FULL) OF 'MD_USER' (Cost=150581 Card=41986
540 Bytes=1595488520)

可以看出，由于domain_user_id字段上允许为null，所以oracle无法走index fast full scan，即使强制hint。

修改sql语句，即然domain_user_id不可能为null，那么我们可以增加where d.domain_user_id is not null的子条件。
c2c@c2c> SELECT /*+ordered use_hash(a b)*/
2 b.ROWID, b.user_type, '0' state
3 FROM (SELECT
4 DISTINCT domain_user_id duid
5 FROM test d where d.domain_user_id is not null) a,
6 md_user b
7 WHERE a.duid = b.domain_user_id;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=150852 Card=2099986
Bytes=117599216)

1 0 HASH JOIN (Cost=150852 Card=2099986 Bytes=117599216)
2 1 VIEW (Cost=21 Card=478 Bytes=8604)
3 2 SORT (UNIQUE) (Cost=21 Card=478 Bytes=8604)
4 3 INDEX (FAST FULL SCAN) OF 'IDX_TEST' (NON-UNIQUE) (C
ost=4 Card=478 Bytes=8604)

5 1 TABLE ACCESS (FULL) OF 'MD_USER' (Cost=150581 Card=41986
540 Bytes=1595488520)

大家可以看出，这样是走了index fast full scan。在test表很大的情况下，这也许是最好的一种解决办法。

当然我们也可以增加not null的约束条件：

alter">c2c@c2c>alter table test modify domain_user_id not null;

Table altered.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=154999 Card=41986540
Bytes=2351246240)

1 0 HASH JOIN (Cost=154999 Card=41986540 Bytes=2351246240)
2 1 VIEW (Cost=55 Card=9557 Bytes=172026)
3 2 SORT (UNIQUE) (Cost=55 Card=9557 Bytes=172026)
4 3 INDEX (FAST FULL SCAN) OF 'IDX_TEST' (NON-UNIQUE) (C
ost=4 Card=9557 Bytes=172026)

5 1 TABLE ACCESS (FULL) OF 'MD_USER' (Cost=150581 Card=41986
540 Bytes=1595488520)
这样也走了index fast full scan.

当然一般情况下，最好是增加这个not null的约束条件。

但是当test表非常大时，并在7*24小时的系统上增加这个约束条件即是一件不太可能的事情，此时增加where子条件将是唯一可选的方法了。

logzgh 发表于:2007.09.09 22:02 ::分类: ( Oracle技术 ) ::阅读:(2820次) :: 评论 (2)