ITPUB论坛-中国专业的IT技术社区

 找回密码
 注册
查看: 525|回复: 8

多表连接 cardinality 估算不准

[复制链接]
论坛徽章:
289
蛋疼蛋
日期:2013-03-29 13:46:58优秀写手
日期:2013-12-24 06:00:12福特
日期:2014-02-17 17:30:59生肖徽章:兔
日期:2012-05-24 19:03:36SQL极客
日期:2013-12-09 14:13:35ITPUB季度 技术新星
日期:2014-02-24 11:00:06IT宝贝
日期:2014-08-27 10:32:17马上加薪
日期:2014-08-05 09:18:33SQL数据库编程大师
日期:2016-01-13 10:30:43玉石琵琶
日期:2014-03-04 16:46:07
跳转到指定楼层
1#
发表于 2018-7-5 14:22 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
create table t3 as select * from dba_objects;

select * from t1,t2,t3
where t1.object_id=t2.object_id
and t3.object_id=t1.object_id
and t3.object_name=t2.object_name
;
T3表同时有T1和T2的连接条件。

Execution Plan
----------------------------------------------------------
Plan hash value: 1487401159

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     2 |   714 |       |  4057   (1)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     2 |   714 |    12M|  4057   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | T3   | 92028 |    11M|       |   430   (1)| 00:00:01 |
|*  3 |   HASH JOIN         |      | 92024 |    20M|    11M|  1967   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   | 92027 |    10M|       |   430   (1)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| T1   | 92026 |    10M|       |   430   (1)| 00:00:01 |
------------------------------------------------------------------------------------

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

   1 - access("T3"."OBJECT_ID"="T1"."OBJECT_ID" AND
              "T3"."OBJECT_NAME"="T2"."OBJECT_NAME")
   3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")


select count(*) from t1,t2,t3
where t1.object_id=t2.object_id
and t3.object_id=t1.object_id
and t3.object_name=t2.object_name
; -- 92024


请问为什么会估算不准
论坛徽章:
289
蛋疼蛋
日期:2013-03-29 13:46:58优秀写手
日期:2013-12-24 06:00:12福特
日期:2014-02-17 17:30:59生肖徽章:兔
日期:2012-05-24 19:03:36SQL极客
日期:2013-12-09 14:13:35ITPUB季度 技术新星
日期:2014-02-24 11:00:06IT宝贝
日期:2014-08-27 10:32:17马上加薪
日期:2014-08-05 09:18:33SQL数据库编程大师
日期:2016-01-13 10:30:43玉石琵琶
日期:2014-03-04 16:46:07
2#
 楼主| 发表于 2018-7-5 14:24 | 只看该作者
这样就能估准

select * from
(
select /*+no_merge*/ t1.object_id,t2.object_name from t1,t2
where t1.object_id=t2.object_id
) a,t3
where
t3.object_id=a.object_id
and t3.object_name = a.object_name;

Execution Plan
----------------------------------------------------------
Plan hash value: 487419594

-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 92024 |    18M|       |  2293   (1)| 00:00:01 |
|*  1 |  HASH JOIN           |      | 92024 |    18M|  8184K|  2293   (1)| 00:00:01 |
|   2 |   VIEW               |      | 92024 |  7099K|       |   860   (1)| 00:00:01 |
|*  3 |    HASH JOIN         |      | 92024 |  3145K|       |   860   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   | 92026 |   449K|       |   430   (1)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2   | 92027 |  2696K|       |   430   (1)| 00:00:01 |
|   6 |   TABLE ACCESS FULL  | T3   | 92028 |    11M|       |   430   (1)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - access("T3"."OBJECT_ID"="A"."OBJECT_ID" AND
              "T3"."OBJECT_NAME"="A"."OBJECT_NAME")
   3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

使用道具 举报

回复
论坛徽章:
289
蛋疼蛋
日期:2013-03-29 13:46:58优秀写手
日期:2013-12-24 06:00:12福特
日期:2014-02-17 17:30:59生肖徽章:兔
日期:2012-05-24 19:03:36SQL极客
日期:2013-12-09 14:13:35ITPUB季度 技术新星
日期:2014-02-24 11:00:06IT宝贝
日期:2014-08-27 10:32:17马上加薪
日期:2014-08-05 09:18:33SQL数据库编程大师
日期:2016-01-13 10:30:43玉石琵琶
日期:2014-03-04 16:46:07
3#
 楼主| 发表于 2018-7-5 14:25 | 只看该作者
