当前位置:首页 > Articles > 正文
Where In List问题解析 –丁俊已关闭评论

Where In List问题解析 –丁俊

摘要:这篇文章重点是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问题总结

111111_meitu_1

本章对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

 

 

本文章由 Eygle 于2015年04月19日发布在Articles分类下,
转载请注明:Where In List问题解析 –丁俊-中国 Oracle 用户组

好文章就要一起分享!

更多

评论已关闭!