怎么看sql是否走索引oracle,请协助看一下这个SQL为什么不走索引

最新推荐文章于 2025-04-12 14:29:37 发布

转载最新推荐文章于 2025-04-12 14:29:37 发布 · 1.6k 阅读

文章标签：

本文分析了一个在Oracle数据库中，SQL查询未使用表`sc.cfg_flow_link`上的索引进行全表扫描的问题。问题在于一个包含子查询的IN操作，子查询从`workflow.wfwiparticipant@wflink`获取数据。虽然`cfg_flow_link`和`flow_oper_record`表的`link_id`字段均有索引，但特定的查询条件导致了全表扫描。对比另一个直接使用IN操作的SQL，它能够利用索引。执行计划显示了不同查询的执行路径和成本。

select k.link_id, k.link_name

from sc.cfg_flow_link k

where k.link_id in

(select r.link_id

from sc.flow_oper_record r

where r.ins_id in (select a.processinstid

from workflow.wfwiparticipant@wflink a

where a.rootprocinstid = '302293'))

其中表k与r上都有依link_id字段的索引，没有弄明白sc.cfg_flow_link为什么走全表扫描？

而下述语句：

select k.link_id

from sc.cfg_flow_link k

where k.link_id in (114014001,

114014004,

114014027,

114014007,

114014005,

114014026,

114014035,

114014042,

114014003,

114014006)

就可以走索引，下述in()中的值为第一个SQL中的全部link_id值。但是换成

select r.link_id

from sc.flow_oper_record r

where r.ins_id in (select a.processinstid

from workflow.wfwiparticipant@wflink a

where a.rootprocinstid = '302293')

就不走索引了。

其中在flow_oper_record表上的link_id字段可以为空；

执行计划

----------------------------------------------------------

Plan hash value: 669742738

-------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 71 | 2414 | 68 (0)| 00:00:01 | | |

|* 1 | HASH JOIN RIGHT SEMI | | 71 | 2414 | 68 (0)| 00:00:01 | | |

| 2 | VIEW | VW_NSO_1 | 71 | 923 | 50 (0)| 00:00:01 | | |

| 3 | NESTED LOOPS | | 71 | 2698 | 50 (0)| 00:00:01 | | |

| 4 | NESTED LOOPS | | 71 | 2698 | 50 (0)| 00:00:01 | | |

| 5 | REMOTE | WFWIPARTICIPANT | 6 | 156 | 4 (0)| 00:00:01 | WFLINK | R->S |

|* 6 | INDEX RANGE SCAN | FLOW_OPER_RECORD_IDX02 | 11 | | 2 (0)| 00:00:01 | | |

| 7 | TABLE ACCESS BY INDEX ROWID| FLOW_OPER_RECORD | 11 | 132 | 13 (0)| 00:00:01 | | |

| 8 | TABLE ACCESS FULL | CFG_FLOW_LINK | 2499 | 52479 | 18 (0)| 00:00:01 | | |

-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("K"."LINK_ID"="LINK_ID")

6 - access("R"."INS_ID"="A"."PROCESSINSTID")

Remote SQL Information (identified by operation id):

----------------------------------------------------

5 - SELECT "PROCESSINSTID","ROOTPROCINSTID" FROM "WORKFLOW"."WFWIPARTICIPANT" "A" WHERE

"ROOTPROCINSTID"=302293 (accessing 'WFLINK' )

Note

-----

- 'PLAN_TABLE' is old version

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

154 consistent gets

0 physical reads

0 redo size

663 bytes sent via SQL*Net to client

472 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10 rows processed

--------------------------------------------------------------------------------------------------------------------------

执行计划

----------------------------------------------------------

Plan hash value: 1792807260

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 10 | 70 | 2 (0)| 00:00:01 |

| 1 | INLIST ITERATOR | | | | | |

|* 2 | INDEX UNIQUE SCAN| PK_CFG_FLOW_LINK_ID | 10 | 70 | 2 (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("K"."LINK_ID"=114014001 OR "K"."LINK_ID"=114014003 OR

"K"."LINK_ID"=114014004 OR "K"."LINK_ID"=114014005 OR "K"."LINK_ID"=114014006 OR

"K"."LINK_ID"=114014007 OR "K"."LINK_ID"=114014026 OR "K"."LINK_ID"=114014027 OR

"K"."LINK_ID"=114014035 OR "K"."LINK_ID"=114014042)

Note

-----

- 'PLAN_TABLE' is old version

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

9 consistent gets

0 physical reads

0 redo size

476 bytes sent via SQL*Net to client

472 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10 rows processed