oracle 開發誤區探索《一》

    環境：

sys@ORCL> select * from v$version where rownum=1;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prodsys@ORCL> !uname -aLinux 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