Oracle11gr2新增遞迴WITH語句(一)
在11.2中,WITH語句得到進一步的增強,可以支援遞迴的呼叫。
這一篇簡單介紹WITH語句的遞迴呼叫。
Oracle在11.2中增強了WITH語句,使得一些樹型查詢不再需要CONNECT BY語句就可以完成。
看一個簡單的例子:
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> CREATE TABLE T_TREE (
2 ID NUMBER PRIMARY KEY,
3 FATHER_ID NUMBER,
4 NAME VARCHAR2(30));
表已建立。
SQL> INSERT INTO T_TREE VALUES (1, 0, 'A');
已建立 1 行。
SQL> INSERT INTO T_TREE VALUES (2, 1, 'BC');
已建立 1 行。
SQL> INSERT INTO T_TREE VALUES (3, 1, 'DE');
已建立 1 行。
SQL> INSERT INTO T_TREE VALUES (4, 1, 'FG');
已建立 1 行。
SQL> INSERT INTO T_TREE VALUES (5, 2, 'HIJ');
已建立 1 行。
SQL> INSERT INTO T_TREE VALUES (6, 4, 'KLM');
已建立 1 行。
SQL> INSERT INTO T_TREE VALUES (7, 6, 'NOPQ');
已建立 1 行。
SQL> INSERT INTO T_TREE VALUES (8, 5, 'RSTU');
已建立 1 行。
SQL> COMMIT;
提交完成。
SQL> SELECT * FROM T_TREE;
ID FATHER_ID NAME
---------- ---------- ------------------------------
1 0 A
2 1 BC
3 1 DE
4 1 FG
5 2 HIJ
6 4 KLM
7 6 NOPQ
8 5 RSTU
已選擇8行。
看一個樹型查詢的例子:
SQL> SELECT *
2 FROM T_TREE
3 START WITH ID = 2
4 CONNECT BY PRIOR ID = FATHER_ID;
ID FATHER_ID NAME
---------- ---------- ------------------------------
2 1 BC
5 2 HIJ
8 5 RSTU
利用遞迴的WITH語句,可以實現同樣的功能:
SQL> WITH A (ID, FATHER_ID, NAME) AS
2 (SELECT ID, FATHER_ID, NAME
3 FROM T_TREE
4 WHERE ID = 2
5 UNION ALL
6 SELECT B.ID, B.FATHER_ID, B.NAME
7 FROM A, T_TREE B
8 WHERE A.ID = B.FATHER_ID)
9 SELECT * FROM A;
ID FATHER_ID NAME
---------- ---------- ------------------------------
2 1 BC
5 2 HIJ
8 5 RSTU
這種寫法與樹型查詢相比顯得更加清晰易懂。
檢查二者的執行計劃:
SQL> SET AUTOT ON EXP
SQL> WITH A (ID, FATHER_ID, NAME) AS
2 (SELECT ID, FATHER_ID, NAME
3 FROM T_TREE
4 WHERE ID = 2
5 UNION ALL
6 SELECT B.ID, B.FATHER_ID, B.NAME
7 FROM A, T_TREE B
8 WHERE A.ID = B.FATHER_ID)
9 SELECT * FROM A;
ID FATHER_ID NAME
---------- ---------- ------------------------------
2 1 BC
5 2 HIJ
8 5 RSTU
執行計劃
----------------------------------------------------------
Plan hash value: 374960264
--------------------------------------------------------------------------------------------
|Id|Operation |Name |Rows|Bytes|Cost (%CPU)|Time |
--------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 2| 86| 7 (15)|00:00:01|
| 1| VIEW | | 2| 86| 7 (15)|00:00:01|
| 2| UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | |
| 3| TABLE ACCESS BY INDEX ROWID |T_TREE | 1| 43| 1 (0)|00:00:01|
|*4| INDEX UNIQUE SCAN |SYS_C0011143| 1| | 1 (0)|00:00:01|
|*5| HASH JOIN | | 1| 56| 6 (17)|00:00:01|
| 6| RECURSIVE WITH PUMP | | | | | |
| 7| TABLE ACCESS FULL |T_TREE | 8| 344| 4 (0)|00:00:01|
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ID"=2)
5 - access("A"."ID"="B"."FATHER_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> SELECT *
2 FROM T_TREE
3 START WITH ID = 2
4 CONNECT BY PRIOR ID = FATHER_ID;
ID FATHER_ID NAME
---------- ---------- ------------------------------
2 1 BC
5 2 HIJ
8 5 RSTU
執行計劃
----------------------------------------------------------
Plan hash value: 856284266
-------------------------------------------------------------------------------------------
| Id | Operation |Name |Rows| Bytes |Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8| 344 | 5 (20)| 00:00:01 |
|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 2 | TABLE ACCESS FULL |T_TREE| 8| 344 | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T_TREE"."FATHER_ID"=PRIOR "T_TREE"."ID")
filter("ID"=2)
Note
-----
- dynamic sampling used for this statement (level=2)
可以看到,雖然實現了相同的功能,但是兩種方法的執行計劃相去甚遠,置於哪種方式效率更高,可能需要具體的測試才能確定。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/4227/viewspace-625297/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle11gr2新增遞迴WITH語句(三)Oracle遞迴
- Oracle11gr2新增遞迴WITH語句(二)Oracle遞迴
- Oracle11gr2物化檢視日誌新增PURGE語句Oracle
- Oracle11gr2物化檢視日誌新增COMMIT SCN語句OracleMIT
- Oracle11gr2 審計語句增強(一)Oracle
- Matlab的if語句switch語句for迴圈while迴圈語句練習MatlabWhile
- c語言_遞迴C語言遞迴
- PLSQL Language Referenc-PL/SQL控制語句-迴圈語句-基本迴圈(EXIT語句)SQL
- JavaScript 流程控制語句詳解:if語句、switch語句、while迴圈、for迴圈等JavaScriptWhile
- TypeScript 迴圈語句TypeScript
- JavaScript for 迴圈語句JavaScript
- MySQL迴圈語句MySql
- Oracle迴圈語句Oracle
- PLSQL Language Referenc-PL/SQL控制語句-迴圈語句-FOR迴圈SQL
- PLSQL Language Referenc-PL/SQL控制語句-迴圈語句-WHILE迴圈SQLWhile
- oracle遞迴(一)Oracle遞迴
- JavaScript跳出for迴圈語句JavaScript
- java 迴圈語句(轉)Java
- PLSQL Language Referenc-PL/SQL控制語句-迴圈語句-FOR迴圈-FOR迴圈中的索引SQL索引
- Oracle11gr2新增版本功能(一)Oracle
- Oracle11gr2的PLSQL優化NULL語句OracleSQL優化Null
- Oracle11gr2 審計語句增強(二)Oracle
- Python-條件語句和迴圈語句Python
- MySQL 三種新增語句MySql
- MySQL 四種新增語句MySql
- c語言中,while(1)語句使用break語句跳出迴圈C語言While
- Oracle11gr2新增ALTER DATABASE LINK語法OracleDatabase
- PLSQL Language Referenc-PL/SQL控制語句-迴圈語句-FOR迴圈-下限和上限SQL
- Java基礎 迴圈語句 for while do.....while語句JavaWhile
- Java 可以採用什麼語句跳出迴圈語句Java
- C語言goto語句以及用goto語句構成迴圈C語言Go
- Python的迴圈語句Python
- 流程控制、 迴圈語句
- swift控制流——迴圈語句Swift
- 使用for迴圈操作DML語句
- 6、迴圈結構語句
- linux 迴圈控制語句Linux
- PL/SQL迴圈控制語句SQL