注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

小葫芦君(汉斯的博客)

博客迁移到新博客:https://blog.ssxingshou.com

 
 
 

日志

 
 
关于我

小小葫芦商城,为您提供高品质的商品,一流的产品,一流的包装服务,一流的物流服务,放心购买

网易考拉推荐

使用淘宝Str2varlist与str2numlist 代替 in/exist ,提升性能(Oracle)  

2012-09-29 09:33:33|  分类: 默认分类 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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

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

针对数据类型的:

Sql代码  收藏代码
  1. CREATE OR REPLACE TYPE NUMTABLETYPE as table of number;  
 

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

Sql代码  收藏代码
  1. create or replace type vartabletype as table of varchar2(1000);  
 

然后创建两个相关的函数

数字列表函数

Sql代码  收藏代码
  1. create or replace function str2numList( p_string in varchar2 ) return numTableType  
  2. as  
  3.     v_str long default p_string || ',';  
  4.     v_n number;  
  5.     v_data numTableType := numTableType();  
  6. begin  
  7.     loop  
  8.     v_n := to_number(instr( v_str, ',' ));  
  9.     exit when (nvl(v_n,0) = 0);  
  10.     v_data.extend;  
  11.     v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));  
  12.     v_str := substr( v_str, v_n+1 );  
  13.     end loop;  
  14.     return v_data;  
  15. end;  

字符列表函数

Sql代码  收藏代码
  1. create or replace function str2varList( p_string in varchar2 ) return VarTableType  
  2.  as  
  3.  v_str long default p_string || ',';  
  4.  v_n varchar2(2000);  
  5.  v_data VarTableType := VarTableType();  
  6.  begin  
  7.     loop  
  8.        v_n :=instr( v_str, ',' );  
  9.     exit when (nvl(v_n,0) = 0);  
  10.     v_data.extend;  
  11.     v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));  
  12.     v_str := substr( v_str, v_n+1 );  
  13.     end loop;  
  14.     return v_data;  
  15. end

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

Sql代码  收藏代码
  1. SELECT /*+ ordered use_nl(a,u) */ id, user_id, BITAND(promoted_type,4) busauth  
  2.      from table(STR2NUMLIST(:bind0)) a,  
  3.      bmw_users u  
  4.      where u.user_id = a.column_value;  
Sql代码  收藏代码
  1. SELECT  /*+ leading(a) */ id, user_id, BITAND(promoted_type,4) busauth  
  2.      from bmw_users u where user_id in  
  3.      (select * from table(STR2NUMLIST(:bind0)) a);  
Sql代码  收藏代码
  1. SELECT  /*+ index(bmw_users UK_BMW_USERS_USERID) */ id, user_id  
  2.      from bmw_users where user_id in  
  3.      (SELECT * FROM THE (SELECT CAST(STR2NUMLIST(:bind0) AS NUMTABLETYPE)  
  4.      FROM dual) WHERE rownum<1000);  

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

Sql代码  收藏代码
  1. SQL> SELECT /*+ ordered use_nl(a,u) */ id, user_id  from table(STR2NUMLIST('1,2,3')) a,     
  2. 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代码  收藏代码
  1. SQL> SELECT  /*+ index(bmw_users UK_BMW_USERS_USERID) */ id, user_id  
  2.     from bmw_users where user_id in  
  3.       (SELECT * FROM THE (SELECT CAST(STR2NUMLIST('1,2,3'AS NUMTABLETYPE)  
  4.     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
……
  评论这张
 
阅读(977)| 评论(1)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017