[转帖]一次针对Not in的优化--如何让其选择hash join anti_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 3745 | 回复: 0   主题: [转帖]一次针对Not in的优化--如何让其选择hash join anti        下一篇 
derek
注册用户
等级:中校
经验:1550
发帖:209
精华:0
注册:2011-7-21
状态:离线
发送短消息息给derek 加好友    发送短消息息给derek 发消息
发表于: IP:您无权察看 2011-8-1 21:44:34 | [全部帖] [楼主帖] 楼主

首先来看两个sql,返回结果相同,但是耗时差别很大

SQL> select count(*)

2             from justin_good r

3            where not exists

4                  (select 'x' from justin_count pc where pc.id = r.justin_good_id)

5  ;

COUNT(*)

----------

7229

Executed in 3.437 seconds

SQL> select count(*)

2             from justin_good r

3            where r.justin_good_id not in

4                  (select pc.id from justin_count pc)

5  ;

COUNT(*)

----------

7229

Executed in 128.203 seconds

再来看一下它们的执行计划

使用not exist的语句cost为3452,而not in 的却达到14216

SQL> explain plan for select count(*)

2             from justin_good r

3            where not exists

4                  (select 'x' from justin_count pc where pc.id = r.justin_good_id);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1087925722

----------------------------------------------------------------------------------------

| Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |               |     1 |     9 |  3452   (2)| 00:00:42 |

|   1 |  SORT AGGREGATE        |               |     1 |     9 |            |          |

|*  2 |   HASH JOIN RIGHT ANTI |               |    59 |   531 |  3452   (2)| 00:00:42 |

|   3 |    INDEX FAST FULL SCAN| PK11_1        |  4562 | 22810 |     4   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL   | justin_good   |   602K|  2355K|  3440   (2)| 00:00:42 |

----------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("PC"."ID"="R"."justin_good_id")

16 rows selected.

SQL> explain plan for select count(*)

2             from justin_good r

3            where r.justin_good_id not in

4                  (select pc.id from justin_count pc);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 4119029611

-------------------------------------------------------------------------------------

| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |               |     1 |     4 | 14216   (2)| 00:02:51 |

|   1 |  SORT AGGREGATE     |               |     1 |     4 |            |          |

|*  2 |   FILTER            |               |       |       |            |          |

|   3 |    TABLE ACCESS FULL| justin_good   |   602K|  2355K|  3442   (2)| 00:00:42 |

|*  4 |    INDEX FULL SCAN  | PK11_1        |     1 |     5 |    11   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter( NOT EXISTS (SELECT 0 FROM "justin"."justin_count" "PC" WHERE

LNNVL("PC"."ID"<>:B1)))

4 - filter(LNNVL("PC"."ID"<>:B1))

18 rows selected.

可以看到使用not exist的sql采用了hash join anti,而not in的却使用了filter

改写一下语句,确保justin_good_id不会在查询中返回NULL

SQL> set linesize 300

SQL> explain plan for select count(*)

2             from justin_good r

3            where nvl(r.justin_good_id,'NULL') not in

4                  (select pc.id from justin_count pc);

Explained.

ts




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论