查看: 3273|回复: 12

这个语句还能优化嘛?

[复制链接]
认证徽章
论坛徽章:
9
ITPUB社区OCM联盟徽章
日期:2013-03-27 11:17:11奥运纪念徽章
日期:2013-06-18 09:13:52ITPUB社区千里马徽章
日期:2013-08-22 09:58:03大众
日期:2013-08-30 14:51:33路虎
日期:2013-12-01 18:25:42
发表于 2011-8-29 16:13 | 显示全部楼层 |阅读模式
原语句:
select b.*
    from test1 a,test2 b
   where a.sflag ='-3'
     and instr(','||a.id||',',','||b.certiid||',')>0;

   通过执行发现test2表执行了全表扫描,需要120秒通过分析发现是test2的certiid列上实际上是有索引的。
但是它不能使用这个索引,不能通过索引的ROWID来搜索表,因为这里并没有b.certiid等于的条件,而是通过
条件instr(','||a.id||',',','||b.certiid||',')>0进行的连接。
   然后考虑到需要返回的值是B.*而索引只包含了CERTIID列,索引INDEX FAST SCAN也用不到。
   所以我们这里考虑修改如下:
   select * from  test2 where certiid in(
    select b.certiid
    from test1 a,test2 b
     where a.sflag ='-3'
     and instr(','||a.id||',',','||b.certiid||',')>0);
     
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  gsha1gj68gacg, child number 0
-------------------------------------
select /*+  gather_plan_statistics */  *   from test2  where certiid in        (select
b.certiid           from test1 a, test2 b          where a.sflag = '-3'            and
instr(',' || a.id || ',', ',' || b.certiid || ',') > 0)

Plan hash value: 4074250259

----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |      1 |        |     54 |00:00:32.69 |     133K|
|   1 |  NESTED LOOPS                   |                 |      1 |      1 |     54 |00:00:32.69 |     133K|
|   2 |   VIEW                          | VW_NSO_1        |      1 |      1 |     54 |00:00:32.69 |     133K|
|   3 |    HASH UNIQUE                  |                 |      1 |      1 |     54 |00:00:32.69 |     133K|
|   4 |     NESTED LOOPS                |                 |      1 |      1 |     54 |00:00:28.67 |     133K|
|   5 |      TABLE ACCESS BY INDEX ROWID| test1      |      1 |      1 |     38 |00:00:00.01 |      49 |
|*  6 |       INDEX RANGE SCAN          | INDEX_SFLAG     |      1 |      1 |     38 |00:00:00.01 |       3 |
|*  7 |      INDEX FAST FULL SCAN       | PK_test2 |     38 |  24575 |     54 |00:00:25.65 |     133K|
|   8 |   TABLE ACCESS BY INDEX ROWID   | test2    |     54 |      1 |     54 |00:00:00.01 |     167 |
|*  9 |    INDEX UNIQUE SCAN            | PK_test2 |     54 |      1 |     54 |00:00:00.01 |     113 |
----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

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

   6 - access("A"."SFLAG"='-3')
   7 - filter(INSTR(','||"A"."ID"||',',','||"B"."CERTIID"||',')>0)
   9 - access("CERTIID"="$nso_col_1")
   这里使用了一个子查询,子查询中使用的条件不是B.*而是B.certiid.可以看到这里使用了 INDEX FAST FULL SCAN,相当于把
索引当表来进行扫描,不考虑索引的结构。但是这里的一列的‘表’比所有字段的表要小得多所以也加快了查询。
    修改后得语句任然有性能问题,看到这里的
    INDEX FAST FULL SCAN       | PK_test2 |     38
    在NESTED LOOP的机制下运行了38次。本来想通过HASH JION来代替NESTED LOOPS 但是这里条件是INSTR(','||"A"."ID"||',',','||"B"."CERTIID"||',')>0,所以不好修改了。
   修改后语句运行的时间由120秒降低到48秒,不知道还有优化的方法没?
