where in list问题
1.使用正则
var v_emp varchar2(200);
exec :v_emp := 'CLARK,MILLER,KING';
select * from emp
where ename in
(
SELECT regexp_substr(:v_emps, '[^,]+', 1, LEVEL) AS ename
FROM dual
CONNECT BY LEVEL <= (length(translate(:v_emps, ',' || :v_emps, ',')) + 1)
);
2.使用context
DBMS_SESSION.SET_CONTEXT (
namespace VARCHAR2,
attribute VARCHAR2,
value VARCHAR2,
username VARCHAR2,
client_id VARCHAR2 );
--这个上下文的名字是inlist_ctx,需要由过程set_inlist_ctx_prc创建
create or replace context inlist_ctx using set_inlist_ctx_prc;
create or replace procedure set_inlist_ctx_prc(p_val in varchar2)
/**
||程序说明:
||上下文inlist_ctx属性名为str
||p_val为属性对应的值
**/
as
begin
dbms_session.set_context('inlist_ctx','str',p_val);
end;
/
创建动态视图,让sys_context动态给视图传参数
create or replace view v_inlist
as
select substr(inlist,
instr(inlist,',',1,level)+1,
instr(inlist,',',1,level+1) - instr(inlist,',',1,level)-1) as value_str
from (select ','||sys_context('inlist_ctx','str')||',' as inlist from dual)
connect by level<=length(sys_context('inlist_ctx','str')) - length(replace(sys_context('inlist_ctx','str'),','))+1;
测试
exec set_inlist_ctx_prc('ab,bc,cd');
select value_str from v_inlist;
VALUE_STR
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ab
bc
cd
修改上下文的属性值,则试图也改变
exec set_inlist_ctx_prc('papapa,hahah,keke');
select value_str from v_inlist;
VALUE_STR
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
papapa
hahah
keke
通过测试发现,动态试图正常工作,而且因为保存在context内的树形是在session范围内的,具有很好地并发性。
exec set_inlist_ctx_prc;
select * from emp
where ename in (
select value_str from v_inlist);
写到过程里就是先接收传入的参数,然后执行set_inlist_ctx_prc,最后执行查询
3.使用集合构造伪表
一个函数用来解析字符串并返回一个集合,然后用table()函数
注意不能是index by表,必须是schema级别的类型,数组有容量也不常使用
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;
/
var str varchar2(100);
exec :str := 'XY,YZ';
select count(*) from emp where ename in (
select column_value from table(cast(f_str2list(:str) as varchar2_tt))
);
一般来说in列表返回行数都比较少,一般来说应该走NL
要注意的是,用table()函数CBO会认为rows为8168(其实这个默认的table函数基数是8168依赖于块大小,我这里是8K的块)
Execution Plan
----------------------------------------------------------
Plan hash value: 2659767359
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 33 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | HASH JOIN SEMI | | 1 | 8 | 33 (4)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 84 | 3 (0)| 00:00:01 |
| 4 | COLLECTION ITERATOR PICKLER FETCH| F_STR2LIST | 8168 | 16336 | 29 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"=VALUE(KOKBF$))
通过hint固定执行计划
select count(*) from emp where ename in (
select /*+ cardinality(tab,5) */column_value from table(cast(f_str2list(:str) as varchar2_tt) a)
);
多行in list
WITH TEMP AS
(
SELECT 1 INT_ID,'ni' NAME,'12,13,14,a,b,~' REGION_ID FROM DUAL
UNION
SELECT 2 INT_ID,'wo' NAME,'13,14,F' REGION_ID FROM DUAL
UNION
SELECT 3 INT_ID,'ta' NAME,'11' REGION_ID FROM DUAL
)
SELECT T1.INT_ID,T1.NAME,REGEXP_SUBSTR(T1.REGION_ID,'[0-9a-zA-Z~]+',1,T2.LEV) REGION_ID FROM
(SELECT TEMP.*,LENGTH(REGION_ID)-LENGTH(REPLACE(REGION_ID,',',''))+1 RN FROM TEMP) T1,
(SELECT LEVEL LEV FROM DUAL
CONNECT BY LEVEL <= (SELECT SUM(LENGTH(REGION_ID)-LENGTH(REPLACE(REGION_ID,',',''))+1) RN FROM TEMP)) T2
WHERE T2.LEV <= T1.RN ORDER BY INT_ID,LEV
select department_id,regexp_substr(first_name,'[^,]+',1,t2.lev) first_name from
(select x.*,LENGTH(first_name)-LENGTH(REPLACE(first_name,',',''))+1 RN FROM x) t1,
(select level lev from dual connect by level <=(SELECT SUM(LENGTH(first_name)-LENGTH(REPLACE(first_name,',',''))+1) RN FROM x)) T2
where t2.lev <= t1.rn