关于sql优化——索引的使用

Question

有一个视图如下：
Select a.WKI_ID AS wf_workitem_id, 
       a.WKI_Name AS wf_workitem_name, j.user_name as wf_from_user_name,
      a.WKI_CreateTime AS wf_create_date,a.wki_checkintime as wf_checkin_date, a.WKI_Desc AS wf_workitem_url,
      a.wki_state,a.wki_user,a.wki_checkouttime,
      c.PRI_ID AS wf_process_instance_id,
      d.wka_applyman,
      g.BUSINESS_ID AS wf_business_id,
      g.PROJECT_ID AS wf_project_id,
      h.PRJ_TITLE AS wf_project_title,
      h.PRJ_BUSS_TYPE AS wf_business_type,
      h.PRJ_BUSS_NAME AS wf_business_name, k.TIME_LIMIT AS wf_workitem_timelimit,
      k.TIME_USED AS wf_workitem_timeused, k.TIME_LEFT AS wf_workitem_timeleft, 
      e.atd_id,e.atd_tamid
      l.act_max_timeout
      ,decode((select count(*) from wf_workitem_urge m where m.wki_id=a.wki_id),0,'no') as ifHaveUrge
FROM WorkItem a      
      INNER JOIN ActiInstance b ON a.WKI_AtiID = b.ATI_ID
      INNER JOIN ProcInstance c ON b.ATI_PriID = c.PRI_ID
      INNER JOIN WF_PROCESS_BUSINESS_REL g ON c.PRI_ID = g.PRO_INST_ID
      INNER JOIN WF_PUBLIC_PROJECT_INFO h ON g.PROJECT_ID = h.PROJECT_ID
      INNER JOIN ProcDefinition f ON f.PRD_ID = c.PRI_PrdID
      LEFT OUTER JOIN WorkitemApply d ON a.WKI_ID = d.WKA_WkiID
      INNER JOIN ActiDefinition e ON b.ATI_AtdID = e.ATD_ID           
      LEFT OUTER JOIN WF_WORKITEM_REL i ON a.WKI_ID = i.TO_ITEM_ID
      LEFT OUTER JOIN WF_USER j ON j.USER_ID = i.FROM_USER_ID
      INNER JOIN WF_WORKITEM_PROCESS_TIME k ON a.WKI_ID = k.WORKITEM_ID
      LEFT JOIN wf_actinstance_ext l On b.ATI_ID = l.act_instance_id
      INNER JOIN  workitemreledata m on a.wki_id = m.WKR_WKIID      
