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的解决方法

相关内容