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秒，不知道还有优化的方法没？
原执行计划：
PLAN_TABLE_OUTPUT
-----------------------------