where m.WKR_VARIABLENAME='isNeedSignIn' and m.WKR_NUMBVALUE!=1其中，oracle执行计划如下：
SELECT STATEMENT, GOAL = CHOOSE Cost=206 Cardinality=302 Bytes=208078
 HASH JOIN Cost=206 Cardinality=302 Bytes=208078
  NESTED LOOPS Cost=187 Cardinality=302 Bytes=200226
   NESTED LOOPS Cost=144 Cardinality=43 Bytes=21672
    HASH JOIN Cost=144 Cardinality=43 Bytes=21113
     HASH JOIN Cost=141 Cardinality=43 Bytes=19049
      HASH JOIN OUTER Cost=133 Cardinality=43 Bytes=17931
       NESTED LOOPS Cost=130 Cardinality=43 Bytes=16985
        NESTED LOOPS OUTER Cost=102 Cardinality=28 Bytes=9968
         HASH JOIN OUTER Cost=82 Cardinality=20 Bytes=6540
          HASH JOIN Cost=77 Cardinality=20 Bytes=6140
           HASH JOIN OUTER Cost=73 Cardinality=20 Bytes=5100
            NESTED LOOPS Cost=69 Cardinality=20 Bytes=4500
             TABLE ACCESS FULL Object owner=YZH Object name=WORKITEMRELEDATA Cost=49 Cardinality=20 Bytes=1060
             TABLE ACCESS BY INDEX ROWID Object owner=YZH Object name=WORKITEM Cost=1 Cardinality=1 Bytes=172
              INDEX UNIQUE SCAN Object owner=YZH Object name=PK_WORKITEM Cardinality=1
            TABLE ACCESS FULL Object owner=YZH Object name=WORKITEMAPPLY Cost=3 Cardinality=1062 Bytes=31860
           TABLE ACCESS FULL Object owner=YZH Object name=WF_WORKITEM_PROCESS_TIME Cost=3 Cardinality=1062 Bytes=55224
          INDEX FAST FULL SCAN Object owner=YZH Object name=PK_WORKITEM_REL Cost=4 Cardinality=1634 Bytes=32680
         TABLE ACCESS BY INDEX ROWID Object owner=YZH Object name=WF_USER Cost=1 Cardinality=1 Bytes=29
          INDEX UNIQUE SCAN Object owner=YZH Object name=PK_WF_USER Cardinality=1
        TABLE ACCESS BY INDEX ROWID Object owner=YZH Object name=ACTIINSTANCE Cost=1 Cardinality=2 Bytes=78
         INDEX UNIQUE SCAN Object owner=YZH Object name=PK_ACTIINSTANCE Cardinality=1
       TABLE ACCESS FULL Object owner=YZH Object name=WF_ACTINSTANCE_EXT Cost=2 Cardinality=409 Bytes=8998
      TABLE ACCESS FULL Object owner=YZH Object name=PROCINSTANCE Cost=7 Cardinality=4738 Bytes=123188
     TABLE ACCESS FULL Object owner=YZH Object name=WF_PROCESS_BUSINESS_REL Cost=2 Cardinality=409 Bytes=19632
    INDEX UNIQUE SCAN Object owner=YZH Object name=PK_PROCDEFINITION Cardinality=1 Bytes=13
   TABLE ACCESS BY INDEX ROWID Object owner=YZH Object name=WF_PUBLIC_PROJECT_INFO Cost=1 Cardinality=7 Bytes=1113
    INDEX UNIQUE SCAN Object owner=YZH Object name=PK_WF_PUBLIC_PROJECT_INFO Cardinality=1
  TABLE ACCESS FULL Object owner=YZH Object name=ACTIDEFINITION Cost=12 Cardinality=8414 Bytes=218764现在问题是，我的c.PRI_ID 和 g.PRO_INST_ID都是主键，为什么PROCINSTANCE表会造成全表查询？还有我的e.atd_id也是主键，为什么ACTIDEFINITION表也会造成全表查询？我把e.atd_tamid这个字段去掉就不会使用全表查询而使用索引了。

Answer 1

