`

怎么对IN子查询使用绑定变量

阅读更多

 

 

在实际使用中,经常会有带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个逻辑读左右

 

例子:

 

create or replace procedure SP_MOVE_DATA(p_string in varchar2)

is

 

begin

 

update t1 set name= 'Orphean' where t1.id in

(select * from table(str2numList(p_string)));

 

insert into t2

select * from t1 where id in

(select * from table(str2numList(p_string)));

 

delete from t1 where id in

(select * from table(str2numList(p_string)));

 

commit;

 

exception

when others then

rollback;

 

end SP_MOVE_DATA;

-----------------


分享到:
评论

相关推荐

    SQL&PL SQL FAQ第二版

    怎么对IN子查询使用绑定变量 在 SQL 查询中使用 `IN` 子查询时,如果能够使用绑定变量替换硬编码的值,这将大大提高性能并减少硬解析的次数。在 PL/SQL 中,可以通过数组或集合来实现这一点。 ```plsql DECLARE ...

    oracle编程的性能注意点

    此外,避免使用in/exists、not in/not exists子查询,转而采用直接连接的方式也是提高性能的有效手段。这些子查询可能导致全表扫描和多次表访问,尤其是在数据量大的情况下,性能开销显著。例如,使用NOT IN或NOT ...

    ORACLE培训SQL性能优化.pptx

    在解析阶段,ORACLE 会检查语法、语义和权限,并合并视图定义和子查询。然后,在绑定阶段,ORACLE 会查找绑定变量并赋值。在执行阶段,ORACLE 会应用执行计划并执行必要的 I/O 和排序操作。最后,在提取阶段,ORACLE...

    birt数据集参数与列绑定实现

    1. 数据集参数允许我们传递变量给数据集查询。比如,我们可以设置一个日期参数,让使用者选择一个日期范围,然后数据集会根据这个范围来筛选数据。 2. 参数可以在报告设计中创建,并在数据集的查询语句中使用,用`?`...

    Oracle性能查询包使用

    4. **子查询优化**:子查询可能会导致多次表访问,考虑使用连接(JOIN)或连接重写来替换子查询。 三、性能监控与调优工具 1. **V$视图**:Oracle提供了一系列V$视图,如V$SESSION, V$SQL, V$BUFFER_CACHE等,用于...

    Oracle-sql-Performance-Tuning-30Tips.rar_oracle_tom kyte

    18. **使用绑定变量避免全表扫描**:全表扫描对性能影响极大,通过绑定变量可以避免因字符串比较导致的全表扫描。 19. **优化游标**:对于循环处理大量数据的游标,考虑使用BULK COLLECT和FORALL来提升性能。 20. ...

    Oracle数据库查询优化分析与研究.pdf

    6. **优化子查询**:减少子查询的使用,或利用关联子查询替代嵌套子查询,提高查询效率。 7. **注意排序和分组操作**:排序和分组操作会消耗大量资源,尽可能利用索引进行排序,避免不必要的全表排序。 8. **监控...

    oracle连接查询SQL性能测试

    4. **子查询与连接的比较**:在某些情况下,子查询可能比连接更高效,反之亦然。文章可能比较了两者在不同场景下的性能表现。 5. **并行查询**:Oracle支持并行执行查询,这可以在大型数据集上提高性能。不过,过度...

    Oracle数据库中基本的查询优化与子查询优化讲解

    8. **使用绑定变量**:在动态SQL中使用绑定变量可以减少解析次数,提高性能。 9. **选择适当的JOIN类型**:根据数据分布情况选择INNER JOIN、LEFT JOIN或RIGHT JOIN,有时可以使用MERGE JOIN或HASH JOIN来提高效率...

    【oracle】oracle查询优化改写

    4. **绑定变量**:使用绑定变量可以避免SQL硬解析,提高SQL语句的复用性,减少系统资源的消耗。 5. **提示(Hints)**:在SQL查询中添加提示可以指导优化器选择特定的执行计划,但这应谨慎使用,因为过度依赖提示...

    oracl数据库查询优化[定义].pdf

    15. **使用绑定变量**:避免在SQL语句中硬编码值,使用绑定变量可以提高复用性和减少解析开销。 通过以上策略,开发者可以显著提升Oracle数据库的查询性能,降低系统资源消耗,提高整体应用的响应速度。在实际操作...

    Oracle查询优化改写 技巧与案例

    4. **使用连接(JOIN)优化**:避免在JOIN条件中使用非等值连接,考虑使用外连接(OUTER JOIN)或自连接(SELF JOIN)替代子查询。 5. **避免在索引列上使用函数**:这会导致Oracle无法使用索引,除非创建函数索引。...

    SQL语句规范

    7. **优化子查询**:避免嵌套过多的子查询,尤其是当子查询结果集较大时。可以尝试转换为JOIN操作或者使用反向查询,如`NOT EXISTS`代替`NOT IN`,以提高查询效率。 8. **使用索引**:合理创建和使用索引是优化SQL...

    oracle sql优化 30例

    10. **使用绑定变量提示**:在某些复杂情况下,可能需要使用绑定变量提示(BINDING_AWARE或BIND_SAFELY)来指导优化器选择更好的执行计划。 11. **考虑使用索引合并**:Oracle支持索引合并策略,当有多个可用索引时...

    Oracle语句优化规则汇总

    10. **优化子查询**:尽可能地将子查询转化为连接操作,或者使用关联子查询。子查询可能会导致多次数据访问,而连接操作通常更有效。 11. **合理设计数据库模式**:确保数据类型最小化且适合存储的数据,避免冗余...

    Oracle 查询优化,个人练习

    6. **绑定变量**:使用绑定变量可以提高重用执行计划的效率,避免因参数化不佳导致的硬解析。 7. **查询重写**:通过数据库的查询重写功能,可以将复杂查询转化为更简单的形式,比如利用物化视图或索引组织表。 8....

    sql语句优化

    在处理动态SQL时,使用变量绑定(绑定变量)可以避免重复解析相同的SQL语句,从而减少解析开销。例如,定义一个变量`Vv_sql_demo`,将其赋值为SQL语句,然后使用`EXECUTE IMMEDIATE`执行,这样可以提高性能。 2. *...

    ASP.NET2.0(C#)TreeView绑定SQL数据库、以及对其进行添加修改删除

    - 绑定数据:在Page_Load事件中,使用`TreeView.DataSource = 数据源;`和`TreeView.DataBind();`将数据源绑定到TreeView。 2. **添加操作**: - 用户界面:在TreeView上添加编辑功能,如右键菜单或按钮,让用户...

    提高Oracle查询效率

    - **使用EXISTS替代IN**:当需要检查子查询结果是否包含主查询中的记录时,EXISTS通常比IN更快,因为它一旦找到匹配就停止。 3. **Oracle工具使用** - **Explain Plan**:Oracle自带的解释计划工具,可以分析SQL...

Global site tag (gtag.js) - Google Analytics