oracle 开发误区探索《一》

    环境：

sys@ORCL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

sys@ORCL> !uname -a
Linux localhost.localdomain 2.6.18-308.el5xen #1 SMP Fri Jan 27 17:59:00 EST 2012 i686 i686 i386 GNU/Linux

    ① 单列和复合列NOT IN 子查询

    not in (........)里面的null，如果存在null，则返回的绝对是空值。因为，in本是或的关系，加上not，则任何值和null，逻辑与，其结果都是空。

    测试：

    --Q1;单列not in子查询有null分析

        hr@ORCL> drop table test1;
        hr@ORCL> drop table test2;
        hr@ORCL> create table test1 (id number);
       
        Table created.
       
        hr@ORCL> create table test2 (id number);
       
        Table created.
       
        hr@ORCL> insert into test1 values(1);
       
        1 row created.
       
        hr@ORCL> insert into test1 values(2);
       
        1 row created.
       
        hr@ORCL> insert into test2 values(null);
       
        1 row created.
       
        hr@ORCL> insert into test2 values(1);
       
        1 row created.
       
        hr@ORCL> commit;
       
        Commit complete.

      --需求：选出在test1、却不在test2的记录
      --单列、常见错误如下、没有结果：

        hr@ORCL> select id from test1 where id not in (select id from test2);
       
        no rows selected

      --正确的写法、常见的还是not exists

        hr@ORCL> select id from test1 where not exists (select 1 from test2 where test1.id=test2.id);
       
                ID
        ----------
                 2

    --Q2：复合列not in子查询有null分析

        hr@ORCL> create table t1 (a number,b number);
       
        Table created.
       
        hr@ORCL> create table t2 (a number,b number);
       
        Table created.
       
        hr@ORCL> insert into t1 values(1,1);
       
        1 row created.
       
        hr@ORCL> insert into t1 values(1,2);
       
        1 row created.
       
        hr@ORCL> insert into t2 values(1,1);
       
        1 row created.
       
        hr@ORCL> insert into t2 values(null,2);
       
        1 row created.
       
        hr@ORCL> commit;
       
        Commit complete.

       --需求：选出在t1、却不在t2的记录
       --常见错误、和Q1一样、没有结果

        hr@ORCL> select * from t1 where (a,b) not in (select * from t2);   
       
        no rows selected
       
       
        --正确解法、常见的是not exists
        hr@ORCL> select * from t1 where not exists (select 1 from t2 where t1.a=t2.a and t1.b=t2.b);
       
                 A          B
        ---------- ----------
                 1          2
        --分析
          因为是复合列，相当于列的组合条件是or，根据NULL的比较和逻辑运算规则，OR条件有一个为TRUE则返回TRUE，全为FALSE则结果为FALSE，其他为UNKNOWN，
          比如(1,2) not in (null,2)则相当于1 <> null or 2 <> 2,那么明显返回的结果是UNKNOWN，所以不可能为真，不返回结果；
          但是(1,2) not in (null,3)相当于1 <> null or 2 <> 3,因为2<>3的已经是TRUE,所以条件为TRUE，返回结果；
          再做个简单测试：
        hr@ORCL> select * from dual;
       
        D
        -
        X
        hr@ORCL> select * from dual where (1,1) not in ((null,2));
       
        D
        -
        X
        hr@ORCL> select * from dual where (1,1) not in ((null,1));
       
        no rows selected

 

 

    ② 消除隐式转换

 

        在所有的auto trace分析中，都带有Predicate information的关键字。Predicate information信息有两种取值：filter和access，一般索引读和hash join，体现为access。
        关注Predicate information最重要的一点在于，查看是否发生了数据类型转换。
        数据类型转换，既产生了开销，也影响索引的正常使用
        所以，任何时刻都不该在此处看到有数据类型的转化
      --测试
      hr@ORCL> drop table t;
     
      Table dropped.
     
      hr@ORCL> create table t(col1 varchar2(20),col2 number);
     
      Table created.
     
      hr@ORCL> insert into t select rownum,rownum+1 from dual connect by level <=10000;
     
      10000 rows created.
     
      hr@ORCL> commit;
     
      Commit complete.
     
      hr@ORCL> create index idx_t on t(col1);
     
      Index created.
     
      hr@ORCL> set autot traceonly
      hr@ORCL> select * from t where col1=2 and col2=3;
     
     
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 1601196873
     
      --------------------------------------------------------------------------
      | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------
      |   0 | SELECT STATEMENT  |      |     1 |    25 |     6   (0)| 00:00:01 |
      |*  1 |  TABLE ACCESS FULL| T    |     1 |    25 |     6   (0)| 00:00:01 |
      --------------------------------------------------------------------------
     
      Predicate Information (identified by operation id):
      ---------------------------------------------------
     
         1 - filter("COL2"=3 AND TO_NUMBER("COL1")=2)
     
      Note
      -----
         - dynamic sampling used for this statement
     
     
      Statistics
      ----------------------------------------------------------
                5  recursive calls
                0  db block gets
               48  consistent gets
                0  physical reads
                0  redo size
              463  bytes sent via SQL*Net to client
              385  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
                1  rows processed

      --通过statistics，可知：获取1行产生48个逻辑读，不符合获取单行记录小于5个逻辑读的标准
      --在Predicate information信息里，我们发现此处存在TO_NUMBER("COL1")=2的类型转换
      --查看t表的col1字段类型为varchar2
      hr@ORCL> select * from t where col1='2' and col2=3;
     
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 1594971208
     
      -------------------------------------------------------------------------------------
      | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
      -------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT            |       |     1 |    25 |     2   (0)| 00:00:01 |
      |*  1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    25 |     2   (0)| 00:00:01 |
      |*  2 |   INDEX RANGE SCAN          | IDX_T |     1 |       |     1   (0)| 00:00:01 |
      -------------------------------------------------------------------------------------
     
      Predicate Information (identified by operation id):
      ---------------------------------------------------
     
         1 - filter("COL2"=3)
         2 - access("COL1"='2')
     
      Note
      -----
         - dynamic sampling used for this statement
     
     
      Statistics
      ----------------------------------------------------------
                0  recursive calls
                0  db block gets
                4  consistent gets
                0  physical reads
                0  redo size
              463  bytes sent via SQL*Net to client
              385  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
                1  rows processed
     
      --执行计划由全表扫描变更为索引读
      --在Predicate information，access("COL1"='2')表示用到了索引方式的访问路径
      --返回行与逻辑读的比率也<5