SQL> WITH2 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_K5 FROM OBJ O, SEG S6 WHERE O.OBJECT_NAME = S.SEGMENT_NAME (+)7 ;OBJECT_NAME OBJECT_TYPE SIZE_KDAIJC_TEST TABLE 128P_TEST PROCEDURE 0IND_DAIJC_TEST_C1 INDEX 128
SQL> WITH2 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*M8 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_WITH2 WHERE ID IN 3 (4 SELECT MAX(ID) FROM T_WITH 5 UNION ALL6 SELECT MIN(ID) FROM T_WITH7 UNION ALL8 SELECT TRUNC(AVG(ID)) FROM T_WITH9 );ID NAME1 STANDARD50000 DBMS_BACKUP_RESTORE100000 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 calls0 db block gets5529 consistent gets0 physical reads0 redo size543 bytes sent via SQL*Net to client385 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)3 rows processed
SQL> SET AUTOT OFFSQL> SET TIMING OFFSQL> WITH SUBQ AS (SELECT 1 FROM DUAL)2 SELECT ID, NAME FROM T_WITH WHERE ID IN (SELECT * FROM SUBQ);ID NAME---------- ------------------------------1 STANDARDSQL> 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 要害字
