`
boendev
  • 浏览: 243447 次
  • 性别: Icon_minigender_1
  • 来自: 上海
文章分类
社区版块
存档分类
最新评论

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

阅读更多

 

在实际使用中,经常会有带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
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
……   
/*###################*/
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
……
分享到:
评论

相关推荐

    JAVA排序算法比较

    冒泡排序的时间复杂度为O(n^2),其中n为数组长度,最坏情况下需要比较和交换n*(n-1)/2次。 ### 选择排序(Selection Sort) 选择排序是一种简单直观的排序算法。其工作原理如下:首先在未排序序列中找到最小(大)...

    8 面板门槛回归-王群勇xthreg命令详解.pdf

    xthreg depvar [indepvars] [if] [in], rx(varlist) qx(varname) [thnum(#) grid(#) trim(numlist) bs(numlist) thlevel(#) gen(newvarname) noreg nobslog thgiven options] ``` 其中,`depvar` 是因变量,`...

    很多java的经典算法 以及很多扩展算法

    选择排序在任何情况下都进行n(n-1)/2次比较,但不会进行不必要的交换;插入排序在数据量小或者基本有序的情况下有较好的效率。在面试中,了解这些排序算法的基本原理、复杂度分析以及适用场景是十分重要的,因为它们...

    JAVA经典算法收集整理

    numlist[in] = numlist[in-1]; --in; } numlist[in] = temp; } } ``` 这些排序算法虽然简单,但它们在理解和实现上都非常基础,是学习更复杂算法的基础。在实际应用中,根据数据规模和特定场景,可能需要选择...

    Java上机文档

    if (numlist[in] &gt; numlist[in + 1]) { int temp = numlist[in]; numlist[in] = numlist[in + 1]; numlist[in + 1] = temp; } } } } // 插入排序实现 private void insertSort(int[] numlist) { int in,...

    java经典算法汇总.pdf

    numlist[in]=numlist[in-1]; --in; } numlist[in]=temp; } } ``` 排序算法的比较 在main方法中,我们可以看到对冒泡排序、选择排序和插入排序的比较。我们可以看到,插入排序算法的执行速度最快,而冒泡排序...

    D3.js的基础部分之数组的处理数组的排序和求值(v3版本)

    2. `d3.descending(a, b)`:这是一个递减排序函数,与`d3.ascending`相反,当`a`大于`b`时返回`-1`,`a`小于`b`时返回`1`,相等时返回`0`。这将把数组 `[22, 44, 33, 11, 66]` 按降序排列: ```javascript ...

    jQuery个性化数字焦点图代码

    此外,还可以通过优化图片大小、使用懒加载等方法来提升页面性能。 总结,jQuery个性化数字焦点图是通过jQuery库实现的一种动态图片展示方式,它结合了HTML、CSS和JavaScript,为用户提供了一种交互性强、视觉效果...

    2 面板门槛回归模型Stata程序xthreg具体操作.pdf

    xthreg 命令的语法格式为:xthreg depvar [indepvars] [if] [in], rx(varlist) qx(varname) [thnum(#) grid(#) trim(numlist) bs(numlist) thlevel(#) gen(newvarname) noreg nobslog thgiven options],其中 depvar...

    (word完整版)python二级考试操作题7.doc

    海伦公式是:`area = sqrt[p*(p-a)*(p-b)*(p-c)]`,其中`p`是半周长,即`p = (a+b+c)/2`。代码模板已经给出了大部分结构,只需要按照要求填写即可。完整代码如下: ```python a, b, c = map(float, input().split...

    java泛型指导(挺全的)

    List&lt;? extends Number&gt; numList; // 只能接受Number或其子类的List List&lt;? super Integer&gt; intList; // 可以接受Integer或其超类的List ``` #### 7. 类型参数的限定 可以对类型参数添加限定,例如要求必须实现...

    vue3中使用tinyMCE

    在Vue3中,TinyMCE的使用既可以直接与TinyMCE库结合,也可以通过Vue插件实现。无论是哪种方式,关键在于理解Vue3的Composition API和生命周期钩子,以及TinyMCE的配置和API,这样才能有效地集成和管理编辑器实例。...

    浅谈PL/SQL批处理语句:BULK COLLECT与FORALL对优化做出的贡献

    在PL/SQL编程环境中,批处理语句是提高性能的关键技术之一,特别是在处理大量数据时。本文主要讨论了两个核心的批处理特性:BULK COLLECT 和 FORALL,它们对于优化PL/SQL程序的效率有着显著的贡献。 BULK COLLECT ...

    泛型&通配符常见面试题总结

    List&lt;? extends Number&gt; numList = new ArrayList(); numList.add(5); // 错误 Number n = numList.get(0); // 正确 ``` 4. 编写一个方法,接受两个列表,返回它们的交集,但不能修改输入列表。如何使用泛型和...

    在vue3项目中使用tinymce编辑器

    toolbar: 'undo redo | bold italic | alignleft aligncenter alignright alignjustify | bullist numlist outdent indent | link image', }); }, ``` 注意,你可能需要根据项目需求添加更多的插件或自定义工具...

    authorware变量、函数大全

    用法 numlist=[1,2,3] AddLinear(numList,99,1) NumList为[99,1,2,3] numList:=[1,2,3] Addlinear(numList,99,6) NumList为[1,2,3,0,0,99] 相关函数 参见系统函数SortByProperty和SortByValue。

    富文本tinymceCDN

    富文本编辑器TinyMCE是一款广泛使用的开源JavaScript库,它为网页提供强大的富文本编辑功能。在“富文本tinymceCDN”这个主题下,我们主要关注如何利用TinyMCE CDN(内容分发网络)来集成和使用这个编辑器。 ...

    该文为Java进阶部分同步代码 .rar

    这里实现方式事先说明下,采用json+文本流方式,即读取数据库信息至文本流中,格式采用json,之后读取文本中json数据至数据库中,实现度还不完全,不过也提供了...protected List numList=new ArrayList(); protected

    王群勇xthreg命令.pdf

    xthreg depvar [indepvars] [if] [in], rx(varlist) qx(varname) [thnum(#) grid(#) trim(numlist) bs(numlist) thlevel(#) gen(newvarname) noreg nobslog thgiven options] ``` 其中各参数的含义如下: - `depvar...

Global site tag (gtag.js) - Google Analytics