如何快速递归

chengccy2010 · 发表于 2023-9-13 13:36

本帖最后由 chengccy2010 于 2023-9-13 14:02 编辑

现在有一个表：节点组，节点，节点层级，父节点，节点名称现在需要增加一个全路径名称，但是这个表非常大，有几百万。递归一次非常久，而且这个字段高频使用，做成函数效率也不高。想问问大佬们有什么解决方案没有。
demo：

create table t_test (group_id varchar2(100), node_id varchar2(100), p_node_id varchar2(100), node_level number,node_name varchar2(100));
INSERT INTO T_TEST
(GROUP_ID, NODE_ID, P_NODE_ID, NODE_LEVEL, NODE_NAME)
SELECT 'A', LPAD(LEVEL, 4, '0') AS NODE_ID, 'root' AS P_NODE_ID,
1 AS NODE_LEVEL, LEVEL NODE_NAME
FROM DUAL
CONNECT BY LEVEL <= 10;
INSERT INTO T_TEST
(GROUP_ID, NODE_ID, P_NODE_ID, NODE_LEVEL, NODE_NAME)
SELECT A.GROUP_ID, A.NODE_ID || B.NODE_ID, A.NODE_ID, 2 AS NODE_LEVEL,
B.NODE_NAME
FROM T_TEST A,
(SELECT LPAD(LEVEL, 4, '0') AS NODE_ID, LEVEL NODE_NAME
FROM DUAL
CONNECT BY LEVEL <= 100) B
WHERE A.NODE_LEVEL = 1;
INSERT INTO T_TEST
(GROUP_ID, NODE_ID, P_NODE_ID, NODE_LEVEL, NODE_NAME)
SELECT A.GROUP_ID, A.NODE_ID || B.NODE_ID, A.NODE_ID, 3 AS NODE_LEVEL,
B.NODE_NAME
FROM T_TEST A,
(SELECT LPAD(LEVEL, 4, '0') AS NODE_ID, LEVEL NODE_NAME
FROM DUAL
CONNECT BY LEVEL <= 1000) B
WHERE A.NODE_LEVEL = 2;
INSERT INTO T_TEST
(GROUP_ID, NODE_ID, P_NODE_ID, NODE_LEVEL, NODE_NAME)
SELECT A.GROUP_ID, B.NODE_ID, B.NODE_ID, B.NODE_LEVEL, B.NODE_NAME
FROM (SELECT CHR(65 + LEVEL) AS GROUP_ID FROM DUAL CONNECT BY LEVEL <= 10) A,
T_TEST B;
COMMIT;
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

〇〇 · 发表于 2023-9-13 21:01

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

newkid · 发表于 2023-9-14 01:11

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

newkid · 发表于 2023-9-14 02:48

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

chengccy2010 · 发表于 2023-9-14 15:55

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

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

newkid · 发表于 2023-9-14 22:38

本帖最后由 newkid 于 2023-9-15 05:46 编辑

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

jihuyao · 发表于 2024-3-8 12:01

〇〇发表于 2023-9-13 21:01
把子查询存入物理表，建索引

Or simply create global index partitions and use it and then drop it after all. If necessary drop all indexes and create them again after merge.

newkid · 发表于 2024-3-8 22:37

jihuyao 发表于 2024-3-8 12:01
Or simply create global index partitions and use it and then drop it after all. If necessary drop a ...

分区索引帮不上吧？你说的思路是在大量删改数据的时候去掉索引事后重建，但是如果是增量更新，在MERGE的时候带个WHERE条件就没有多少维护索引的开销了。

jihuyao · 发表于 2024-3-9 08:17

Just a cheap comment without analyzing the sql statement. The interesting part is the downhill performance at certain point. My first reaction is some sorting operation happened (similar to the case in another post with materialized view being applied). If it is true find out which part of sql cause the sorting. Wether or not index is needed is a question mark. Partition is simply a tool for large set of data operation.

[SQL] 如何快速递归