摘要：这篇文章重点是SQL语句优化，构造SQL，所有的测试都是基于IN子句进行的，正如题目所写，当然与其他的，比如exists，join等是相通的。包括动态SQL构造，解决办法，以及where in list的性能问题等方面。
1. 背景介绍
在系统开发中，经常有这样的需求：前台传入一个字符串，而且此字符串具有指定分隔符，并且长度不定，那么如何根据传入的这个字符串查询对应的结果呢?考虑这样的需求，你肯定已经想到，采用构造SQL语句来解决，的确，你的想法没错，最简单的也就是构造SQL：
|
—将字符串转换为convert_value_list,convert_value_list类似于值的列表，比如将’a,b,c’转为’a’,’b’,’c’ SELECT ….. FROM ….. WHERE column in (convert_value_list);
|
的确可以通过构造SQL来解决这样的问题(比如在JAVA中可以将传入的字符串通过String的split方法处理，然后将结果拼凑到SQL中)，但是另一方面，这样的写法有一定的限制：Oracle WHERE条件中IN列表数目不能超过1000个，另外列表数目不定会导致无法使用绑定变量而影响效率。那么怎样才能使列表长度不定而又能使用绑定变量呢？解决方法有很多种，下面逐一分析，从而使你能够根据实际情况来选择何种方法(动态SQL构造也会作为例子进行反面探讨，这个例子在PL/SQL中实现，当然在JAVA等语言中实现方式也类似)。
|
解决where in list问题，首要考虑的两个问题就是解决列表长度问题和效率问题，效率问题首要考虑绑定变量问题，另外还要考虑比如cardinality(基数)对执行计划的影响等。 |
为了避免复杂性，测试的大部分例子只根据where in list计算COUNT，这里测试的都是基于IN子查询的，当然也可以使用JOIN或EXISTS等实现，对10g来说效率基本没有什么差别(读者可以自己测试下其它方法)。
建立测试表：
–建两个表xy和yz作为测试用，所以这两个表很简单，不需要数据
DROP TABLE xy;
CREATE TABLE xy(id NUMBER);
DROP TABLE yz;
CREATE TABLE yz(id NUMBER);
–将all_objects copy到表t中，以便测试
DROP TABLE t;
CREATE TABLE t AS SELECT * FROM all_objects;
CREATE INDEX idx_t ON t(object_name);
–分析表和索引
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER,tabname => ‘t’);
DBMS_STATS.GATHER_INDEX_STATS(ownname => USER,indname => ‘idx_t’);
END;
/
–运行完上述语句后查看表t行数
DINGJUN123>SELECT COUNT(*) FROM t;
COUNT(*)
———-
14006
已选择 1 行。
2. 问题引入:动态SQL构造
本节主要研究动态SQL解决where in list问题以及相关分析。
下面使用一个简单的拼凑条件进行初步试验，这里我使用的SQL是静态SQL，看看会发生什么情况?
DINGJUN123>SET SERVEROUTPUT ON SIZE 10000
DINGJUN123>DECLARE
2 v_condition VARCHAR2(100);
3 v_sql VARCHAR2(1000);
4 v_count NUMBER(10);
5 BEGIN
6 v_condition := ”’XY”’ || ‘,’ || ”’YZ”’; –本意是拼凑’XY’,’YZ’，有很多人会写成’XY,YZ’
7 SELECT COUNT(*)
8 INTO v_count
9 FROM t
10 WHERE object_name IN (v_condition);
11 –打印结果
12 DBMS_OUTPUT.PUT_LINE(v_count);
13 –打印SQL
14 v_sql := ‘SELECT COUNT(*) FROM t WHERE object_name IN (‘ ||
15 v_condition || ‘)’;
16 DBMS_OUTPUT.PUT_LINE(v_sql);
17 END;
18 /
0
SELECT COUNT(*) FROM t WHERE object_name IN (‘XY’,’YZ’)
从上面的结果看到，通过拼凑的SQL，打印出来的是SELECT COUNT(*) FROM t WHERE object_name IN (‘XY’,’YZ’)，看似正确的，但是为什么执行结果是0呢?下面分析一下，执行此SQL：
DINGJUN123>SELECT COUNT(*)
2 FROM t
3 WHERE object_name IN (‘XY’,’YZ’);
COUNT(*)
———-
2
已选择 1 行。
的确是有结果的，但是为什么在PL/SQL中执行拼凑的静态SQL没有结果呢?原因在于在PL/SQL中打印出的SQL不是真正执行的SQL，打印的是动态拼凑的SQL，而真正执行的是静态SQL，注意：
SELECT COUNT(*) INTO v_count FROM t WHERE object_name IN (v_condition);
中的v_condition是一个varchar2类型，在静态SQL中拼凑的条件相当于一个整体，’XY’,’YZ’是一个字符串，在SQL中相当于”’XY”,”YZ”’，因此实际执行的SQL是：
DINGJUN123>SELECT COUNT(*)
2 FROM t
3 WHERE object_name IN (”’XY”,”YZ”’);
COUNT(*)
———-
0
没有找到数据，而不是SELECT COUNT(*) FROM t WHERE object_name IN (‘XY’,’YZ’)则返回2
这个错误很多人初次解决类似问题会碰到，而且可能百思不得其解，通过上面的分析，你可能已经发现静态SQL与动态SQL有很多不同的地方值得注意。
使用动态SQL，就可以正确查询结果了，如下：
DINGJUN123> DECLARE
2 v_condition VARCHAR2(100);
3 v_sql VARCHAR2(1000);
4 v_count NUMBER(10);
5 BEGIN
6 v_condition:=”’XY”’||’,’||”’YZ”’;
–放入动态SQL中，结果正确
8 v_sql:=’SELECT COUNT(*) FROM t WHERE object_name IN (‘||v_condition||’)’;
9 EXECUTE IMMEDIATE v_sql INTO v_count;
10 DBMS_OUTPUT.PUT_LINE(v_count);
11 DBMS_OUTPUT.PUT_LINE(v_sql);
12 END;
13 /
2
SELECT COUNT(*) FROM t WHERE object_name IN (‘XY’,’YZ’)
PL/SQL 过程已成功完成。
现在的结果和预期结果一致，查询返回2。动态SQL的确可以解决这个问题，但是动态SQL会拼凑很多常量，而且数目不定，会导致无法使用绑定变量而影响效率(可能你认为可以使用动态SQL的USING,这是不行的，因为你不知道要绑定多少变量)，而且IN中列表数目限制最大是1000。所以，针对这种方法，在实际开发中不推荐使用。
3. 使用正则表达式解决
Oracle 10G支持正则表达式的确给程序开发带来了很大方便，正则表达式是字符串处理的利器，Perl、JAVA、JAVASCRIPT等主流开发语言都支持正则表达式，Oracle也意识到正则表达式的重要性，所以在10G中也引入了对正则表达式的支持。在本节中将使用正则表达式REGEXP_SUBSTR，将按指定分隔符组成的字符串转为中间查询结果集，然后使用子查询(IN、EXISTS)或JOIN解决where in list问题。对于正则表达式，如需详细了解，请参考官方文档，使用正则表达式解决where in list问题的方法如下：
DINGJUN123>VAR str VARCHAR2(100);
DINGJUN123>EXEC :str := ‘XY,YZ’;
PL/SQL 过程已成功完成。
DINGJUN123>SELECT COUNT(*)
2 FROM t
3 WHERE object_name IN
4 (
5 SELECT REGEXP_SUBSTR(:str, ‘[^,]+’, 1, LEVEL) AS value_str
6 FROM DUAL
7 CONNECT BY LEVEL <=
8 LENGTH(TRIM(TRANSLATE(:str,TRANSLATE(:str, ‘,’, ‘ ‘), ‘ ‘))) + 1
9 );
COUNT(*)
———-
2
已选择 1 行。
上面的SQL使用REGEXP_SUBSTR将逗号分隔的字符串转为行结果集，其中LENGTH(TRIM(TRANSLATE(:str,TRANSLATE(:str, ‘,’, ‘ ‘), ‘ ‘))) + 1就是查询出有多少个值列表，注意’ ‘是1个空格，当然也可以使用LENGTH(:str) -LENGTH(REPLACE(:str,
‘,’,”))+1实现(这里的”是空字符串，相当于NULL)，这种方法在10G环境中的大部分情况下是可以使用的，好处是可以用到绑定变量，而且列表数可以超过1000个。这里的正则表达式的子查询还是有点复杂的，下一节会讲解如何将子查询封装为一个动态视图，从而屏蔽子查询的复杂性。
4. 使用常规字符串函数以及动态视图
上一节使用正则表达式解决where in list问题，但是如果你的Oracle版本较低(10G之前)，无法使用正则表达式怎么办?那么就可以用本节的知识来解决了，使用INSTR、SUBSTR等函数处理指定分隔符的字符串，将字符串按分隔符转为行，这样就可以像上一节那样处理了。
首先要解决的问题就是如何使用INSTR、SUBSTR等函数将字符串按分隔符转为多行记录，比如对于’ab,bc,cd’这个字符串来说，要转为3行记录分别为’ab’、’bc’、’cd’，如何转换呢?一般要用到CONNECT BY的，试想如果将字符串转为’,ab,bc,cd,’，那么就很好转换了，找第1个值’ab’就是从第1个逗号后面的位置开始，然后截取的长度就是第2个逗号位置-第1个逗号位置-1，其他值类似，有了这个分析，就能很好实现这个需求了：
DINGJUN123>VAR str VARCHAR2(100);
DINGJUN123>EXEC :str := ‘ab,bc,cd’;
PL/SQL 过程已成功完成。
— LENGTH(:str)-LENGTH(REPLACE(:str,’,’,”))+1是计算有多少个值，和前一节的TRANSLATE一样
DINGJUN123>SELECT
2 SUBSTR (inlist,
3 INSTR (inlist, ‘,’, 1, LEVEL ) + 1,
4 INSTR (inlist, ‘,’, 1, LEVEL+1)
5 – INSTR (inlist, ‘,’, 1, LEVEL) -1 )
6 AS value_str
7 FROM (SELECT ‘,’||:str||’,’ AS inlist
8 FROM DUAL)
9 CONNECT BY LEVEL <=
10 LENGTH(:str)-LENGTH(REPLACE(:str,’,’,”)) + 1;
VALUE_STR
————————————————————
ab
bc
cd
已选择3行。
有了上面的结果作为子查询就和正则表达式一样可以解决where in list问题，在上一节我说过，这样的子查询可能会很复杂，为了隐藏子查询的复杂性，可以将子查询封装为一个动态视图，所谓动态视图就是传入不同的字符串，视图的结果是不同的，那么如何实现动态视图功能呢?
在PL/SQL中有内置包DBMS_SESSION，这个包的方法SET_CONTEXT可以创建绑定名字的上下文，并且具有属性名和属性值，通过SYS_CONTEXT函数就可以获取指定上下文的属性值。这样只要视图中的字符串值是通过SYS_CONTEXT获取的就可以了，每次调用存储过程重置CONTEXT。注意创建CONTEXT必须在一个命名过程或包过程中调用DBMS_SESSION.SET_CONTEXT，而不能在匿名过程中直接使用DBMS_SESSION.SET_CONTEXT，对于DBMS_SESSION包的详细使用请参考相关文档。详细如下：
1) 创建上下文
–这个上下文的名字是INLIST_CTX，需要由过程SET_INLIST_CTX_PRC创建
DINGJUN123>CREATE OR REPLACE CONTEXT INLIST_CTX USING set_inlist_ctx_prc;
上下文已创建。
2) 建立与上下文创建相关的过程
DINGJUN123>CREATE OR REPLACE PROCEDURE set_inlist_ctx_prc(p_val IN VARCHAR2)
2 /**
3 ||程序说明:
4 ||上下文INLIST_CTX属性名为STR
5 ||p_val为属性对应的值
6 **/
7 AS
8 BEGIN
9 DBMS_SESSION.set_context(‘INLIST_CTX’, ‘STR’, p_val);
10 END;
11 /
过程已创建。
3) 建立视图
–创建动态视图，让SYS_CONTEXT动态给视图传参，只需要将前面语句中的绑定变量:str改为SYS_CONTEXT(‘INLIST_CTX’, ‘STR’)就可以了
DINGJUN123>CREATE OR REPLACE VIEW v_inlist
2 AS
3 SELECT
4 SUBSTR (inlist,
5 INSTR (inlist, ‘,’, 1, LEVEL ) + 1,
6 INSTR (inlist, ‘,’, 1, LEVEL+1)
7 – INSTR (inlist, ‘,’, 1, LEVEL) -1 )
8 AS value_str
9 FROM (SELECT ‘,’||SYS_CONTEXT(‘INLIST_CTX’, ‘STR’)||’,’
10 AS inlist
11 FROM DUAL)
12 CONNECT BY LEVEL <=
13 LENGTH(SYS_CONTEXT(‘INLIST_CTX’, ‘STR’))
14 -LENGTH(REPLACE(SYS_CONTEXT(‘INLIST_CTX’, ‘STR’),’,’,”))+1;
视图已创建。
4) 测试
下面测试此动态视图，看是否满足要求：
–创建上下文，并给予属性STR初始值为’ab,bc,cd’
DINGJUN123>EXEC set_inlist_ctx_prc(‘ab,bc,cd’);
PL/SQL 过程已成功完成。
–视图成功输出3行记录
DINGJUN123>SELECT value_str
2 FROM v_inlist;
VALUE_STR
————–
ab
bc
cd
已选择3行。
–修改上下文的属性值，则视图也改变
DINGJUN123>EXEC set_inlist_ctx_prc(‘x,y,z’);
PL/SQL 过程已成功完成。
DINGJUN123>SELECT value_str
2 FROM v_inlist;
VALUE_STR
—————
x
y
z
已选择3行。
通过测试发现，动态视图正常工作，而且因为保存在CONTEXT内的属性是在SESSION范围内的，具有很好的并发性。
下面就用这个动态视图实现本章讨论的where in list问题，其实很简单，只要将视图放入到子查询中即可，如下：
–先重置CONTEXT
DINGJUN123>EXEC set_inlist_ctx_prc(‘XY,YZ’);
PL/SQL 过程已成功完成。
DINGJUN123>SELECT COUNT(*)
2 FROM t
3 WHERE object_name IN
4 (
5 SELECT value_str
6 FROM v_inlist
7 );
COUNT(*)
———-
2
已选择 1 行。
这个查询是符合要求的，使用动态视图，可以隐藏查询的复杂性，只需要每次查询前调用存储过程重置CONTEXT即可，而且和正则表达式一样，列表数目可以不定，也使用到了绑定变量。
本节主要讨论使用INSTR+SUBSTR代替正则表达式在低版本Oracle中的使用，并且介绍了使用DBMS_SESSION包创建CONTEXT和建立动态视图放入子查询中隐藏查询复杂性的方法。
5. 使用集合构造伪表
对这类问题的常规解法，比如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 );
END LOOP;
RETURN v_result;
END;
/
执行上面的脚本，创建嵌套表和返回嵌套表的函数即可。下面使用上面创建的嵌套表和函数来解决where in list的问题。通过函数将传入的字符串包装成嵌套表，然后利用TABLE函数将嵌套表转为伪表，放到子查询中即可。具体操作如下：
DINGJUN123>VAR str VARCHAR2(100);
DINGJUN123>EXEC :str :=’XY,YZ’;
PL/SQL 过程已成功完成。
DINGJUN123>SELECT COUNT(*)
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函数改改就可以了，这个读者可以自己思考一下，这里不详细讲解。
6. Where In List性能问题
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固化子查询，这样一般可以获得最大性能。
7. Where In List问题总结
本章对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具有”cardinality feedback“特性，这个特性使CBO具有智能化自我学习功能，本章测试的TABLE函数的默认基数CBO认为是8168(因为我的数据库数据块大小是8K)，但是到11GR2使用”cardinality feedback“特性就会改变这一情况了，也可以减少一堆hint的使用，其实很简单，这里就不测试了。如果你的环境是11GR2，可以到网上搜索Oracle cardinality feedback特性的相关文章来学习。
8. 作者简介
丁俊，网名：dingjun123
ITPUB开发版资深版主、ITPUB社区专家、ITPUB名人堂成员、ChinaUnix BLOG专家，
ITPUB 2010-2013连续4届最佳精华获得者、2011-2014连续4届最佳版主。
电子工业出版社终身荣誉作者，《剑破冰山-Oracle开发艺术》副主编。
曾多次参与和ORACLE相关的公共活动：ITPUB 2011演讲嘉宾、OOW 2013 上海 weibo特使等.
更多信息请访问：http://www.acoug.org/members/1734.html
