查看: 3971|回复: 10

oracle优化器里面怎样更好地得到多个表之间结果集关联的估算

[复制链接]
招聘 : c/c++研发
认证徽章
论坛徽章:
45
技术图书徽章
日期:2014-03-10 14:09:192012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15现任管理团队成员
日期:2011-05-07 01:45:082011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:50
发表于 2011-6-21 09:09 | 显示全部楼层 |阅读模式
第一次在oracle坛子里发帖,如果问题太愚昧请见谅哈

现在假设我有这么一个查询
select * from t1, t2, t3, t4, t5
where
t1.c1=t2.c1 and
t1.c2=t3.c2 and
t1.c3=t3.c3 and
t1.c4=t3.c4 and
t3.c1=t4.c1 and
t3.c2=t4.c2 and
t4.c5=t5.c5 and
t4.c6=t5.c6 and
xxxx (一些本地谓词,非关联谓词)

其中t1是fact table,t2, t3是dimension,不过t4和t5与fact表没有直接关联,而是t4与t5关联,t3与t4关联。。。在t2,t3与t1上面有FK关联,但是t3,t4,t5之间没有这种联系。表示出来就是大概这种形式

t5<->t4<->t3<-FK->t1<-FK->t2

现在我想要做的是t1先与t2做NLJOIN,然后结果集与t3做NLJOIN,然后结果集再与t4,t5做join。

但是实际上oracle的优化器选择了(t5,t4,t3)先做完一个join,其估算的输出为150行左右,然后用这个结果集与(t1,t2)的结果集进行NLJOIN关联。但是实际上t5,t4,t3三个表之间的关联输出了50万行,因此这样的效率非常低(跑半个小时没动静,但是如果用我想要的那种关联方式,10秒钟就得到结果了)。

现在看起来是这三个表之间的关联存在数据倾斜,但是每个表的column都收集了详细的统计信息,而且本地谓词的应用可以得到正确的estimation,就是三个表之间关联的结果集的行数明显低估了~~~

不知道俺说明白了木有,总体上俺就是想看看有没有类似statistical view这种可以对一个view (不是materialized view)的各个行收集统计信息,然后告诉优化器”如果你选择这三个表join,得到的结果集很大哦“~~~~

不知道诸位大神有什么好的方法

[ 本帖最后由 wangzhonnew 于 2011-6-21 10:10 编辑 ]
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2011-6-21 09:12 | 显示全部楼层
oracle,sql server没有办法

使用道具 举报

回复
招聘 : c/c++研发
认证徽章
论坛徽章:
45
技术图书徽章
日期:2014-03-10 14:09:192012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15现任管理团队成员
日期:2011-05-07 01:45:082011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:50
发表于 2011-6-21 09:18 | 显示全部楼层
原帖由 棉花糖ONE 于 2011-6-21 10:12 发表
oracle,sql server没有办法

  不是吧~~~ 那我能不能用hint直接指定那几个表之间关联呢?我试了USE_NL之类的,但是好像是只能说(我要对t1,t2两个表在关联时都与其他任何可能关联的组合使用NLJOIN),而不能说(我想让t1 NLJOIN t2,然后结果集再与别的join)~~~

使用道具 举报

回复
招聘 : c/c++研发
认证徽章
论坛徽章:
45
技术图书徽章
日期:2014-03-10 14:09:192012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15现任管理团队成员
日期:2011-05-07 01:45:082011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:50
发表于 2011-6-21 09:21 | 显示全部楼层
比如我想要
HSJOIN(HSJOIN(NLJOIN(NLJOIN(t1, t2), t3), t4), t5)

使用道具 举报

回复
招聘 : c/c++研发
认证徽章
论坛徽章:
45
技术图书徽章
日期:2014-03-10 14:09:192012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15现任管理团队成员
日期:2011-05-07 01:45:082011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:50
发表于 2011-6-21 09:24 | 显示全部楼层
或者能不能用selectivity的hint指定这三个t3,t4,t5之间的结果集的估算大小呢?我简单试了一下
/*+ SELECTIVITY (t3 t4 t5 1.0) */ 好像不工作,估算没有任何变化~~~是不是格式不对啊,google上找不到好的例子

使用道具 举报

回复
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2011-6-21 09:30 | 显示全部楼层
这个hint比较难,oracle没有selectity,但是有cardinality指定返回行数

使用道具 举报

回复
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2011-6-21 09:35 | 显示全部楼层
oracle 10g,有个hint opt_estimate可以指定join返回的行数,但是这个的缺陷是不同的sql 很难搞

使用道具 举报

回复
招聘 : c/c++研发
认证徽章
论坛徽章:
45
技术图书徽章
日期:2014-03-10 14:09:192012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15现任管理团队成员
日期:2011-05-07 01:45:082011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:50
发表于 2011-6-21 09:35 | 显示全部楼层
哦,我好像google上看到selectivity是未公开的~~~ anyway,用cardinality能指定多个表之间join后的结果集大小么?俺要的就是得到一个正确的estimation啊~~~

使用道具 举报

回复
招聘 : c/c++研发
认证徽章
论坛徽章:
45
技术图书徽章
日期:2014-03-10 14:09:192012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15现任管理团队成员
日期:2011-05-07 01:45:082011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:50
发表于 2011-6-21 09:38 | 显示全部楼层
opt_estimate的格式:
Usage

Parameters separate by space (optionally by comma). “[]” means that “query block” is optional.

/*+ OPT_ESTIMATE( [query block] operation_type identifier adjustment ) */

    query block

    optional and in the format “@QB_NAME”. For example, in the lack of any QB_NAME hints, you could say: OPT_ESTIMATE(@SEL$1 TABLE….)
    operation_type

    QUERY_BLOCK
    unknown

    TABLE
    refers to table

    Example:
    1
    2
           
    select /*+OPT_ESTIMATE(TABLE DD ROWS=10)*/
    count(*) from dual DD

    INDEX_FILTER
    adjusts index filtering – number of rows produced by the index lookup, does not affect the cost to scan the index

    INDEX_SCAN / INDEX_SKIP_SCAN
    For index range or skip scans – how many rows will be scanned in the index – affects the COST of the index access and the number of rows outputted

    JOIN
    the join result of 2 tables – oracle joins 2 tables at a time. Note that you can adjust join cardinalities between 2 tables in order to make that join not to be used.


好像只能指定2个表之间的join?

使用道具 举报

回复
招聘 : c/c++研发
认证徽章
论坛徽章:
45
技术图书徽章
日期:2014-03-10 14:09:192012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15现任管理团队成员
日期:2011-05-07 01:45:082011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:50
发表于 2011-6-21 10:38 | 显示全部楼层
顶,期待高人

使用道具 举报

回复

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

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


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

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