首页 > 代码库 > Oracle 使用with要小心了--谓词不能推进

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

2024-08-03 10:28:55   224人阅读

    今天看到一条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 使用with要小心了--谓词不能推进


联系
我们