Oracle 使用with要小心了--谓词不能推进

今天看到一条SQL大量使用了with，开发人员为了逻辑清晰，把一些结果集先用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    c as (select count(1) from a where object_id>=10 and object_id    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"  7 - filter("OBJECT_ID">=10 AND "OBJECT_ID"  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