在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.
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=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子条件将是唯一可选的方法了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/100091/viewspace-968673/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/100091/viewspace-968673/