Oracle 9i 数据库WITH查询语法小议 

SQL> WITH

2 SEG AS (SELECT SEGMENT_NAME, SUM(BYTES)/1024 K FROM USER_SEGMENTS GROUP BY SEGMENT_NAME),

3 OBJ AS (SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS)

4 SELECT O.OBJECT_NAME, OBJECT_TYPE, NVL(S.K, 0) SIZE_K

5 FROM OBJ O, SEG S

6 WHERE O.OBJECT_NAME = S.SEGMENT_NAME (+)

7 ;

OBJECT_NAME OBJECT_TYPE SIZE_K

DAIJC_TEST TABLE 128

P_TEST PROCEDURE 0

IND_DAIJC_TEST_C1 INDEX 128

SQL> WITH

2 Q1 AS (SELECT 3 + 5 S FROM DUAL),

3 Q2 AS (SELECT 3 * 5 M FROM DUAL),

4 Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)

5 SELECT * FROM Q3;

S M S+M S*M

8 15 23 120

SQL> CREATE TABLE T_WITH AS SELECT ROWNUM ID, A.* FROM DBA_SOURCE A WHERE ROWNUM < 100001; 

表已创建。 

SQL> SET TIMING ON 

SQL> SET AUTOT ON 

SQL> SELECT ID, NAME FROM T_WITH

2 WHERE ID IN 

3 (

4 SELECT MAX(ID) FROM T_WITH 

5 UNION ALL

6 SELECT MIN(ID) FROM T_WITH

7 UNION ALL

8 SELECT TRUNC(AVG(ID)) FROM T_WITH

9 );



ID NAME

1 STANDARD

50000 DBMS_BACKUP_RESTORE

100000 INITJVMAUX



已用时间: 00: 00: 00.09

执行计划

Plan hash value: 647530712



-----------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |

-----------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 129 |

|* 1 | HASH JOIN | | 3 | 129 |

| 2 | VIEW | VW_NSO_1 | 3 | 39 |

| 3 | HASH UNIQUE | | 3 | 39 |

| 4 | UNION-ALL | | | |

| 5 | SORT AGGREGATE | | 1 | 13 |

| 6 | TABLE ACCESS FULL| T_WITH | 112K| 1429K|

| 7 | SORT AGGREGATE | | 1 | 13 |

| 8 | TABLE ACCESS FULL| T_WITH | 112K| 1429K|

| 9 | SORT AGGREGATE | | 1 | 13 |

| 10 | TABLE ACCESS FULL| T_WITH | 112K| 1429K|

| 11 | TABLE ACCESS FULL | T_WITH | 112K| 3299K|

-----------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



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



Note

-----

- dynamic sampling used for this statement



统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

5529 consistent gets

0 physical reads

0 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

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

SQL> SET AUTOT OFF

SQL> SET TIMING OFF

SQL> WITH SUBQ AS (SELECT 1 FROM DUAL)

2 SELECT ID, NAME FROM T_WITH WHERE ID IN (SELECT * FROM SUBQ);

ID NAME

---------- ------------------------------

1 STANDARD

SQL> WITH SUBQ AS (SELECT 1 FROM DUAL)

2 UPDATE T_WITH SET ID = 1 WHERE ID IN (SELECT * FROM SUBQ);

UPDATE T_WITH SET ID = 1 WHERE ID IN (SELECT * FROM SUBQ)

*第 2 行出现错误:

ORA-00928: 缺失 SELECT 关键字

SQL> WITH SUBQ AS (SELECT 1 FROM DUAL)

2 DELETE T_WITH WHERE ID IN (SELECT * FROM SUBQ);

DELETE T_WITH WHERE ID IN (SELECT * FROM SUBQ)

*第 2 行出现错误:

ORA-00928: 缺失 SELECT 关键字