当前位置:首页 > 开发 > 数据库 > 正文

SEMI-JOIN执行计划突然变成HASH JOIN了 的原因分析

发表于: 2013-08-06   作者:cwqcwqmax9   来源:转载   浏览:
摘要: 甲说: A B两个表总数据量都很大,在百万以上。 idx1  idx2字段表示是索引字段 A B 两表上都有 col1字段表示普通字段 select xxx from A where A.idx1 between mmm and nnn      and exists (select 1 from B where B.idx2 =
甲说:
A B两个表总数据量都很大,在百万以上。
idx1  idx2字段表示是索引字段
A B 两表上都有
col1字段表示普通字段

select xxx from A
where A.idx1 between mmm and nnn
     and exists (select 1 from B where B.idx2 = A.col1)

满足A.idx1 between mmm and nnn条件的行一般是几百到几千行,选择性还可以。B表上idx2字段选择性很高。

以前执行计划是 NESTED LOOP-SEMI-JOIN,效率很高。
今天数据库负载突增,最后发现是该语句的执行计划变成 HASH JOIN-SEMI-JOIN,也就意味着对B表进行table access full!

无奈之下,修改查询语句为
... and exists (select  /*+ NL_SJ */ 1 from B where...

哪位能指点一下为什么执行计划会变得极差?



乙说:从新分析一下表在查询看看



甲说:
这是未加提示的语句及执行计划,走了HASH JOIN SEMI。[/COLOR]
[PHP]
SQL> explain plan for
  2  select count(*)
  3    from RECORD_TEMP_A t
  4   where 1 = 1
  5     and t.datsendtime >= sysdate - 20
  6     and t.datsendtime < sysdate - 10
  7     AND EXISTS (SELECT 1
  8            FROM RECORD_TEMP_B partition(P_MAXVALUE) V
  9           WHERE V.MSGID = T.MSGID);

已解释。

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

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             |  Name                    | Rows  | Bytes | Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                          |     1 |    64 | 17103 |       |       |
|   1 |  SORT AGGREGATE                       |                          |     1 |    64 |       |       |       |
|*  2 |   FILTER                              |                          |       |       |       |       |       |
|*  3 |    HASH JOIN SEMI                     |                          | 15167 |   947K| 17103 |       |       |
|   4 |     TABLE ACCESS BY GLOBAL INDEX ROWID| RECORD_TEMP_A            | 22142 |   648K| 10881 | ROWID | ROW L |
|*  5 |      INDEX RANGE SCAN                 | IDX_REC_TEMP_A_SENDTIME  | 39856 |       |   464 |       |       |
|*  6 |     TABLE ACCESS FULL                 | RECORD_TEMP_B            |   682K|    22M|  5779 |    24 |    24 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
省略...

Note: cpu costing is off

已选择22行。
[/PHP]


这是加了NL_SJ提示的语句及执行计划,走了NESTED LOOPS SEMI。[/COLOR]
[PHP]
SQL> explain plan for
  2  select count(*)
  3    from RECORD_TEMP_A t
  4   where 1 = 1
  5     and t.datsendtime >= sysdate - 20
  6     and t.datsendtime < sysdate - 10
  7     AND EXISTS (SELECT /*+ NL_SJ */ 1
  8            FROM RECORD_TEMP_B partition(P_MAXVALUE) V
  9           WHERE V.MSGID = T.MSGID);

已解释。

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

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             |  Name                    | Rows  | Bytes | Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                          |     1 |    64 | 77307 |       |       |
|   1 |  SORT AGGREGATE                       |                          |     1 |    64 |       |       |       |
|*  2 |   FILTER                              |                          |       |       |       |       |       |
|   3 |    NESTED LOOPS SEMI                  |                          | 15167 |   947K| 77307 |       |       |
|   4 |     TABLE ACCESS BY GLOBAL INDEX ROWID| RECORD_TEMP_A            | 22142 |   648K| 10881 | ROWID | ROW L |
|*  5 |      INDEX RANGE SCAN                 | IDX_REC_TEMP_A_SENDTIME  | 39856 |       |   464 |       |       |
|*  6 |     TABLE ACCESS BY GLOBAL INDEX ROWID| RECORD_TEMP_B            |   467K|    15M|     3 |    24 |    24 |
|*  7 |      INDEX RANGE SCAN                 | IDX_REC_B_MSGID          |     1 |       |     2 |       |       |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
省略...

Note: cpu costing is off

已选择23行。

SQL> spool off

乙说:

问题应该在这里
TABLE ACCESS BY GLOBAL INDEX ROWID| RECORD_TEMP_A            | 22142
|*  5 |      INDEX RANGE SCAN                 | IDX_REC_TEMP_A_SENDTIME  | 39856 |     

这里外表评估的cardinality是错误的,跟楼主说的“满足A.idx1 between mmm and nnn条件的行一般是几百到几千行,选择性还可以”严重不符

内表结果集2万多,外表68万,CBO选择hash join无可厚非

SEMI-JOIN执行计划突然变成HASH JOIN了 的原因分析

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
SQL server 内部实现了三种类型的内连接运算,大多数人从来没有听说过这些连接类型,因为它们不是逻
当一个查询到达数据库引擎时,SQL Server执行两个主要的步骤来产生期望的查询结果:   第一步:查
http://www.cnblogs.com/rootq/archive/2008/09/06/1285779.html ORACLE 执行计划分析 一、什么是执
 一直想找一些关于SQL语句性能调试的权威参考,但是有参考未必就能够做好调试的工作。我深信实践中
 一直想找一些关于SQL语句性能调试的权威参考,但是有参考未必就能够做好调试的工作。我深信实践中
一直想找一些关于SQL语句性能调试的权威参考,但是有参考未必就能够做好调试的工作。我深信实践中得
Oracle有多种方式获得一条SQL语句的查询计划,比如使用explain plan命令,在PLSQL Developer里面按F
本文分析基于Nginx-1.2.6,与旧版本或将来版本可能有些许出入,但应该差别不大,可做参考 上篇对ngi
HashMap 中hash table 定位算法: Java代码 int hash = hash(key.hashCode()); int i = indexFor(ha
HashMap 中hash table 定位算法: int hash = hash(key.hashCode()); int i = indexFor(hash, table.
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号