关于sql优化——索引的使用
有一个视图如下：
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 = CHOOSECost=206Cardinality=302Bytes=208078
HASH JOINCost=206Cardinality=302Bytes=208078
NESTED LOOPSCost=187Cardinality=302Bytes=200226
NESTED LOOPSCost=144Cardinality=43Bytes=21672
HASH JOINCost=144Cardinality=43Bytes=21113
HASH JOINCost=141Cardinality=43Bytes=19049
HASH JOIN OUTERCost=133Cardinality=43Bytes=17931
NESTED LOOPSCost=130Cardinality=43Bytes=16985
NESTED LOOPS OUTERCost=102Cardinality=28Bytes=9968
HASH JOIN OUTERCost=82Cardinality=20Bytes=6540
HASH JOINCost=77Cardinality=20Bytes=6140
HASH JOIN OUTERCost=73Cardinality=20Bytes=5100
NESTED LOOPSCost=69Cardinality=20Bytes=4500
TABLE ACCESS FULLObject owner=YZHObject name=WORKITEMRELEDATACost=49Cardinality=20Bytes=1060
TABLE ACCESS BY INDEX ROWIDObject owner=YZHObject name=WORKITEMCost=1Cardinality=1Bytes=172
INDEX UNIQUE SCANObject owner=YZHObject name=PK_WORKITEMCardinality=1
TABLE ACCESS FULLObject owner=YZHObject name=WORKITEMAPPLYCost=3Cardinality=1062Bytes=31860
TABLE ACCESS FULLObject owner=YZHObject name=WF_WORKITEM_PROCESS_TIMECost=3Cardinality=1062Bytes=55224
INDEX FAST FULL SCANObject owner=YZHObject name=PK_WORKITEM_RELCost=4Cardinality=1634Bytes=32680
TABLE ACCESS BY INDEX ROWIDObject owner=YZHObject name=WF_USERCost=1Cardinality=1Bytes=29
INDEX UNIQUE SCANObject owner=YZHObject name=PK_WF_USERCardinality=1
TABLE ACCESS BY INDEX ROWIDObject owner=YZHObject name=ACTIINSTANCECost=1Cardinality=2Bytes=78
INDEX UNIQUE SCANObject owner=YZHObject name=PK_ACTIINSTANCECardinality=1
TABLE ACCESS FULLObject owner=YZHObject name=WF_ACTINSTANCE_EXTCost=2Cardinality=409Bytes=8998
TABLE ACCESS FULLObject owner=YZHObject name=PROCINSTANCECost=7Cardinality=4738Bytes=123188
TABLE ACCESS FULLObject owner=YZHObject name=WF_PROCESS_BUSINESS_RELCost=2Cardinality=409Bytes=19632
INDEX UNIQUE SCANObject owner=YZHObject name=PK_PROCDEFINITIONCardinality=1Bytes=13
TABLE ACCESS BY INDEX ROWIDObject owner=YZHObject name=WF_PUBLIC_PROJECT_INFOCost=1Cardinality=7Bytes=1113
INDEX UNIQUE SCANObject owner=YZHObject name=PK_WF_PUBLIC_PROJECT_INFOCardinality=1
TABLE ACCESS FULLObject owner=YZHObject name=ACTIDEFINITIONCost=12Cardinality=8414Bytes=218764
现在问题是，我的c.PRI_ID 和 g.PRO_INST_ID都是主键，为什么PROCINSTANCE表会造成全表查询？还有我的e.atd_id也是主键，为什么ACTIDEFINITION表也会造成全表查询？我把e.atd_tamid这个字段去掉就不会使用全表查询而使用索引了。
[解决办法]
试试分析一下PROCINSTANCE表