您的位置:首页技术开发数据库教程 → Oracle新增递归WITH语句

Oracle新增递归WITH语句

时间:2010/1/21 10:53:00来源:本站整理作者:我要评论(1)

 

Oracle在11.2中增强了WITH语句,使得一些树型查询不再需要CONNECT BY语句就可以完成。
看一个简单的例子:
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database11gEnterprise Edition Release11.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)
可以看到,虽然实现了相同的功能,但是两种方法的执行计划相去甚远,置于哪种方式效率更高,可能需要具体的测试才能确定

相关视频

    没有数据

相关阅读 数据库流行度排行2019年9月 数据库排行榜2019年最新版fifa online4数据库大全 fifa online4球员数据库在哪myeclipse怎么连接到数据库 myeclipse连接到数据库方法PLSQL Developer怎么导出数据库PLSQL Developer配置Oralce11g连接plsql developer怎么使用 plsql developer使用教程plsql developer怎么连接数据库 plsql developer连接数据库教程mysql数据库root密码忘记的修改方法

文章评论
发表评论

热门文章 oracle10g安装图解(wi

最新文章 数据库流行度排行2019oracle10g安装图解(wi SQL2008全部数据导出导入两种方法SQL2005新建复制“找不到存储过程 错误:28Dos远程登录mysql数据库详细图文教程mysql怎么开启远程登录功能

人气排行 mysql自动定时备份数据库的最佳方法-支持wiVisual Foxpro 6.0安装向导图文教程SQL Server 2008 安装图文教程SQL2008全部数据导出导入两种方法SQL 2000/2005/2008 的收缩日志方法,和清理mysql出 Can't connect to MySQL server onoracle10g安装图解(win7)sql2005安装图解_(sql server2005)安装教程