运with子句子优化代码中再查询

/*
利益: 1. 属性再好,一客复制(类似SYS_TMP…),多卖使用。
       2. 结构清晰,预先定义。
       3. 代码修改不必修改多地处。
       
请求小心观察语句1和语句2执行计划的区别,尤其是语句2的SYS_TEMP_0FD9D6605_3B91BA4这些奇怪之命名。其实这便代表是
复制在内存中的数额,一不成复制,多次施用。
       
*/

          
drop table t_with;
CREATE TABLE T_WITH AS SELECT ROWNUM ID, A.* FROM DBA_SOURCE A WHERE
ROWNUM < 100001;
SET autotrace traceonly
Set linesize 1000

–语句1
SELECT ID, NAME FROM T_WITH
WHERE ID IN 
(SELECT MAX(ID) FROM T_WITH 
 UNION ALL
 SELECT MIN(ID) FROM T_WITH
 UNION ALL
 SELECT TRUNC(AVG(ID)) FROM T_WITH);

实行计划

Plan hash value: 647530712

| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)|

Time     |

|   0 | SELECT STATEMENT       |          |     3 |   129 |  1382   (1)|
00:00:17 |
|*  1 |  HASH JOIN             |          |     3 |   129 |  1382  
(1)| 00:00:17 |
|   2 |   VIEW                 | VW_NSO_1 |     3 |    39 |  1035  
(1)| 00:00:13 |
|   3 |    HASH UNIQUE         |          |     3 |    39 |  1035  (67)|
00:00:13 |
|   4 |     UNION-ALL          |          |       |       |            |
         |
|   5 |      SORT AGGREGATE    |          |     1 |    13 |            |
         |
|   6 |       TABLE ACCESS FULL| T_WITH   | 91060 |  1156K|   345  
(1)| 00:00:05 |
|   7 |      SORT AGGREGATE    |          |     1 |    13 |            |
         |
|   8 |       TABLE ACCESS FULL| T_WITH   | 91060 |  1156K|   345  
(1)| 00:00:05 |
|   9 |      SORT AGGREGATE    |          |     1 |    13 |            |
         |
|  10 |       TABLE ACCESS FULL| T_WITH   | 91060 |  1156K|   345  
(1)| 00:00:05 |
|  11 |   TABLE ACCESS FULL    | T_WITH   | 91060 |  2667K|   345  

(1)| 00:00:05 |

Predicate Information (identified by operation id):

   1 – access(“ID”=”MAX(ID)”)

Note

   – dynamic sampling used for this statement (level=2)

统计信息

          0  recursive calls
          0  db block gets
       4969  consistent gets
          0  physical reads
          0  redo size
        558  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

–语句2
WITH AGG AS (SELECT MAX(ID) MAX, MIN(ID) MIN, TRUNC(AVG(ID)) AVG FROM
T_WITH)
 SELECT ID, NAME FROM T_WITH 
 WHERE ID IN 
 ( SELECT MAX FROM AGG  UNION ALL  SELECT MIN FROM AGG  UNION ALL
 SELECT AVG FROM AGG);

推行计划

Plan hash value: 3705751949

| Id  | Operation                  | Name                       | Rows

 | Bytes | Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT           |                            |     3
|   129 |   697   (1)| 00:00:09 |
|   1 |  TEMP TABLE TRANSFORMATION |                            |      
|       |            |          |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6605_3B91BA4 |    
  |       |            |          |
|   3 |    SORT AGGREGATE          |                            |     1
|    13 |            |          |
|   4 |     TABLE ACCESS FULL      | T_WITH                     | 91060
|  1156K|   345   (1)| 00:00:05 |
|*  5 |   HASH JOIN                |                            |     3
|   129 |   352   (1)| 00:00:05 |
|   6 |    VIEW                    | VW_NSO_1                   |    
3 |    39 |     6   (0)| 00:00:01 |
|   7 |     HASH UNIQUE            |                            |     3
|    39 |     6  (67)| 00:00:01 |
|   8 |      UNION-ALL             |                            |      
|       |            |          |
|   9 |       VIEW                 |                            |     1
|    13 |     2   (0)| 00:00:01 |
|  10 |        TABLE ACCESS FULL   | SYS_TEMP_0FD9D6605_3B91BA4 |    
1 |    13 |     2   (0)| 00:00:01 |
|  11 |       VIEW                 |                            |     1
|    13 |     2   (0)| 00:00:01 |
|  12 |        TABLE ACCESS FULL   | SYS_TEMP_0FD9D6605_3B91BA4 |    
1 |    13 |     2   (0)| 00:00:01 |
|  13 |       VIEW                 |                            |     1
|    13 |     2   (0)| 00:00:01 |
|  14 |        TABLE ACCESS FULL   | SYS_TEMP_0FD9D6605_3B91BA4 |    
1 |    13 |     2   (0)| 00:00:01 |
|  15 |    TABLE ACCESS FULL       | T_WITH                     | 91060

|  2667K|   345   (1)| 00:00:05 |

Predicate Information (identified by operation id):

   5 – access(“ID”=”MAX”)

Note

   – dynamic sampling used for this statement (level=2)

统计信息

          2  recursive calls
          8  db block gets
       2496  consistent gets
          1  physical reads
        600  redo size
        558  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

相关文章