SQL> WITH
2 AGG AS (SELECT MAX(ID) MAX, MIN(ID) MIN, TRUNC(AVG(ID)) AVG FROM T_WITH)
3 SELECT ID, NAME FROM T_WITH 
4 WHERE ID IN 
5 (
6 SELECT MAX FROM AGG 
7 UNION ALL 
8 SELECT MIN FROM AGG 
9 UNION ALL 
10 SELECT AVG FROM AGG
11 );

ID NAME
---------- ------------------------------
1 STANDARD
50000 DBMS_BACKUP_RESTORE
100000 INITJVMAUX

已用时间: 00: 00: 00.07

执行计划
----------------------------------------------------------
Plan hash value: 1033356310

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 |
| 1 | TEMP TABLE TRANSFORMATION | | | |
| 2 | LOAD AS SELECT | T_WITH | | |
| 3 | SORT AGGREGATE | | 1 | 13 |
| 4 | TABLE ACCESS FULL | T_WITH | 112K| 1429K|
|* 5 | HASH JOIN | | 3 | 129 |
| 6 | VIEW | VW_NSO_1 | 3 | 39 |
| 7 | HASH UNIQUE | | 3 | 39 |
| 8 | UNION-ALL | | | |
| 9 | VIEW | | 1 | 13 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662E_BF2EDF12 | 1 | 13 |
| 11 | VIEW | | 1 | 13 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662E_BF2EDF12 | 1 | 13 |
| 13 | VIEW | | 1 | 13 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662E_BF2EDF12 | 1 | 13 |
| 15 | TABLE ACCESS FULL | T_WITH | 112K| 3299K|
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("ID"="$nso_col_1")

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
2 recursive calls
8 db block gets
2776 consistent gets
1 physical reads
648 redo size
543 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed