Oracle 使用with要小心了--謂詞不能推進

Oracle 使用with要小心了--謂詞不能推進

今天看到一條SQL大量使用了with，開發人員為了邏輯清晰，把一些結果集先用with緩衝起來，後面還有很多地方用到這個結果集，原始的SQL需要執行2個多小時。最佳化方法是將把最先緩衝的SQL放到用的地方,最佳化後12s。

下面來類比這個情境，不用糾結SQL的意義，把當時的SQL抽象就是這樣的。可以看到SQL1中先把語句a中的結果緩衝起來，當語句b要用的時候，object_id上的索引是用不到的，它只有在結果集中過濾。簡單點說，SQL1相對於SQL2來說，謂詞沒有推進。所以在SQL的寫法上，追求書面上的清晰和效能要做一個平衡。

--製造資料

SQL> drop table test purge;

SQL> create table test as select * from dba_objects;
SQL> create index ind_t_object_id on test(object_id) nologging;
SQL> exec dbms_stats.gather_table_stats(user,'test',cascade => true);

SQL> set autotrace traceonly

--SQL1，最佳化前沒有謂詞推進

SQL> with a as(select * from test where object_type='TABLE'),
    b as (select count(1) from a where object_id<10),
    c as (select count(1) from a where object_id>=10 and object_id<20)
    select (select * from b) bc,
          (select * from c) cc
    from dual;
執行計畫
----------------------------------------------------------
Plan hash value: 2659770981
----------------------------------------------------------------------------------------------
| Id  | Operation                  | Name            | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |                |    1 |      |  198  (1)| 00:00:03 |
|  1 |  VIEW                      |                |    1 |    13 |    6  (0)| 00:00:01 |
|  2 |  SORT AGGREGATE          |                |    1 |    13 |            |      |
|*  3 |    VIEW                    |                |  1600 | 20800 |    6  (0)| 00:00:01 |
|  4 |    TABLE ACCESS FULL      | SYS_TEMP_0FD9D66|  1600 |  154K|    6  (0)| 00:00:01 |
|  5 |  VIEW                      |                |    1 |    13 |    6  (0)| 00:00:01 |
|  6 |  SORT AGGREGATE          |                |    1 |    13 |            |      |
|*  7 |    VIEW                    |                |  1600 | 20800 |    6  (0)| 00:00:01 |
|  8 |    TABLE ACCESS FULL      | SYS_TEMP_0FD9D66|  1600 |  154K|    6  (0)| 00:00:01 |
|  9 |  TEMP TABLE TRANSFORMATION |                |      |      |            |      |
|  10 |  LOAD AS SELECT          | SYS_TEMP_0FD9D66|      |      |            |      |
|* 11 |    TABLE ACCESS FULL      | TEST            |  1600 |  154K|  196  (1)| 00:00:03 |
|  12 |  FAST DUAL                |                |    1 |      |    2  (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  3 - filter("OBJECT_ID"<10)
  7 - filter("OBJECT_ID">=10 AND "OBJECT_ID"<20)
  11 - filter("OBJECT_TYPE"='TABLE')
統計資訊
----------------------------------------------------------
        394  recursive calls
        22  db block gets
      589  consistent gets
        15  physical reads
        600  redo size
        381  bytes sent via SQL*Net to client
        337  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

--SQL2，最佳化後
SQL> with b as (select count(1) from test where object_id<10),
    c as (select count(1) from test where object_id>=10 and object_id<20)
    select (select * from b) bc,
          (select * from c) cc
    from dual;
執行計畫
----------------------------------------------------------
Plan hash value: 1155001961
--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |                |    1 |      |    2  (0)| 00:00:01 |
|  1 |  VIEW              |                |    1 |    13 |    2  (0)| 00:00:01 |
|  2 |  SORT AGGREGATE  |                |    1 |    5 |            |          |
|*  3 |    INDEX RANGE SCAN| IND_T_OBJECT_ID |    6 |    30 |    2  (0)| 00:00:01 |
|  4 |  VIEW              |                |    1 |    13 |    2  (0)| 00:00:01 |
|  5 |  SORT AGGREGATE  |                |    1 |    5 |            |          |
|*  6 |    INDEX RANGE SCAN| IND_T_OBJECT_ID |    9 |    45 |    2  (0)| 00:00:01 |
|  7 |  FAST DUAL        |                |    1 |      |    2  (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  3 - access("OBJECT_ID"<10)
  6 - access("OBJECT_ID">=10 AND "OBJECT_ID"<20)
統計資訊
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        4  consistent gets
          0  physical reads
          0  redo size
        381  bytes sent via SQL*Net to client
        337  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Oracle 單一實例 從32位 遷移到 64位 方法 

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

Oracle匯入匯出expdp IMPDP詳解

Oracle 10g expdp匯出報錯ORA-4031的解決方案