淘宝Str2varlist与str2numlist 引见-数据库教程-爱易网页

淘宝Str2varlist与str2numlist 引见

日期：2014-05-16　浏览次数：20403 次

淘宝Str2varlist与str2numlist 介绍

在实际使用中，经常会有带in的子查询，如where id in (1,2,3)这样的情况，但是如果很多这样的语句在数据库中出现，将引起数据库的大量硬解析与共享池SQL碎片。所以，在实际应用中，可以采用其他方法，将这些in list给绑定起来。

如果需要绑定in list，首先，需要创建两个类型(type)：

针对数据类型的：

CREATE OR REPLACE TYPE NUMTABLETYPE as table of number;

针对字符串类型的(每个list的单元大小不要超过1000字节)

create or replace type vartabletype as table of varchar2(1000);

然后创建两个相关的函数

数字列表函数

create or replace function str2numList( p_string in varchar2 ) return numTableType
as
    v_str long default p_string || ',';
    v_n number;
    v_data numTableType := numTableType();
begin
    loop
    v_n := to_number(instr( v_str, ',' ));
    exit when (nvl(v_n,0) = 0);
    v_data.extend;
    v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));
    v_str := substr( v_str, v_n+1 );
    end loop;
    return v_data;
end;

字符列表函数

create or replace function str2varList( p_string in varchar2 ) return VarTableType
 as
 v_str long default p_string || ',';
 v_n varchar2(2000);
 v_data VarTableType := VarTableType();
 begin
    loop
       v_n :=instr( v_str, ',' );
    exit when (nvl(v_n,0) = 0);
    v_data.extend;
    v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));
    v_str := substr( v_str, v_n+1 );
    end loop;
    return v_data;
end;

创建之后，我们就可以采用如下的方式来使用in list的绑定了。如可以采用如下的三种方案

SELECT /*+ ordered use_nl(a,u) */ id, user_id, BITAND(promoted_type,4) busauth
     from table(STR2NUMLIST(:bind0)) a,
     bmw_users u
     where u.user_id = a.column_value;

SELECT  /*+ leading(a) */ id, user_id, BITAND(promoted_type,4) busauth
     from bmw_users u where user_id in
     (select * from table(STR2NUMLIST(:bind0)) a);

SELECT  /*+ index(bmw_users UK_BMW_USERS_USERID) */ id, user_id
     from bmw_users where user_id in
     (SELECT * FROM THE (SELECT CAST(STR2NUMLIST(:bind0) AS NUMTABLETYPE)
     FROM dual) WHERE rownum<1000);

在如上的方案中，以上语句中的hint提示，是为了稳定执行计划，防止Oracle对in list的错误估计而导致走hash连接。一般建议采用第一种方法，比较简单可靠并且可以指定稳定的计划。但是要求数据库的版本比较高，在老版本中(8i)，可能只能采用第三种方法。总的来说，1、2两种方法比3要少6个逻辑读左右。如：

SQL> SELECT /*+ ordered use_nl(a,u) */ id, user_id  from table(STR2NUMLIST('1,2,3')) a,   
bmw_users u  where u.user_id = a.column_value

Execution Plan
----------------------------------
? ?0? ? ? SELECT STATEMENT Optimizer=CHOOSE (Cost=3279 Card=8168 Bytes =334888)
? ?1? ? 0? ?NESTED LOOPS (Cost=3279 Card=8168 Bytes=334888)
? ?2? ? 1? ? ?COLLECTION ITERATOR (PICKLER FETCH) OF 'STR2NUMLIST'
? ?3? ? 1? ? ?TABLE ACCESS (BY INDEX ROWID) OF 'BMW_USERS' (Cost=1 Card=1 Bytes=39)
? ?4? ? 3? ? ? ?INDEX (UNIQUE SCAN) OF 'UK_BMW_USERS_USERID' (UNIQUE)
Statistics
----------------------------------
? ? ? ? ? 0? recursive calls

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

淘宝Str2varlist与str2numlist 引见

相关资料更多>

推荐阅读更多>