楼主: hdydmichael

帮忙优化一条语句

[复制链接]
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 2010-8-17 12:35 | 显示全部楼层
原帖由 tom_fans 于 2010-8-17 12:30 发表
我不明白为什么 NULL就不能走? 这绝对是BUG。

你解释下为什么NULL不能走? 从原理上难道HASH算法行不通?

这个我和别人讨论过,大概是算法的原因,具体不是很清楚,不是BUG,老外也研究过那3个hint就有null不凑效的问题,一个老外文章中的warning

WARNING: The anti-join hints merge_aj and hash_aj will only work if the column requested in the not in clause has a NOT NULL constraint.

使用道具 举报

回复
论坛徽章:
9
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:00ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51蛋疼蛋
日期:2011-08-09 14:26:55ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥迪
日期:2013-09-12 15:57:042014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2010-8-17 12:35 | 显示全部楼层
DINGJUN123>select * from t where t.object_name not in (select object_name from t1);

已选择13204行。


执行计划
----------------------------------------------------------
Plan hash value: 1155274866

  1. ---------------------------------------------------------------------------
  2. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  3. ---------------------------------------------------------------------------
  4. |   0 | SELECT STATEMENT   |      | 14214 |  1207K| 65236   (1)| 00:13:03 |
  5. |*  1 | FILTER            |      |       |       |            |          |
  6. |   2 |   TABLE ACCESS FULL| T    | 14215 |  1207K|    56   (2)| 00:00:01 |
  7. |*  3 |   TABLE ACCESS FULL| T1   |     1 |    15 |     6   (0)| 00:00:01 |
  8. ---------------------------------------------------------------------------
复制代码


从原理说说NULL为什么就不走HASH。

在ORACLE里NULL !=NULL, ORACLE其实可以把NULL就当作一个字符或者其他。 完全可以按照HASH算法来走。11G已经可以走了,这只能说这是ORACLE版本的BUG。

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 2010-8-17 12:38 | 显示全部楼层
原帖由 tom_fans 于 2010-8-17 12:35 发表
DINGJUN123>select * from t where t.object_name not in (select object_name from t1);

已选择13204行。


执行计划
----------------------------------------------------------
Plan hash value: 1155274866

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 14214 |  1207K| 65236   (1)| 00:13:03 |
|*  1 | FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T    | 14215 |  1207K|    56   (2)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |     1 |    15 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------


从原理说说NULL为什么就不走HASH。

在ORACLE里NULL !=NULL, ORACLE其实可以把NULL就当作一个字符或者其他。 完全可以按照HASH算法来走。11G已经可以走了,这只能说这是ORACLE版本的BUG。


11G没有测试过,晚上回去测试一把,也不一定是BUG,CBO逐步在改进,越来越聪明了

使用道具 举报

回复
论坛徽章:
9
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:00ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51蛋疼蛋
日期:2011-08-09 14:26:55ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥迪
日期:2013-09-12 15:57:042014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2010-8-17 12:38 | 显示全部楼层

回复 #21 dingjun123 的帖子

如果anti_join,semi_join有这种差别,那HASH JOIN有吗? 如果有的话,不是NOT NULL的连HASH JOIN也走不成? 我目前还没碰到过这种CASE。

如果HASH JOIN可以允许NULL, anti_join不允许,我不认为这是应该存在的,否者11G没必要改进。

使用道具 举报

回复
论坛徽章:
9
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:00ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51蛋疼蛋
日期:2011-08-09 14:26:55ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥迪
日期:2013-09-12 15:57:042014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2010-8-17 12:40 | 显示全部楼层

回复 #23 dingjun123 的帖子

当然说BUG或许有点勉强。 其实我一直理解不了为什么NULL就不走HASH_AJ。  理解不了。

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 2010-8-17 12:47 | 显示全部楼层
原帖由 tom_fans 于 2010-8-17 12:40 发表
当然说BUG或许有点勉强。 其实我一直理解不了为什么NULL就不走HASH_AJ。  理解不了。

不知道具体算法实现啊,只能猜测,呵呵

使用道具 举报

