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

天涯倦客的博客

祝福你朋友永远快乐!

 
 
 

日志

 
 

Str2varlist与 str2numlist 介绍  

2017-07-30 16:43:11|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
作者:piner (转载请注明本文出处:Ixdba.com )

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

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

针对数据类型的:

  1. CREATE OR REPLACE TYPE NUMTABLETYPE as table of number;

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

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

然后创建两个相关的函数

数字列表函数

  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;

字符列表函数

  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的绑定了。如可以采用如下的三种方案

  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;
  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);
  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个逻辑读左右。如:

  1. SQL> SELECT /*+ ordered use_nl(a,u) */ id, user_id
  2.   2   from table(STR2NUMLIST('1,2,3')) a,
  3.   3   bmw_users u
  4.   4*  where u.user_id = a.column_value
  5. Execution Plan
  6. ----------------------------------
  7.    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3279 Card=8168 Bytes =334888)
  8.    1    0   NESTED LOOPS (Cost=3279 Card=8168 Bytes=334888)
  9.    2    1     COLLECTION ITERATOR (PICKLER FETCH) OF 'STR2NUMLIST'
  10.    3    1     TABLE ACCESS (BY INDEX ROWID) OF 'BMW_USERS' (Cost=1 Card=1 Bytes=39)
  11.    4    3       INDEX (UNIQUE SCAN) OF 'UK_BMW_USERS_USERID' (UNIQUE)
  12. Statistics
  13. ----------------------------------
  14.           0  recursive calls
  15.           0  db block gets
  16.          10  consistent gets
  17.           0  physical reads
  18.           0  redo size
  19. ……   
  20. /*###################*/
  21. SQL> SELECT  /*+ index(bmw_users UK_BMW_USERS_USERID) */ id, user_id
  22.   2   from bmw_users where user_id in
  23.   3    (SELECT * FROM THE (SELECT CAST(STR2NUMLIST('1,2,3') AS NUMTABLETYPE)
  24.   4*   FROM dual) WHERE rownum<1000)
  25. Execution Plan
  26. ----------------------------------
  27.    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=430 Card=999 Bytes=51948)
  28.    1    0   NESTED LOOPS (Cost=430 Card=999 Bytes=51948)
  29.    2    1     VIEW OF 'VW_NSO_1' (Cost=11 Card=999 Bytes=12987)
  30.    3    2       SORT (UNIQUE)
  31.    4    3         COUNT (STOPKEY)
  32.    5    4           COLLECTION ITERATOR (PICKLER FETCH) OF 'STR2NUMLIST'
  33.    6    5             TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=82)
  34.    7    1     TABLE ACCESS (BY INDEX ROWID) OF 'BMW_USERS' (Cost=1 Card=1 Bytes=39)
  35.    8    7       INDEX (UNIQUE SCAN) OF 'UK_BMW_USERS_USERID' (UNIQUE)
  36. Statistics
  37. ----------------------------------
  38.           0  recursive calls
  39.           0  db block gets
  40.           16  consistent gets
  41.           0  physical reads
  42.           0  redo size
  43. ……

 

 原文地址 http://Ixdba.com

  评论这张
 
阅读(20)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

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

页脚

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