楼上大哥，我用ANALYZE TABLE <tablename> COMPUTE STATISTICS;命令分析了所有相关的表以后，执行计划如下：
SELECT STATEMENT, GOAL = CHOOSE Cost=144 Cardinality=145 Bytes=31030
 HASH JOIN OUTER Cost=144 Cardinality=145 Bytes=31030
  HASH JOIN OUTER Cost=140 Cardinality=145 Bytes=29870
   HASH JOIN OUTER Cost=135 Cardinality=145 Bytes=28275
    HASH JOIN Cost=132 Cardinality=145 Bytes=27115
     HASH JOIN Cost=125 Cardinality=145 Bytes=23635
      TABLE ACCESS FULL Object owner=YZH Object name=WF_PROCESS_BUSINESS_REL Cost=2 Cardinality=712 Bytes=23496
      NESTED LOOPS Cost=122 Cardinality=957 Bytes=124410
       HASH JOIN Cost=122 Cardinality=957 Bytes=121539
        TABLE ACCESS FULL Object owner=YZH Object name=PROCINSTANCE Cost=8 Cardinality=4702 Bytes=28212
        HASH JOIN Cost=112 Cardinality=957 Bytes=115797
         TABLE ACCESS FULL Object owner=YZH Object name=ACTIDEFINITION Cost=12 Cardinality=5516 Bytes=27580
         HASH JOIN OUTER Cost=98 Cardinality=957 Bytes=111012
          HASH JOIN Cost=94 Cardinality=957 Bytes=104313
           HASH JOIN Cost=71 Cardinality=957 Bytes=94743
            TABLE ACCESS FULL Object owner=YZH Object name=WF_WORKITEM_PROCESS_TIME Cost=3 Cardinality=2724 Bytes=21792
            HASH JOIN Cost=66 Cardinality=2685 Bytes=244335
             TABLE ACCESS FULL Object owner=YZH Object name=WORKITEMRELEDATA Cost=49 Cardinality=2685 Bytes=40275
             TABLE ACCESS FULL Object owner=YZH Object name=WORKITEM Cost=13 Cardinality=7639 Bytes=580564
           TABLE ACCESS FULL Object owner=YZH Object name=ACTIINSTANCE Cost=19 Cardinality=12746 Bytes=127460
          TABLE ACCESS FULL Object owner=YZH Object name=WF_ACTINSTANCE_EXT Cost=2 Cardinality=400 Bytes=2800
       INDEX UNIQUE SCAN Object owner=YZH Object name=PK_PROCDEFINITION Cardinality=1 Bytes=3
     TABLE ACCESS FULL Object owner=YZH Object name=WF_PUBLIC_PROJECT_INFO Cost=6 Cardinality=5029 Bytes=120696
    INDEX FAST FULL SCAN Object owner=YZH Object name=PK_WORKITEM_REL Cost=2 Cardinality=2716 Bytes=21728
   TABLE ACCESS FULL Object owner=YZH Object name=WF_USER Cost=4 Cardinality=1907 Bytes=20977
  TABLE ACCESS FULL Object owner=YZH Object name=WORKITEMAPPLY Cost=3 Cardinality=2388 Bytes=19104
有这么多的全表查询，为什么反而查询速度提升了6倍左右？而且每次对相关表操作以后还要重新执行分析命令，不然速度又恢复到以前的样子，这是怎么回事？有什么解决方案吗？

Answer 2

Oracle查询时使用了两种优化器,一是基于规则的优化器,一是基于代价的优化器；基于规则的优化器依赖于Oralce处理语句的一系列规则，而基于代价的优化器则依赖于对数据表的分析(analyze)，在对表数据进行分析(analyze)之前，Oracle查询会使用基于规则的优化器，即使系统参数设置了使用基于代价的优化器。
对于实际业务表来说，由于数据在不断更新，基于代价的优化器要求数据分析的精度越高，优化器选择执行计划就越高效，当然对于大数据表来说，全表分析的时间会更长，但在Oracle中提供了采样数据分析，可以每次采样百分比进行分析。
所以，由于基于代价的优化器对数据分析的依赖，要求周期性的对数据进行分析，一般一周一次就可以了。
另外一个比较重要的一点，我认为根据查询的计划，看看能不能对SQL进行调整，一个设计好的高效的SQL才是我们提高系统性能的根本所在，而不要过分依赖于通过对Oracle的调整来提高性能。但对于楼主提出的SQL，由于没有实际环境及数据表验证你的查询效率，所以仅限于提出一些看法及建议。

Answer 3

谢谢楼上的大哥，对于上面视图的sql语句的优化，我已经为相关的表和关联的字段都建了主键索引，在sql语句优化这方面大哥还有什么好的建议吗？还有分析表确实是让oracle走了基于代价的优化路线，除了对表进行分析以外，需要对那些主键索引进行分析吗？

Answer 4

你可以用analyze table rr_lan compute statistics for TABLE for all indexes分析表及所有的索引

Answer 5

在SQL语句优化方面,没有固定的公式，关键要具体问题具体分析，但是有一点，要尽量使SQL语句不要太复杂，能拆分成多个语句的，尽量拆分，我在做一些统计报表时，宁愿借助临时表，再写一个存储过程来更新数据，前台只需要直接读取该临时表就可以了。