当然像下面,如果T3上只有一个条件也是能估准的

select * from t1,t2,t3
where t1.object_id=t2.object_id
and t3.object_id=t1.object_id

使用道具 举报

回复
论坛徽章:
289
蛋疼蛋
日期:2013-03-29 13:46:58优秀写手
日期:2013-12-24 06:00:12福特
日期:2014-02-17 17:30:59生肖徽章:兔
日期:2012-05-24 19:03:36SQL极客
日期:2013-12-09 14:13:35ITPUB季度 技术新星
日期:2014-02-24 11:00:06IT宝贝
日期:2014-08-27 10:32:17马上加薪
日期:2014-08-05 09:18:33SQL数据库编程大师
日期:2016-01-13 10:30:43玉石琵琶
日期:2014-03-04 16:46:07
4#
 楼主| 发表于 2018-7-5 16:44 | 只看该作者
为啥要这么算
Table Stats::
  Table: T3  Alias: T3
  #Rows: 92028  SSZ: 0  LGR: 0  #Blks:  1578  AvgRowLen:  127.00  NEB: 0  ChainCnt:  0.00  SPC: 0  RFL: 0  RNF: 0  CBK: 0  CHR: 0  KQDFLG: 1
  #IMCUs: 0  IMCRowCnt: 0  IMCJournalRowCnt: 0  #IMCBlocks: 0  IMCQuotient: 0.000000
  Column (#4): OBJECT_ID(NUMBER)
    AvgLen: 5 NDV: 92026 Nulls: 2 Density: 0.000011 Min: 0.000000 Max: 2.000000
  Column (#2): OBJECT_NAME(VARCHAR2)
    AvgLen: 25 NDV: 54233 Nulls: 0 Density: 0.000018
***********************
Table Stats::
  Table: T2  Alias: T2
  #Rows: 92027  SSZ: 0  LGR: 0  #Blks:  1578  AvgRowLen:  115.00  NEB: 0  ChainCnt:  0.00  SPC: 0  RFL: 0  RNF: 0  CBK: 0  CHR: 0  KQDFLG: 1
  #IMCUs: 0  IMCRowCnt: 0  IMCJournalRowCnt: 0  #IMCBlocks: 0  IMCQuotient: 0.000000
  Column (#4): OBJECT_ID(NUMBER)
    AvgLen: 5 NDV: 92025 Nulls: 2 Density: 0.000011 Min: 0.000000 Max: 2.000000
  Column (#2): OBJECT_NAME(VARCHAR2)
    AvgLen: 25 NDV: 54232 Nulls: 0 Density: 0.000018
***********************
Table Stats::
  Table: T1  Alias: T1
  #Rows: 92026  SSZ: 0  LGR: 0  #Blks:  1578  AvgRowLen:  115.00  NEB: 0  ChainCnt:  0.00  SPC: 0  RFL: 0  RNF: 0  CBK: 0  CHR: 0  KQDFLG: 1
  #IMCUs: 0  IMCRowCnt: 0  IMCJournalRowCnt: 0  #IMCBlocks: 0  IMCQuotient: 0.000000
  Column (#4): OBJECT_ID(NUMBER)
    AvgLen: 5 NDV: 92024 Nulls: 2 Density: 0.000011 Min: 0.000000 Max: 2.000000
第一次join
Join Card:  92024.000000 = outer (92026.000000) * inner (92027.000000) * sel (1.0866e-005)
第二次join
Join Card:  1.696790 = outer (92024.000000) * inner (92028.000000) * sel (2.0036e-010)
Join Card - Rounded: 2 Computed: 1.696790
92024*92028*(1/92026*1/54233) = 1.696790

使用道具 举报

回复
论坛徽章:
496
紫蜘蛛
日期:2007-09-26 17:05:56奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:53
5#
发表于 2018-7-5 21:37 | 只看该作者
CBO并不知道t1和t2之间的object_name和object_id是有对应关系的。2楼那个能算准比较奇怪。

使用道具 举报

回复
论坛徽章:
289
蛋疼蛋
日期:2013-03-29 13:46:58优秀写手
日期:2013-12-24 06:00:12福特
日期:2014-02-17 17:30:59生肖徽章:兔
日期:2012-05-24 19:03:36SQL极客
日期:2013-12-09 14:13:35ITPUB季度 技术新星
日期:2014-02-24 11:00:06IT宝贝
日期:2014-08-27 10:32:17马上加薪
日期:2014-08-05 09:18:33SQL数据库编程大师
日期:2016-01-13 10:30:43玉石琵琶
日期:2014-03-04 16:46:07
6#
 楼主| 发表于 2018-7-6 11:24 | 只看该作者
newkid 发表于 2018-7-5 21:37
CBO并不知道t1和t2之间的object_name和object_id是有对应关系的。2楼那个能算准比较奇怪。

2楼的 JSEL 用的colgroups,不知道怎么算出来的
Join selectivity using 1 ColGroups: 1.0866e-005 (sel1 = 0.000000, sel2 = 0.000000)
Join Card:  92023.999913 = outer (92022.000000) * inner (92028.000000) * sel (1.0866e-005)
Join Card - Rounded: 92024 Computed: 92023.999913

使用道具 举报

回复
论坛徽章:
289
蛋疼蛋
日期:2013-03-29 13:46:58优秀写手
日期:2013-12-24 06:00:12福特
日期:2014-02-17 17:30:59生肖徽章:兔
日期:2012-05-24 19:03:36SQL极客
日期:2013-12-09 14:13:35ITPUB季度 技术新星
日期:2014-02-24 11:00:06IT宝贝
日期:2014-08-27 10:32:17马上加薪
日期:2014-08-05 09:18:33SQL数据库编程大师
日期:2016-01-13 10:30:43玉石琵琶
日期:2014-03-04 16:46:07
7#
 楼主| 发表于 2018-7-6 11:37 | 只看该作者
本帖最后由 demonat 于 2018-7-6 11:42 编辑
demonat 发表于 2018-7-6 11:24
2楼的 JSEL 用的colgroups,不知道怎么算出来的
Join selectivity using 1 ColGroups: 1.0866e-005 (sel ...

  Column (#19):
    NewDensity:0.000011, OldDensity:0.000011 BktCnt:75.000000, PopBktCnt:0.000000, PopValCnt:0, NDV:92026
  Column (#19): SYS_STU95JB#LORF7835KVN$6IGW1J(NUMBER)
    AvgLen: 12 NDV: 92026 Nulls: 0 Density: 0.000011 Min: 0.000000 Max: 77633.000000
    Histogram: HtBal  #Bkts: 75  UncompBkts: 75  EndPtVals: 76  ActualVal: yes
  ColGroup (#1, VC) SYS_STU95JB#LORF7835KVN$6IGW1J
    Col#: 2 4    CorStregth: 54233.00
Join ColGroups for <unnamed>[] and T3[T3] : (#0, #1)

select data_default from dba_tab_cols where column_name = 'SYS_STU95JB#LORF7835KVN$6IGW1J';
SYS_OP_COMBINED_HASH("OBJECT_ID","OBJECT_NAME")

系统生成了一个列,貌似从这个列上拿的统计信息
好像是因为我之前收集了扩展统计信息?

使用道具 举报

回复
论坛徽章:
289
蛋疼蛋
日期:2013-03-29 13:46:58优秀写手
日期:2013-12-24 06:00:12福特
日期:2014-02-17 17:30:59生肖徽章:兔
日期:2012-05-24 19:03:36SQL极客
日期:2013-12-09 14:13:35ITPUB季度 技术新星
日期:2014-02-24 11:00:06IT宝贝
日期:2014-08-27 10:32:17马上加薪
日期:2014-08-05 09:18:33SQL数据库编程大师
日期:2016-01-13 10:30:43玉石琵琶
日期:2014-03-04 16:46:07
8#
 楼主| 发表于 2018-7-6 11:44 | 只看该作者
删掉统计信息后也能估准

Join ColGroups for <unnamed>[] and T3[T3] : Using cdn sanity check

ColGroup cardinality sanity check: ndv for  T3[T3] = 92036.000000
Join selectivity using 1 ColGroups: 1.0865e-005 (sel1 = 0.000000, sel2 = 0.000000)
Join Card:  92030.000000 = outer (92030.000000) * inner (92036.000000) * sel (1.0865e-005)

使用道具 举报

回复
论坛徽章:
496
紫蜘蛛
日期:2007-09-26 17:05:56奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:53
9#
发表于 2018-7-7 02:32 | 只看该作者
demonat 发表于 2018-7-6 11:24
2楼的 JSEL 用的colgroups,不知道怎么算出来的
Join selectivity using 1 ColGroups: 1.0866e-005 (sel ...

看来它是对子查询的结果做了动态采样并且自动创建了列群组并收集了扩展统计信息。

使用道具 举报

回复

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

本版积分规则

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