About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc
全部博文（169）
分类： Oracle
2020-06-09 09:15:18
对这类问题的常规解法，比如Oracle版本是9i，可以使用PL/SQL中的集合类型，对传入的字符串按分隔符解析之后存储到相关集合类型的变量中，比如可以存储到嵌套表，数组中(注意不能是INDEX BY表，必须是SCHEMA级别的类型，数组有容量也不常使用)，然后利用TABLE函数将集合转为伪表，剩下就和前面说的一样了。
试想一下，使用集合构造临时表，需要做哪些工作呢？
1) 外界传入的是一个含有分隔符(一般是逗号，确保字段中没有逗号，如果有，用其他分隔符)的字符串，比如’aa,bb,cc’之类的字符串，首先需要按分隔符解析，然后将每个值存储到对应的集合变量中，所以，需要有一个函数能够接收传入的字符串，然后解析并存储到相应的集合变量并且返回。
2) 将集合变量通过TABLE函数转换为伪表，放到子查询中。table函数将集合转为伪表，返回的列名是COLUMN_VALUE，对应的类型是集合元素的类型，本节例子的COLUMN_VALUE的类型就是VARCHAR2类型。
3) 然后写相应的查询语句。
根据上面的描述，需要创建嵌套表以及将字符串转为嵌套表的函数，如下所示：
--创建嵌套表
CREATE OR REPLACE TYPE varchar2_tt AS TABLE
OF VARCHAR2 (1000);
/
--创建函数
CREATE OR REPLACE
FUNCTION f_str2list( in_str IN VARCHAR2 ,in_delimiter IN VARCHAR2 DEFAULT ',' )
RETURN varchar2_tt
/******************************************************************************
||程序说明:将按指定分隔符分割的字符串转为嵌套表类型变量返回
||输入变量:
|| in_str 字符串，如'a,b,c'
|| in_delimiter 分割符，默认是逗号
||输出变量:
|| varchar2_tt类型，嵌套表
******************************************************************************/
AS
v_str VARCHAR2(32767) DEFAULT in_str || in_delimiter;
v_result varchar2_tt := varchar2_tt();
i NUMBER;
BEGIN
LOOP
EXIT WHEN v_str IS NULL;
i := INSTR( v_str, in_delimiter );
v_result.extend;
v_result(v_result.count) :=
TRIM( SUBSTR( v_str, 1, i -1 ) );
v_str := SUBSTR( v_str, i +1 );
ENDLOOP;
RETURN v_result;
END;
/
执行上面的脚本，创建嵌套表和返回嵌套表的函数即可。下面使用上面创建的嵌套表和函数来解决where in list的问题。通过函数将传入的字符串包装成嵌套表，然后利用TABLE函数将嵌套表转为伪表，放到子查询中即可。具体操作如下：
DINGJUN123>VAR str VARCHAR2(100);
DINGJUN123>EXEC :str :='XY,YZ';
PL/SQL 过程已成功完成。
2 FROM t
3 WHERE object_name IN
4 (SELECT column_value
5 FROM TABLE( CAST(f_str2list(:str) AS varchar2_tt )
6 )
7 );
COUNT(*)
----------
2
已选择 1 行。
结果是正确的。如果传入的不是字符串，而是一个SQL语句或REF CURSOR变量，可以吗?当然可以，把f_str2list函数改改就可以了，这个读者可以自己思考一下，这里不详细讲解。
Where in List问题要特别注意性能问题，一般选择的字段都建有索引，希望计划走索引和nested loop方式连接查询，而不希望通过hash join或sort merge join方式连接查询，因为实际中传入的一般都不是很长的字符串，而源表可能数据量很大。本部分主要探讨使用集合函数解决where in list问题中注意的相关问题，对于正则表达式和INSTR+SUBSTR也可以通过hint来固定计划，而且一般不使用hint,CBO也能选择正确的计划，比较简单，所以只探讨集合函数的性能问题，因为使用TABLE函数还是有点复杂的，请看：
DINGJUN123>SELECT COUNT(*) FROM t;
COUNT(*)
----------
14006
已选择 1 行。
DINGJUN123>SET AUTOTRACE TRACEONLY
DINGJUN123>VAR str VARCHAR2(100);
DINGJUN123>EXEC :str :='XY,YZ';
PL/SQL 过程已成功完成。
DINGJUN123> SELECT *
2 FROM t
3 WHERE object_name IN
4 (SELECT column_value
5 FROM TABLE( CAST( f_str2list(:str) AS varchar2_tt ))
6 );
已选择2行。
执行计划
----------------------------------------------------------
Plan hash value: 3487633200
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 88 | 84 (3)| 00:00:02 |
|* 1 | HASH JOIN RIGHT SEMI | | 1 | 88 | 84 (3)| 00:00:02 |
| 2 | COLLECTION ITERATOR PICKLER FETCH| F_STR2LIST | | | | |
| 3 | TABLE ACCESS FULL | T | 14006 | 1176K| 54 (2)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"=VALUE(KOKBF$))
统计信息
----------------------------------------------------------
927 recursive calls
0 db block gets
486 consistent gets
233 physical reads
0 redo size
1257 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
2 rows processed
第1次硬解析，为了比较多执行几次，直到逻辑读固定，统计信息为：
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
184 consistent gets
0 physical reads
0 redo size
1257 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
从上面结果看到，上面SQL采用的是Hash join的连接方式，全表访问表t，第1次执行逻辑读很大，为486，最终逻辑读固定为184，平均每行逻辑读为92(184/2)，这种计划是很差的。那为什么Oracle会采用这种计划呢?如下分析：
DINGJUN123>SELECT/*+first_rows*/ *
2 FROM TABLE( CAST( f_str2list(:str) AS varchar2_tt ));
已选择2行。
执行计划
----------------------------------------------------------
Plan hash value: 2025561284
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| F_STR2LIST | | | | |
----------------------------------------------------------------------------------------------
从上面结果看出，TABLE函数的默认行数是8168行(TABLE函数创建的伪表是没有统计信息的)，这个值不小了，一般比实际应用中的行数要多的多，经常导致执行计划走hash join，而不是nested loop+index。(其实这个默认的TABLE函数基数8168依赖于数据库的数据块大小，我的数据库数据块是8K的，CBO估算TABLE函数的基数为8168)
怎么改变这种情况呢?当然是加hint提示来改变执行计划了,对where in list，常常使用的hint有：first_rows，index，cardinality等。这里特别介绍下cardinality(table|alias,n)，这个hint很有用，它可以让CBO优化器认为表的行数是n，这样就可以改变执行计划了。现在改写上面的查询：
DINGJUN123>SELECT/*+cardinality(tab,5)*/ column_value
2 FROM TABLE( CAST( f_str2list(:str) AS varchar2_tt )) tab;
已选择2行。
执行计划
----------------------------------------------------------
Plan hash value: 2025561284
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 10 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| F_STR2LIST | | | | |
--------------------------------------------------------------------------------------------
--看上面CBO能估算基数为5了，下面试试
DINGJUN123> SELECT *
2 FROM t
3 WHERE object_name IN
4 (SELECT /*+cardinality(tab,5)*/ column_value
5 FROM TABLE( CAST( f_str2list(:str) AS varchar2_tt )) tab
6 );
已选择2行。
执行计划
----------------------------------------------------------
Plan hash value: 4129437246
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 528 | 36 (3)| 00:00:0
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 86 | 2 (0)| 00:00:0
| 2 | NESTED LOOPS | | 6 | 528 | 36 (3)| 00:00:0
| 3 | SORT UNIQUE | | | | |
| 4 | COLLECTION ITERATOR PICKLER FETCH| F_STR2LIST | | | |
|* 5 | INDEX RANGE SCAN | IDX_T | 1 | | 1 (0)| 00:00:0
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("OBJECT_NAME"=VALUE(KOKBF$))
统计信息
----------------------------------------------------------
590 recursive calls
0 db block gets
149 consistent gets
14 physical reads
0 redo size
1257 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
2 rows processed
多执行几次，直到逻辑读固定的统计信息为：
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1257 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
第1次逻辑读为149，比前面hash
join的软解析逻辑读还要少(184)，而且最后逻辑读固定为7，则平均每行逻辑读为3.5，效率很好。现在计划走nested loop了，而且对表t也走了索引。
还需要注意点，使用TABLE函数解决where in list问题，常需要在子查询中加ROWNUM条件，从而固化子查询为视图，上面的在10g R2环境下测试加ROWNUM和不加ROWNUM计划不同，但是最后的逻辑读和函数调用次数是一样的，只不过计划不同而已，加ROWNUM执行计划和固定逻辑读后的统计信息为(初次执行的省略)：
DINGJUN123> SELECT *
2 FROM t
3 WHERE object_name IN
4 (SELECT/*+cardinality(tab,5)*/ column_value
5 FROM TABLE( CAST( f_str2list(:str) AS varchar2_tt )) tab
6 WHERE ROWNUM >=0
7 );
已选择2行。
执行计划
----------------------------------------------------------
Plan hash value: 483176403
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 588 | 32 (4)| 00:00:01|
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 86 | 2 (0)| 00:00:01|
| 2 | NESTED LOOPS | | 1 | 588 | 32 (4)| 00:00:01|
| 3 | VIEW | VW_NSO_1 | 5 | 2510 | 29 (0)| 00:00:01|
| 4 | HASH UNIQUE | | 1 | 10 | | |
| 5 | COUNT | | | | | |
|* 6 | FILTER | | | | | |
| 7 | COLLECTION ITERATOR PICKLER FETCH| F_STR2LIST | | | | |
|* 8 | INDEX RANGE SCAN | IDX_T | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(ROWNUM>=0)
8 - access("OBJECT_NAME"="$nso_col_1")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1257 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
可见在10g R2下区别不大，也就是执行计划不同而已，最后逻辑读都一样，函数调用的次数也一样，这里也只能代表我的测试环境上是如此，以前碰到过不同的情况，下面就改变测试环境，测试加ROWNUM和不加ROWNUM的区别，然后给出一点建议。这个测试在9i下完成：
SQL>SHOW RELEASE
release 902000400
建表语句以及F_STR2LIST函数都和10g的一样，只不过表t的记录数不同，为了比较加ROWNUM的好处，需要在函数F_STR2LIST的BEGIN后面加上：
DBMS_APPLICATION_INFO.set_client_info(USERENV('client_info')+1 );//用来测试函数的调用次数
如果对这对DBMS_APPLICATION_INFO包不是很熟悉，建议查看Oracle文档了解，这个还是很有用的。
建立表、类型、函数(请读者自己完成)，查看表t的数目：
SQL> SELECT COUNT(*) FROM t;
COUNT(*)
----------
9127
表t有9127行，下面详细看测试过程，先不加ROWNUM：
SQL>VAR str VARCHAR2(100);
SQL>EXEC :str :='XY,YZ';
PL/SQL 过程已成功完成。
--重置client_info
SQL> EXEC DBMS_APPLICATION_INFO.set_client_info(0);
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.07
SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT *
2 FROM t
3 WHERE object_name IN
4 (SELECT/*+cardinality(tab,5)*/ column_value
5 FROM TABLE( CAST( f_str2list(:str) AS varchar2_tt )) tab
6 );
已用时间: 00: 00: 02.76
执行计划
-------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=100410 Card=1 Bytes=83)
1 0 NESTED LOOPS (SEMI) (Cost=100410 Card=1 Bytes=83)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=13 Card=9127 Bytes=757541)
3 1 COLLECTION ITERATOR (PICKLER FETCH) OF 'F_STR2LIST'
统计信息
----------------------------------------------------------
4320 recursive calls
0 db block gets
1042 consistent gets
216 physical reads
0 redo size
732 bytes sent via SQL*Net to client
233 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
109 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> SET AUTOTRACE OFF
SQL> SELECT USERENV('client_info') FROM DUAL;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
9127
已用时间: 00: 00: 00.03
从上面的测试看出，这个计划多次调用了F_STR2LIST函数，表t有9127行，函数就被调用了9127次，这是不可接受的，而且全表扫描t(开始的10g R2中我的测试是走索引了),也是不可接受的，应该走索引才对，可能CBO估算错误，这个cardinality在这个测试中没有起作用，这个计划最终逻辑读固定为118,那么平均每行逻辑读为59,性能也是很差的，受多次调用函数以及不走索引的影响，这个查询最终逻辑读固定的统计信息为：
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
118 consistent gets
0 physical reads
0 redo size
731 bytes sent via SQL*Net to client
233 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
下面测试加rownum的情况：
--重置client_info
SQL> EXEC DBMS_APPLICATION_INFO.set_client_info(0);
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.07
SQL> SELECT *
2 FROM t
3 WHERE object_name IN
4 (SELECT/*+cardinality(tab,5)*/ column_value
5 FROM TABLE( CAST( f_str2list(:str) AS varchar2_tt )) tab
6 WHERE ROWNUM >=0
7 );
已用时间: 00: 00: 00.79
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=32 Card=1 Bytes=585)
1 0 HASH JOIN (SEMI) (Cost=32 Card=1 Bytes=585)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=13 Card=9127 Bytes=757541)
3 1 VIEW OF 'VW_NSO_1' (Cost=11 Card=5 Bytes=2510)
4 3 COUNT
5 4 FILTER
6 5 COLLECTION ITERATOR (PICKLER FETCH) OF 'F_STR2LIST
'
统计信息
----------------------------------------------------------
30 recursive calls
0 db block gets
125 consistent gets
1 physical reads
0 redo size
732 bytes sent via SQL*Net to client
233 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> SET AUTOTRACE OFF
SQL> SELECT USERENV('client_info') FROM DUAL;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
1
已用时间: 00: 00: 00.03
加了ROWNUM，子查询被固化为视图，函数只调用一次，现在的逻辑读为125，比不加ROWNUM的1042要小。但是还是全表扫描，影响效率。下面看固定下来的逻辑读数目为117。
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
117 consistent gets
0 physical reads
0 redo size
731 bytes sent via SQL*Net to client
233 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
平均每行逻辑读为58.5，还是效率很低，虽然这里最终逻辑读和不加ROWNUM的最终逻辑读差不多，但是多次函数调用是不可接受的，这里的函数简单而且表数据量不是很大，如果字符串比较长表数据量很大，效率就低了。有的时候，在9i下测试，加了ROWNUM，就走索引了，也就是cardinality起作用了，比如我在10g R2下的测试就走了索引，但是这里的测试仍然没有起作用，导致还是走全表扫描，所以优化还是和环境有很大关系，必须做足测试。
所以，还是加上ROWNUM固化子查询，减少函数调用次数，并且调整查询走index,最好是nested loop形式。上面因为全表扫描t,导致性能降低，下面加上索引hint和ROWNUM，也需要cardinality（在这个环境下，我做过几个测试，单独使用index hint效果不好，计划走HASH JOIN,逻辑读很大，但是单独使用first_rows hint就可以走NESTED LOOPS，这里就不提供测试过程了），再次测试(index hint和cardinality hint一起使用)：
SQL> SELECT/*+index(t idx_t)*/ *
2 FROM t
3 WHERE object_name IN
4 (SELECT/*+cardinality(tab,5)*/ column_value
5 FROM TABLE( CAST( f_str2list(:str) AS varchar2_tt )) tab
6 WHERE ROWNUM >=0
7 );
已用时间: 00: 00: 00.73
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=6 Bytes=3510)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=83)
2 1 NESTED LOOPS (Cost=37 Card=6 Bytes=3510)
3 2 VIEW OF 'VW_NSO_1' (Cost=11 Card=5 Bytes=2510)
4 3 SORT (UNIQUE)
5 4 COUNT
6 5 FILTER
7 6 COLLECTION ITERATOR (PICKLER FETCH) OF 'F_STR2LIST'
8 2 INDEX (RANGE SCAN) OF 'IDX_T' (NON-UNIQUE) (Cost=1 Card=1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
13 consistent gets
2 physical reads
0 redo size
732 bytes sent via SQL*Net to client
233 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
最终固定下的逻辑读锁定为7，每行平均逻辑读为3.5，和10g上测试的一样了，性能得到提升。如下：
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
732 bytes sent via SQL*Net to client
233 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
综上所述，使用TABLE函数解决where in list问题，常常需要走nested loop+索引形式，常使用hint：first_rows、index、cardinality等，有时候cardinality可能不起作用，需要其它的hint，比如index,first_rows等，有时候需要几个hint配合使用，而且需要加上ROWNUM固化子查询，这样一般可以获得最大性能。
本章对where in list问题进行了详细的探讨，下面给出一些总结：
1) 实现where in list问题，首先需要使传入的不定长参数能够作为绑定变量，其次就是要按分隔符解析字符串为多行，然后通过JOIN、EXISTS或IN子查询等解决where in list问题。
2) 一般的方法就是使用PL/SQL集合+TABLE函数来解决where in list问题，要注意，除非你特别确定，不会多次调用函数，否则加上ROWNUM条件有备无患，另外常常需要通过hint调整查询，有时候需要1个hint就可以，有时候可能需要多个hint，比如cardinality+index配合使用，对于这种查询的性能测试，必须了解自己的源表数据量情况以及传入的参数情况，查询的结果行数等，做足测试，从而获得最大的性能。上面写的函数参数最大支持32767字节，如果需要更大的字节支持，比如CLOB，那么可以考虑使用临时表，如果还用函数就比较麻烦，当然使用正则表达式或INSTR+SUBSTR的方法是支持CLOB的。
3）在10G中可以考虑使用正则表达式解决，10G以下的版本可以考虑使用INSTR+SUBSTR的方法。当然使用正则表达式或INSTR+SUBSTR方法可以考虑使用动态视图屏蔽查询复杂性，这种方法比较简单，一般也能获得很好的性能。
4）一般来说，where in list是很复杂的，需要经过严格的测试，来确定何种方法适合你，比如本章说的正则和INSTR+SUBSTR的方法测试下来平均每行逻辑读为2，比集合的效率要稍微高点(集合最好的为3.5)，但是一般我比较喜欢集合处理的方式，因为SQL写起来最简单，只需要定义函数和集合类型即可。读者可以自己经过测试后，选定何种方法解决。
5）对于管道函数的基数预测，11GR2具有""特性，这个特性使CBO具有智能化自我学习功能，本章测试的TABLE函数的默认基数CBO认为是8168(因为我的数据库数据块大小是8K)，但是到11GR2使用""特性就会改变这一情况了，也可以减少一堆hint的使用，其实很简单，这里就不测试了。如果你的环境是11GR2，可以到网上搜索Oracle 特性的相关文章来学习。
