ITPub博客

首页 > Linux操作系统 > Linux操作系统 > in_list查询与or扩展

in_list查询与or扩展

原创 Linux操作系统 作者:wei-xh 时间:2011-12-08 15:58:22 0 删除 编辑
select t.COMPANY_ID, t.GMT_CREATE, t.GMT_MODIFIED, t.PRODUCT_ID
  from t
 where (t.PRODUCT_ID) in
       (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50)
在没有统计信息的情况下,9I会走or扩展,rule规则产生的执行计划。这种执行计划的效率没有IN_LIST查询高,而且执行计划会非常的长。占用比较大的共享池。
如果想走 in_list查询,可以尝试如下几种办法:
1)收集统计信息,那么就会采用CBO去产生执行计划,CBO会有限选择IN_LIST查询
2)增加HINT,增加有效的HINT后,也会打破RULE规则,而去使用CBO去产生执行计划。有如下两个HINT可以参考:
select /*+ index(t index_name) */
 t.COMPANY_ID, t.GMT_CREATE, t.GMT_MODIFIED, t.PRODUCT_ID
  from t
 where (t.PRODUCT_ID) in
       (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50);
 
select /*+ NO_EXPAND */ t.COMPANY_ID, t.GMT_CREATE, t.GMT_MODIFIED, t.PRODUCT_ID
  from t
 where (t.PRODUCT_ID) in
      (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50);

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-712965/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2285227