回复
论坛徽章:
9
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:00ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51蛋疼蛋
日期:2011-08-09 14:26:55ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥迪
日期:2013-09-12 15:57:042014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2010-8-17 12:50 | 显示全部楼层
哈哈哈。

但是事实HASH JOIN不存在这种限制。 以为你知道可以指点一下。还是没答案。哈哈哈

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 2010-8-17 12:57 | 显示全部楼层
原帖由 tom_fans 于 2010-8-17 12:50 发表
哈哈哈。

但是事实HASH JOIN不存在这种限制。 以为你知道可以指点一下。还是没答案。哈哈哈

CBO从傻到精明,刚找到一个10G到11G变化的文档,上面有11G null-aware 就是说这个的

twp-upgrading-10g-to-11g-what-to-ex-133707.pdf

1.25 MB, 下载次数: 12

使用道具 举报

回复
论坛徽章:
9
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:00ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51蛋疼蛋
日期:2011-08-09 14:26:55ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥迪
日期:2013-09-12 15:57:042014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2010-8-17 13:02 | 显示全部楼层
这个我知道,所以我就是针对ORACLE以前版本。 我就是他NND不知道为什么以前就不行。 虽然我们不知道他的算法,但是这种东西肯定可以解决,ORACLE竟然到11G才解决。我很气愤。

使用道具 举报

回复
论坛徽章:
86
2015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11优秀写手
日期:2013-12-18 09:29:11日产
日期:2013-10-17 08:44:39马自达
日期:2013-08-26 16:28:022013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-23 16:55:51马上有房
日期:2014-02-19 11:55:14
发表于 2010-8-17 13:03 | 显示全部楼层
估计是这个问题人神共愤,被Oracle重点处理了。呵呵。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select object_id,count(*) from x group by object_id;

OBJECT_ID   COUNT(*)
---------- ----------
                    2
         2          1

SQL> select * from t where t.object_id not in (select object_id from x);

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 4056177348
--------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      | 64257 |    13M|   250   (2)| 00:00:04 |
|*  1 |  HASH JOIN RIGHT ANTI NA|      | 64257 |    13M|   250   (2)| 00:00:04 |
|   2 |   TABLE ACCESS FULL     | X    |     3 |    39 |     4   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL     | T    | 64257 |    12M|   245   (2)| 00:00:04 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."OBJECT_ID"="OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement (level=4)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       1184  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select * from t where not exists (select 1 from x where t.object_id=x.objec
t_id);

已选择64963行。

执行计划
----------------------------------------------------------
Plan hash value: 193404642

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 64257 |    13M|   250   (2)| 00:00:04 |
|*  1 |  HASH JOIN RIGHT ANTI|      | 64257 |    13M|   250   (2)| 00:00:04 |
|   2 |   TABLE ACCESS FULL  | X    |     3 |    39 |     4   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL  | T    | 64257 |    12M|   245   (2)| 00:00:04 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."OBJECT_ID"="X"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement (level=4)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5196  consistent gets
          0  physical reads
          0  redo size
    3124663  bytes sent via SQL*Net to client
      48046  bytes received via SQL*Net from client
       4332  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      64963  rows processed

SQL> select * from t where t.object_id not in (select object_id from x where x.o
bject_id is not null);

已选择64963行。

执行计划
----------------------------------------------------------
Plan hash value: 3211237403

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      | 64257 |    13M|   250   (2)| 00:00:04|
|*  1 |  HASH JOIN RIGHT ANTI SNA|      | 64257 |    13M|   250   (2)| 00:00:04|
|*  2 |   TABLE ACCESS FULL      | X    |     1 |    13 |     4   (0)| 00:00:01|
|   3 |   TABLE ACCESS FULL      | T    | 64257 |    12M|   245   (2)| 00:00:04|
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."OBJECT_ID"="OBJECT_ID")
   2 - filter("X"."OBJECT_ID" IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement (level=4)

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5196  consistent gets
          0  physical reads
          0  redo size
    3124663  bytes sent via SQL*Net to client
      48046  bytes received via SQL*Net from client
       4332  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      64963  rows processed

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表