当前位置:Gxlcms > 数据库问题 > [转载]oracle树形查询 start with connect by

[转载]oracle树形查询 start with connect by

时间:2021-07-01 10:21:17 帮助过:22人阅读

  1. 1 -- 表结构
  2. 2 create table menu(
  3. 3 id varchar2(64) not null,
  4. 4 parent_id varchar2(64) not null,
  5. 5 name varchar2(100) not null,
  6. 6 depth number(2) not null,
  7. 7 primary key (id)
  8. 8 )
  9. 9
  10. 10 -- 初始化数据
  11. 11 -- 顶级菜单
  12. 12 insert into menu values (‘100000‘, ‘0‘, ‘顶级菜单1‘, 1);
  13. 13 insert into menu values (‘200000‘, ‘0‘, ‘顶级菜单2‘, 1);
  14. 14 insert into menu values (‘300000‘, ‘0‘, ‘顶级菜单3‘, 1);
  15. 15
  16. 16 -- 父级菜单
  17. 17 -- 顶级菜单1 直接子菜单
  18. 18 insert into menu values (‘110000‘, ‘100000‘, ‘菜单11‘, 2);
  19. 19 insert into menu values (‘120000‘, ‘100000‘, ‘菜单12‘, 2);
  20. 20 insert into menu values (‘130000‘, ‘100000‘, ‘菜单13‘, 2);
  21. 21 insert into menu values (‘140000‘, ‘100000‘, ‘菜单14‘, 2);
  22. 22 -- 顶级菜单2 直接子菜单
  23. 23 insert into menu values (‘210000‘, ‘200000‘, ‘菜单21‘, 2);
  24. 24 insert into menu values (‘220000‘, ‘200000‘, ‘菜单22‘, 2);
  25. 25 insert into menu values (‘230000‘, ‘200000‘, ‘菜单23‘, 2);
  26. 26 -- 顶级菜单3 直接子菜单
  27. 27 insert into menu values (‘310000‘, ‘300000‘, ‘菜单31‘, 2);
  28. 28
  29. 29 -- 菜单13 直接子菜单
  30. 30 insert into menu values (‘131000‘, ‘130000‘, ‘菜单131‘, 3);
  31. 31 insert into menu values (‘132000‘, ‘130000‘, ‘菜单132‘, 3);
  32. 32 insert into menu values (‘133000‘, ‘130000‘, ‘菜单133‘, 3);
  33. 33
  34. 34 -- 菜单132 直接子菜单
  35. 35 insert into menu values (‘132100‘, ‘132000‘, ‘菜单1321‘, 4);
  36. 36 insert into menu values (‘132200‘, ‘132000‘, ‘菜单1332‘, 4);
  37. 37
技术分享图片

  生成的菜单层次结构如下:
顶级菜单1
          菜单11
          菜单12
          菜单13
                    菜单131
                    菜单132
                              菜单1321
                              菜单1322
                    菜单133
          菜单14
顶级菜单2
          菜单21
          菜单22
          菜单23
顶级菜单3
          菜单31

  2、SQL查询

  1. --prior放的左右位置决定了检索是自底向上还是自顶向下. 左边是自上而下(找子节点),右边是自下而上(找父节点)
  2. --找父节点
  3. select * from menu start with id=‘130000‘ connect by id = prior parent_id;

  技术分享图片

  1. --找子节点节点
  2. -- (子节点)id为130000的菜单,以及130000菜单下的所有直接或间接子菜单(prior 在左边, prior、parent_id(等号右边)在右边)
  3. select * from menu start with id=‘130000‘ connect by prior id = parent_id ;

  技术分享图片

  1. -- (父节点)id为1321的菜单,以及1321菜单下的所有直接或间接父菜单(prior、parent_id(等号左边) 都在左边)
  2. select * from menu start with id=‘132100‘ connect by prior parent_id = id;
  3. -- prior 后面跟的是(parent_id) 则是查找父节点,prior后面跟的是(id)则是查找子节点

  技术分享图片

  1. --根据菜单组分类统计每个菜单包含子菜单的个数
  2. select id, max(name) name, count(1) from menu
  3. group by id
  4. connect by prior parent_id = id
  5. order by id

  技术分享图片

  1. -- 查询所有的叶子节点
  2. select t2.* from menu t2 where id not in(select t.parent_id from menu t) order by id;

  技术分享图片

三、性能问题  

   对于 start with connect by语句的执行,oracle会进行递归查询,当数据量大的时候会产生性能相关问题。

  1. --生成执行计划
  2. explain plan for select * from menu start with id=‘132100‘ connect by prior parent_id = id;
  3. -- 查询执行计划
  4. select * from table( dbms_xplan.display);

  语句执行计划结果如下:

技术分享图片
  1. Plan hash value: 3563250490
  2. ----------------------------------------------------------------------------------------------
  3. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  4. ----------------------------------------------------------------------------------------------
  5. | 0 | SELECT STATEMENT | | 1 | 133 | 1 (0)| 00:00:01 |
  6. |* 1 | CONNECT BY WITH FILTERING | | | | | |
  7. | 2 | TABLE ACCESS BY INDEX ROWID | MENU | 1 | 133 | 1 (0)| 00:00:01 |
  8. |* 3 | INDEX UNIQUE SCAN | SYS_C0018586 | 1 | | 1 (0)| 00:00:01 |
  9. | 4 | NESTED LOOPS | | | | | |
  10. | 5 | CONNECT BY PUMP | | | | | |
  11. | 6 | TABLE ACCESS BY INDEX ROWID| MENU | 1 | 133 | 1 (0)| 00:00:01 |
  12. |* 7 | INDEX UNIQUE SCAN | SYS_C0018586 | 1 | | 1 (0)| 00:00:01 |
  13. ----------------------------------------------------------------------------------------------
  14. Predicate Information (identified by operation id):
  15. ---------------------------------------------------
  16. 1 - access("ID"=PRIOR "PARENT_ID")
  17. 3 - access("ID"=‘132100‘)
  18. 7 - access("ID"=PRIOR "PARENT_ID")
  19. Note
  20. -----
  21. - dynamic sampling used for this statement

[转载]oracle树形查询 start with connect by

标签:复制   for   order   isp   生成   with   间接   use   menu   

人气教程排行