如果优化器不确定是否包含空值，只能采用filter操作，而无法选择hash join，原因是无法对空值做hash
SQL> l
1 select * from t where id not in(select id from t where
2 (zhifang between 2.5 and 9.5) and (midu between 2.5 and 9.5)
3* and (danbai between 2.5 and 9.5) and (hanshui between 2.5 and 9.5))
SQL> /
执行计划
----------------------------------------------------------
Plan hash value: 366813129
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 1170 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T | 50 | 3250 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T | 27 | 1755 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "T" "T" WHERE
"ZHIFANG">=2.5 AND "ZHIFANG"<=9.5 AND "MIDU">=2.5 AND "MIDU"<=9.5 AND
"DANBAI">=2.5 AND "DANBAI"<=9.5 AND "HANSHUI">=2.5 AND "HANSHUI"<=9.5
AND LNNVL("ID"<>:B1)))
3 - filter("ZHIFANG">=2.5 AND "ZHIFANG"<=9.5 AND "MIDU">=2.5 AND
"MIDU"<=9.5 AND "DANBAI">=2.5 AND "DANBAI"<=9.5 AND "HANSHUI">=2.5 AND
"HANSHUI"<=9.5 AND LNNVL("ID"<>:B1))
Note
-----
- dynamic sampling used for this statement
在where条件中加上not null的条件后，优化器选择了hash join
SQL> select * from t where id is not null and id not in(select id from t where id is not null and
2 (zhifang between 2.5 and 9.5) and (midu between 2.5 and 9.5)
3 and (danbai between 2.5 and 9.5) and (hanshui between 2.5 and 9.5));
执行计划
----------------------------------------------------------
Plan hash value: 3340559942
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 2730 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 21 | 2730 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T | 50 | 3250 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T | 28 | 1820 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"="ID")
2 - filter("ID" IS NOT NULL)
3 - filter("ZHIFANG">=2.5 AND "ZHIFANG"<=9.5 AND "MIDU">=2.5 AND
"MIDU"<=9.5 AND "DANBAI">=2.5 AND "DANBAI"<=9.5 AND "HANSHUI">=2.5 AND
"HANSHUI"<=9.5 AND "ID" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-707656/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
一个实现行变列的存储过程
请登录后发表评论
登录
全部评论
