查看: 304|回复: 5

[SQL] 如何快速递归

[复制链接]
论坛徽章:
18
优秀写手
日期:2014-02-27 06:00:13秀才
日期:2017-12-12 09:52:44秀才
日期:2017-08-11 15:37:32弗兰奇
日期:2017-07-04 09:16:01秀才
日期:2017-06-29 10:16:48乌索普
日期:2017-05-26 08:58:24娜美
日期:2017-05-18 16:07:23ITPUB15周年纪念
日期:2017-05-02 15:22:36妮可·罗宾
日期:2017-04-06 10:06:19处女座
日期:2016-03-10 09:03:26
发表于 2023-9-13 13:36 | 显示全部楼层 |阅读模式
本帖最后由 chengccy2010 于 2023-9-13 14:02 编辑

现在有一个表: 节点组,节点,节点层级,父节点, 节点名称现在需要增加一个全路径名称,但是这个表非常大,有几百万。 递归一次非常久,而且这个字段高频使用,做成函数效率也不高。想问问大佬们有什么解决方案没有。
demo:
  1. create table t_test (group_id varchar2(100), node_id  varchar2(100), p_node_id  varchar2(100), node_level number,node_name  varchar2(100));

  2. INSERT INTO T_TEST
  3.   (GROUP_ID, NODE_ID, P_NODE_ID, NODE_LEVEL, NODE_NAME)
  4.   SELECT 'A', LPAD(LEVEL, 4, '0') AS NODE_ID, 'root' AS P_NODE_ID,
  5.          1 AS NODE_LEVEL, LEVEL NODE_NAME
  6.   FROM   DUAL
  7.   CONNECT BY LEVEL <= 10;

  8. INSERT INTO T_TEST
  9.   (GROUP_ID, NODE_ID, P_NODE_ID, NODE_LEVEL, NODE_NAME)
  10.   SELECT A.GROUP_ID, A.NODE_ID || B.NODE_ID, A.NODE_ID, 2 AS NODE_LEVEL,
  11.          B.NODE_NAME
  12.   FROM   T_TEST A,
  13.          (SELECT LPAD(LEVEL, 4, '0') AS NODE_ID, LEVEL NODE_NAME
  14.            FROM   DUAL
  15.            CONNECT BY LEVEL <= 100) B
  16.   WHERE  A.NODE_LEVEL = 1;


  17. INSERT INTO T_TEST
  18.   (GROUP_ID, NODE_ID, P_NODE_ID, NODE_LEVEL, NODE_NAME)
  19.   SELECT A.GROUP_ID, A.NODE_ID || B.NODE_ID, A.NODE_ID, 3 AS NODE_LEVEL,
  20.          B.NODE_NAME
  21.   FROM   T_TEST A,
  22.          (SELECT LPAD(LEVEL, 4, '0') AS NODE_ID, LEVEL NODE_NAME
  23.            FROM   DUAL
  24.            CONNECT BY LEVEL <= 1000) B
  25.   WHERE  A.NODE_LEVEL = 2;



  26. INSERT INTO T_TEST
  27.   (GROUP_ID, NODE_ID, P_NODE_ID, NODE_LEVEL, NODE_NAME)
  28.   SELECT A.GROUP_ID, B.NODE_ID, B.NODE_ID, B.NODE_LEVEL, B.NODE_NAME
  29.   FROM   (SELECT CHR(65 + LEVEL) AS GROUP_ID FROM DUAL CONNECT BY LEVEL <= 10) A,
  30.          T_TEST B;
  31. COMMIT;

  32. alter table T_TEST add path VARCHAR2(1000);
复制代码


现在的SQL: 如果记录数在百万以下效率还可以, 而后直线下降。
MERGE INTO T_TEST A
USING (SELECT T.ROWID AS RD, SYS_CONNECT_BY_PATH(NODE_NAME, '/') AS PATH
       FROM   T_TEST T
       START  WITH T.P_NODE_ID = 'root'
       CONNECT BY PRIOR NODE_ID = P_NODE_ID
           AND    PRIOR GROUP_ID = GROUP_ID) B
ON (A.ROWID = B.RD)
WHEN MATCHED THEN
  UPDATE SET A.PATH = B.PATH



论坛徽章:
407
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2023-9-13 21:01 来自手机 | 显示全部楼层
把子查询存入物理表,建索引

使用道具 举报

回复
论坛徽章:
519
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2023-9-14 01:11 | 显示全部楼层
你这个MERGE只是一次性执行吧?以后就只有数据发生变化才需要修改,而且修改的只是局部,除非你动了很高层的结构,比如在根节点上面又加一个父节点之类的。

使用道具 举报

回复
论坛徽章:
519
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2023-9-14 02:48 | 显示全部楼层
另外,如果做成函数的话,效率可能是足够高的,因为是往根部走,只有一条线。但是不能直接用SYS_CONNECT_BY_PATH,因为顺序是反的。

使用道具 举报

回复
论坛徽章:
18
优秀写手
日期:2014-02-27 06:00:13秀才
日期:2017-12-12 09:52:44秀才
日期:2017-08-11 15:37:32弗兰奇
日期:2017-07-04 09:16:01秀才
日期:2017-06-29 10:16:48乌索普
日期:2017-05-26 08:58:24娜美
日期:2017-05-18 16:07:23ITPUB15周年纪念
日期:2017-05-02 15:22:36妮可·罗宾
日期:2017-04-06 10:06:19处女座
日期:2016-03-10 09:03:26
 楼主| 发表于 2023-9-14 15:55 | 显示全部楼层
newkid 发表于 2023-9-14 02:48
另外,如果做成函数的话,效率可能是足够高的,因为是往根部走,只有一条线。但是不能直接用SYS_CONNECT_BY ...

emmm,现在不确定会不会动上层的名称,因为这个数据是来源上游。 我们现在都不好识别上游更新了哪些,每次都是全删全写。
函数,因为确认了最高层级是6级,之前写了一个函数是自连接6次,并且建了索引,但是如果调用这个函数的记录数达到万级别,也会很慢

使用道具 举报

回复
论坛徽章:
519
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2023-9-14 22:38 | 显示全部楼层
本帖最后由 newkid 于 2023-9-15 05:46 编辑

上游给你们的数据每次都是全表更新?不是增量更新?如果是这样,那你只好每次都重新算一遍。
你可以把表拆开,把主键和这个PATH单独存放在另外一张表,这样你只需要CREATE TABLE AS SELECT, 不需要MERGE, 可以节省点时间。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 未成年人举报专区 
京ICP备16024965号-8  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表