在实际使用中，经常会有带in的子查询，如where id in (1,2,3)这样的情况，但是如果很多这样的语句在数据库中出现，将引起数据库的大量硬解析与共享池SQL碎片。所以，在实际应用中，可以采用其他方法，将这些in list给绑定起来。
如果需要绑定in list，首先，需要创建两个类型(type)：
针对数据类型的：
Sql代码
- CREATE OR REPLACE TYPE NUMTABLETYPE as table of number;
CREATE OR REPLACE TYPE NUMTABLETYPE as table of number;
针对字符串类型的(每个list的单元大小不要超过1000字节)
Sql代码
- create or replace type vartabletype as table of varchar2(1000);
create or replace type vartabletype as table of varchar2(1000);
然后创建两个相关的函数
数字列表函数
Sql代码
- 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 str2numList( p_string in varchar2 ) return numTableTypeas 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;
字符列表函数
Sql代码
- 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;
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的绑定了。如可以采用如下的三种方案
Sql代码
- 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 /*+ 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;Sql代码
- 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 /*+ leading(a) */ id, user_id, BITAND(promoted_type,4) busauth from bmw_users u where user_id in (select * from table(STR2NUMLIST(:bind0)) a);Sql代码
- 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);
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代码
- 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
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
0 db block gets
10 consistent gets
0 physical reads
0 redo size
……
/*###################*/
Sql代码
- SQL> SELECT /*+ index(bmw_users UK_BMW_USERS_USERID) */ id, user_id
- from bmw_users where user_id in
- (SELECT * FROM THE (SELECT CAST(STR2NUMLIST('1,2,3') AS NUMTABLETYPE)
- FROM dual) WHERE rownum<1000)
SQL> SELECT /*+ index(bmw_users UK_BMW_USERS_USERID) */ id, user_id from bmw_users where user_id in (SELECT * FROM THE (SELECT CAST(STR2NUMLIST('1,2,3') AS NUMTABLETYPE) FROM dual) WHERE rownum<1000)
Execution Plan
----------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=430 Card=999 Bytes=51948)
1 0 NESTED LOOPS (Cost=430 Card=999 Bytes=51948)
2 1 VIEW OF 'VW_NSO_1' (Cost=11 Card=999 Bytes=12987)
3 2 SORT (UNIQUE)
4 3 COUNT (STOPKEY)
5 4 COLLECTION ITERATOR (PICKLER FETCH) OF 'STR2NUMLIST'
6 5 TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=82)
7 1 TABLE ACCESS (BY INDEX ROWID) OF 'BMW_USERS' (Cost=1 Card=1 Bytes=39)
8 7 INDEX (UNIQUE SCAN) OF 'UK_BMW_USERS_USERID' (UNIQUE)
Statistics
----------------------------------
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
……
Oralce中使用str2numlist函数需要注意的问题
1.str2numlist的默认长度是4000字符。
2.使用TABLE(str2numlist(#transid#)) 语句时，有时会出现 ORA-22905错：cannot access rows from a non-nest table item.
就能避免此错误。