如何快速递归

chengccy2010

现在有一个表： 节点组，节点，节点层级，父节点， 节点名称现在需要增加一个全路径名称，但是这个表非常大，有几百万。 递归一次非常久，而且这个字段高频使用，做成函数效率也不高。想问问大佬们有什么解决方案没有。
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. 
   
3. INSERT INTO T_TEST
   
4.   (GROUP_ID, NODE_ID, P_NODE_ID, NODE_LEVEL, NODE_NAME)
   
5.   SELECT 'A', LPAD(LEVEL, 4, '0') AS NODE_ID, 'root' AS P_NODE_ID,
   
6.          1 AS NODE_LEVEL, LEVEL NODE_NAME
   
7.   FROM   DUAL
   
8.   CONNECT BY LEVEL <= 10;
   
9. 
   
10. INSERT INTO T_TEST
    
11.   (GROUP_ID, NODE_ID, P_NODE_ID, NODE_LEVEL, NODE_NAME)
    
12.   SELECT A.GROUP_ID, A.NODE_ID || B.NODE_ID, A.NODE_ID, 2 AS NODE_LEVEL,
    
13.          B.NODE_NAME
    
14.   FROM   T_TEST A,
    
15.          (SELECT LPAD(LEVEL, 4, '0') AS NODE_ID, LEVEL NODE_NAME
    
16.            FROM   DUAL
    
17.            CONNECT BY LEVEL <= 100) B
    
18.   WHERE  A.NODE_LEVEL = 1;
    
19. 
    
20. 
    
21. INSERT INTO T_TEST
    
22.   (GROUP_ID, NODE_ID, P_NODE_ID, NODE_LEVEL, NODE_NAME)
    
23.   SELECT A.GROUP_ID, A.NODE_ID || B.NODE_ID, A.NODE_ID, 3 AS NODE_LEVEL,
    
24.          B.NODE_NAME
    
25.   FROM   T_TEST A,
    
26.          (SELECT LPAD(LEVEL, 4, '0') AS NODE_ID, LEVEL NODE_NAME
    
27.            FROM   DUAL
    
28.            CONNECT BY LEVEL <= 1000) B
    
29.   WHERE  A.NODE_LEVEL = 2;
    
30. 
    
31. 
    
32. 
    
33. INSERT INTO T_TEST
    
34.   (GROUP_ID, NODE_ID, P_NODE_ID, NODE_LEVEL, NODE_NAME)
    
35.   SELECT A.GROUP_ID, B.NODE_ID, B.NODE_ID, B.NODE_LEVEL, B.NODE_NAME
    
36.   FROM   (SELECT CHR(65 + LEVEL) AS GROUP_ID FROM DUAL CONNECT BY LEVEL <= 10) A,
    
37.          T_TEST B;
    
38. COMMIT;
    
39. 
    
40. 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

〇〇

把子查询存入物理表，建索引

newkid

你这个MERGE只是一次性执行吧？以后就只有数据发生变化才需要修改，而且修改的只是局部，除非你动了很高层的结构，比如在根节点上面又加一个父节点之类的。

newkid

另外，如果做成函数的话，效率可能是足够高的，因为是往根部走，只有一条线。但是不能直接用SYS_CONNECT_BY_PATH，因为顺序是反的。

chengccy2010

newkid 发表于 2023-9-14 02:48
另外，如果做成函数的话，效率可能是足够高的，因为是往根部走，只有一条线。但是不能直接用SYS_CONNECT_BY ...

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

newkid

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