这个语句还能优化嘛？

gaopengtttt

原语句：
select b.*
    from test1 a,test2 b
   where a.sflag ='-3'
     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秒，不知道还有优化的方法没？

gaopengtttt

原执行计划：
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1212030027
--------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |  3943K|  1921M|  1384K  (1
|   1 |  NESTED LOOPS                |              |  3943K|  1921M|  1384K  (1
|   2 |   TABLE ACCESS BY INDEX ROWID| test1   |    71 |  1278 |     5   (0
|*  3 |    INDEX RANGE SCAN          | INDEX_SFLAG  |    71 |       |     3   (0
|*  4 |   TABLE ACCESS FULL          | test2 | 55470 |    26M| 19504   (1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."SFLAG"='-3')
   4 - filter(INSTR(','||"A"."ID"||',',','||"B"."CERTIID"||',')>0)

17 rows selected

SQL>

gaopengtttt

顶顶看

catchwo

试试
select b.*
    from test2 b where exists (select 1 from test1 a where a.sflag='-3' and a.id like '%'||b.certiid||'%')

zhanglincon

用函数索引试试，不用instr，改用substr

catchwo

instr(','||a.id||',',','||b.certiid||',')是建不了函数索引的

zhanglincon

所以说用substr

catchwo

a模式 str1,str2,str3,str4
b模式 strn
请问如何建substr

gaopengtttt

like '%'||b.certiid||'%'
我觉得：
这样的情况下优化器可能很难应用到索引除非索引包含了需要所有信息，会使用INDEX FAST SCAN。
因为ORACLE很难定位到索引的信息，用不到索引的结构，所以只能全扫描了。

catchwo

那就强制走
select /*+INDEX_FFS(b PK_test2)*/b.*
    from test2 b where exists (select 1 from test1 a where a.sflag='-3' and a.id like '%'||b.certiid||'%')