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

[killhuore]

有一个视图如下：
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这个字段去掉就不会使用全表查询而使用索引了。

[zgh2003]

试试分析一下PROCINSTANCE表

[killhuore]

楼上大哥，我用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倍左右？而且每次对相关表操作以后还要重新执行分析命令，不然速度又恢复到以前的样子，这是怎么回事？有什么解决方案吗？

[killhuore]

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

[zgh2003]

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

[zgh2003]

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

[zgh2003]

rr_lan->table_name

[zgh2003]

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

[killhuore]

谢谢楼上大哥的悉心指导，用临时表确实能加快查询速度，但是问题是我这个视图里面的数据查询周期很短，不适宜用触发器或者存储过程来更新临时表里的数据。现在我想还是做一个计划来执行分析表的语句，这样可能会更适合一点。