认证徽章
论坛徽章:
9
ITPUB社区OCM联盟徽章
日期:2013-03-27 11:17:11奥运纪念徽章
日期:2013-06-18 09:13:52ITPUB社区千里马徽章
日期:2013-08-22 09:58:03大众
日期:2013-08-30 14:51:33路虎
日期:2013-12-01 18:25:42
发表于 2011-8-29 16:17 | 显示全部楼层
原执行计划:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1212030027
--------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |  3943K|  1921M|  1384K  (1
|   1 |  NESTED LOOPS                |              |  3943K|  1921M|  1384K  (1
|   2 |   TABLE ACCESS BY INDEX ROWID| test1   |    71 |  1278 |     5   (0
|*  3 |    INDEX RANGE SCAN          | INDEX_SFLAG  |    71 |       |     3   (0
|*  4 |   TABLE ACCESS FULL          | test2 | 55470 |    26M| 19504   (1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."SFLAG"='-3')
   4 - filter(INSTR(','||"A"."ID"||',',','||"B"."CERTIID"||',')>0)

17 rows selected

SQL>

使用道具 举报

回复
认证徽章
论坛徽章:
9
ITPUB社区OCM联盟徽章
日期:2013-03-27 11:17:11奥运纪念徽章
日期:2013-06-18 09:13:52ITPUB社区千里马徽章
日期:2013-08-22 09:58:03大众
日期:2013-08-30 14:51:33路虎
日期:2013-12-01 18:25:42
发表于 2011-9-2 11:23 | 显示全部楼层
顶顶看

使用道具 举报

回复
论坛徽章:
10
授权会员
日期:2007-08-09 15:37:26会员2007贡献徽章
日期:2007-09-26 18:42:10ITPUB元老
日期:2007-10-15 21:12:09ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44ITPUB十周年纪念徽章
日期:2011-11-01 16:20:28灰彻蛋
日期:2013-06-24 14:20:02
发表于 2011-9-2 11:39 | 显示全部楼层
试试
select b.*
    from test2 b where exists (select 1 from test1 a where a.sflag='-3' and a.id like '%'||b.certiid||'%')

使用道具 举报

回复
论坛徽章:
1
ITPUB社区OCM联盟徽章
日期:2014-08-08 09:20:49
发表于 2011-9-2 11:40 | 显示全部楼层
用函数索引试试,不用instr,改用substr

使用道具 举报

回复
论坛徽章:
10
授权会员
日期:2007-08-09 15:37:26会员2007贡献徽章
日期:2007-09-26 18:42:10ITPUB元老
日期:2007-10-15 21:12:09ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44ITPUB十周年纪念徽章
日期:2011-11-01 16:20:28灰彻蛋
日期:2013-06-24 14:20:02
发表于 2011-9-2 11:47 | 显示全部楼层
instr(','||a.id||',',','||b.certiid||',')是建不了函数索引的

使用道具 举报

回复
论坛徽章:
1
ITPUB社区OCM联盟徽章
日期:2014-08-08 09:20:49
发表于 2011-9-2 11:49 | 显示全部楼层
所以说用substr

使用道具 举报

回复
论坛徽章:
10
授权会员
日期:2007-08-09 15:37:26会员2007贡献徽章
日期:2007-09-26 18:42:10ITPUB元老
日期:2007-10-15 21:12:09ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44ITPUB十周年纪念徽章
日期:2011-11-01 16:20:28灰彻蛋
日期:2013-06-24 14:20:02
发表于 2011-9-2 11:52 | 显示全部楼层
a模式 str1,str2,str3,str4
b模式 strn
请问如何建substr

使用道具 举报

回复
认证徽章
论坛徽章:
9
ITPUB社区OCM联盟徽章
日期:2013-03-27 11:17:11奥运纪念徽章
日期:2013-06-18 09:13:52ITPUB社区千里马徽章
日期:2013-08-22 09:58:03大众
日期:2013-08-30 14:51:33路虎
日期:2013-12-01 18:25:42
发表于 2011-9-2 12:07 | 显示全部楼层
like '%'||b.certiid||'%'
我觉得:
这样的情况下优化器可能很难应用到索引除非索引包含了需要所有信息,会使用INDEX FAST SCAN。
因为ORACLE很难定位到索引的信息,用不到索引的结构,所以只能全扫描了。

使用道具 举报

回复
论坛徽章:
10
授权会员
日期:2007-08-09 15:37:26会员2007贡献徽章
日期:2007-09-26 18:42:10ITPUB元老
日期:2007-10-15 21:12:09ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44ITPUB十周年纪念徽章
日期:2011-11-01 16:20:28灰彻蛋
日期:2013-06-24 14:20:02
发表于 2011-9-2 12:58 | 显示全部楼层
那就强制走
select /*+INDEX_FFS(b PK_test2)*/b.*
    from test2 b where exists (select 1 from test1 a where a.sflag='-3' and a.id like '%'||b.certiid||'%')

使用道具 举报

回复

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

本版积分规则 发表回复

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号
  
快速回复 返回顶部 返回列表