分类目录归档：Oracle 开发

in/exists和not in/not exists执行效率

发表于 2011 年 12 月 16 日由惜分飞

一、IN 与EXISTS
1、理解
IN的执行流程
SELECT * FROM T1 WHERE X IN (SELECT Y FROM T2)
事实上可以理解为：
SELECT * FROM T1, (SELECT DISTINCT Y FROM T2) T2 WHERE T1.X = T2.Y
从这里可以看出，IN需要先处理T2表，然后再和T1进行关联

EXISTS的执行流程

SELECT * FROM T1 WHERE EXISTS (SELECT NULL FROM T2 WHERE Y = X)
--可以理解为：
for x in ( select * from t1 ) LOOP
    if ( exists ( select null from t2 where y = x.x )THEN 
        OUTPUT THE RECORD
    end if
end loop

从这里看出，EXISXTS会先查询T1表，然后再LOOP处理T2表

2、结论
对于in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了。
综合以上对IN/EXISTS的讨论，我们可以得出一个基本通用的结论：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

二、NOT IN 与NOT EXISTS
1、理解
NOT IN的执行流程
SELECT * FROM T1 WHERE X NOT IN (SELECT Y FROM T2)
事实上可以理解为：
SELECT * FROM T1, (SELECT DISTINCT Y FROM T2) T2 WHERE T1.X != T2.Y

NOT EXISTS的执行流程

SELECT .. .. .
  FROM ROLLUP R
 WHERE NOT EXISTS
 (SELECT 'Found' FROM TITLE T WHERE R.SOURCE_ID = T.TITLE_ID);
--可以理解为:
for x in ( select * from rollup )
       loop
           if ( not exists ( that query ) ) then
                  OUTPUT
           end if;
        end;

注意:NOT EXISTS 与 NOT IN 不能完全互相替换，看具体的需求。如果选择的列可以为空，则不能被替换。具体见:in/exists和not in/not exists语意探讨

2、结论
not in 只有当子查询中，select 关键字后的字段有not null约束或者有这种暗示时用not in,另外如果主查询中表大，子查询中的表小但是记录多，则应当使用not in,并使用anti hash join.如果主查询表中记录少，子查询表中记录多，并有索引，可以使用not exists,另外not in最好也可以用/*+ HASH_AJ */或者外连接+is null.一般情况下建议使用not exists

--比如:
SELECT .. .. ..
  FROM ROLLUP R
 WHERE NOT EXISTS
 (SELECT 'Found' FROM TITLE T WHERE R.SOURCE_ID = T.TITLE_ID);
--改成
SELECT .. .. ..
  FROM TITLE T, ROLLUP R
 WHERE R.SOURCE_ID = T.TITLE_ID(+)
   AND T.TITLE_ID IS NULL;
--或者
SELECT /*+ HASH_AJ */
.. .. ..
  FROM ROLLUP R
 WHERE OURCE_ID NOT IN
       (SELECT OURCE_ID FROM TITLE T WHERE OURCE_ID IS NOT NULL);

发表在 Oracle 开发 | 2 条评论

修改clob字段并插入新表

发表于 2011 年 12 月 14 日由惜分飞

最近开发有个需求，需要替换一张表的clob字段中的某些字符串，然后插入的一张新表中，我查询了一些资料，利用function结合匿名块模拟实现
0、数据库版本

SQL> select * from v$version;

BANNER
-----------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

1、创建替换clob中字符串函数

create or replace function replaceClob_new (
srcClob IN CLOB,
replaceStr IN VARCHAR2,
replaceWith IN VARCHAR2
--newClob OUT CLOB
) RETURN CLOB
IS

vBuffer    VARCHAR2 (32767);
l_amount   BINARY_INTEGER := 32767;
l_pos      PLS_INTEGER := 1;
l_clob_len PLS_INTEGER;
newClob  CLOB;

BEGIN
   newClob  := EMPTY_CLOB;
  -- initalize the new clob
 dbms_lob.createtemporary(newClob,TRUE);
 l_clob_len := dbms_lob.getlength(srcClob);
 WHILE l_pos < l_clob_len
  LOOP
    dbms_lob.read(srcClob, l_amount, l_pos, vBuffer);
    IF vBuffer IS NOT NULL THEN
      -- replace the text
      vBuffer := replace(vBuffer, replaceStr, replaceWith);
      -- write it to the new clob
      dbms_lob.writeappend(newClob, LENGTH(vBuffer), vBuffer);
    END IF;
    l_pos := l_pos + l_amount;
  END LOOP;
  RETURN newclob;
  
 EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;

2、创建原表，并测试该函数

SQL> CREATE TABLE t_clob(ID NUMBER,clob_xff CLOB);
 
Table created

--手工通过plsql dev插入一些数据（大于4000）

SQL> select dbms_lob.getlength(clob_xff) from t_clob;
 
DBMS_LOB.GETLENGTH(CLOB_XFF)
----------------------------
                        4856

SQL> select dbms_lob.getlength(replaceClob_new(clob_xff,'function','1')) from t_clob;
 
DBMS_LOB.GETLENGTH(REPLACECLOB
------------------------------
                          4814

SQL> set long 50
SQL> select clob_xff from t_clob where id=1;

CLOB_XFF
--------------------------------------------------
create or replace function replaceClob_new (
srcCl

SQL> select replaceClob_new(clob_xff,'function','1')
  2  from t_clob where id=1;

REPLACECLOB_NEW(CLOB_XFF,'FUNCTION','1')
--------------------------------------------------
create or replace 1 replaceClob_new (
srcClob IN C

3、编写匿名块实现插入功能

DECLARE
  A_CLOB CLOB;
  R_CLOB CLOB;
  VSTRT  NUMBER(4);
BEGIN
--否则会在新表的clob字段头部会有空格
  VSTRT := 1;
  SELECT REPLACECLOB_NEW(CLOB_XFF, 'function', 'xifenfei')
    INTO R_CLOB
    FROM T_CLOB
   WHERE ID = 1;
  INSERT INTO T_CLOB_NEW
    (ID, CLOB_XFF)
  VALUES
    (1, EMPTY_CLOB())
  RETURNING CLOB_XFF INTO A_CLOB;
  DBMS_LOB.WRITE(A_CLOB, DBMS_LOB.GETLENGTH(R_CLOB), VSTRT, R_CLOB);
  COMMIT;
END;

4、测试匿名块功能

SQL> set long 50
SQL> select clob_xff from t_clob where id=1;

CLOB_XFF
--------------------------------------------------
create or replace function replaceClob_new (
srcCl

SQL> select clob_xff from t_clob_new where id=1;

CLOB_XFF
--------------------------------------------------
create or replace xifenfei replaceClob_new (
srcCl

参考:http://space.itpub.net/111631/viewspace-605827

发表在 Oracle 开发 | 评论关闭

in/exists和not in/not exists语意探讨

发表于 2011 年 12 月 10 日由惜分飞

本篇只讨论in/exists和not in/not exists语意，不涉及这些写法的执行效率问题，至于效率问题请见:in/exists和not in/not exists执行效率
1、准备实验环境

C:\Users\XIFENFEI>sqlplus chf_xff/xifenfei

SQL*Plus: Release 11.2.0.1.0 Production on 星期六 12月 10 14:55:14 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> create table t1 (c1 number,c2 number);

表已创建。

SQL> create table t2 (c1 number,c2 number);

表已创建。

SQL> insert into t1 values (1,2);

已创建 1 行。

SQL> insert into t1 values (1,3);

已创建 1 行。

SQL> insert into t2 values (1,2);

已创建 1 行。

SQL> insert into t2 values (1,null);

已创建 1 行。

SQL> insert into t1 values (1,null);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from t1;

        C1         C2
---------- ----------
         1          2
         1          3
         1

SQL> select * from t2;

        C1         C2
---------- ----------
         1          2
         1

Note:t1和t2表都有null，且t1比t2多一条记录

2、t2做内部表

SQL> select * from t1 where c2 in (select c2 from t2 );

        C1         C2
---------- ----------
         1          2

SQL> select * from t1 where exists (select c2 from t2 where t1.c2=t2.c2);

        C1         C2
---------- ----------
         1          2

SQL> select * from t1 where c2 not in (select c2 from t2 );

未选定行

SQL> select * from t1 where not exists (select 1 from t2 where t1.c2=t2.c2);

        C1         C2
---------- ----------
         1          3
         1

3、t1为内部表

SQL> select * from t2 where c2 in (select c2 from t1 );

        C1         C2
---------- ----------
         1          2

SQL> select * from t2 where exists (select c2 from t1 where t1.c2=t2.c2);

        C1         C2
---------- ----------
         1          2

SQL> select * from t2 where c2 not in (select c2 from t1 );

未选定行

SQL> select * from t2 where not exists (select 1 from t1 where t1.c2=t2.c2);

        C1         C2
---------- ----------
         1

3、结论
in和exists结果相同(都会排除掉null,无论内部表中有无null)
not in会过滤掉外部表中的null(即使内部表中无null)
not exists不会过滤掉外部表的null(即使内部表有null)
由于篇幅关系,括号中的部分实验过程未展现出来

发表在 Oracle 开发 | 评论关闭

17813235971

QQ 咨询

分类目录归档：Oracle 开发

in/exists和not in/not exists执行效率

修改clob字段并插入新表

in/exists和not in/not exists语意探讨

文章分类

最近发表

分类目录归档：Oracle 开发

in/exists和not in/not exists执行效率

修改clob字段并插入新表

in/exists和not in/not exists语意探讨

标签云

文章分类

最近发表