有没有大佬帮忙优化一下oracle语句

灰烬鬼舞

select id,name,time,

replace(regexp_substr(FJ,'[^,]+',1,level),',','')FJ

from table1

where name="测试中"

connect by level <=(length(FJ)-length(replace(FJ,',',''))+1);

语句目的是为了将FJ字段中字符串拆分多行，依据逗号拆分，组合其他字段多行展示；

目前数据库中只有6000+的数据量，执行此语句时数据库卡死；自己在虚拟机已经验证，6行数据时拆分正常的；

百度搜了下，号称regexp_substr执行性能低，有没有数据库大佬帮忙看看怎么优化，后续数据量肯定继续增长的；

bobo0121

[SQL] 纯文本查看 复制代码

SELECT t1.id,
       t1.name,
       t1.time,
       t2.fj_value AS FJ
FROM table1 t1
CROSS JOIN LATERAL
  (SELECT REGEXP_SUBSTR(t1.FJ, '[^,]+', 1, LEVEL) AS fj_value
   FROM dual
   CONNECT BY REGEXP_SUBSTR(t1.FJ, '[^,]+', 1, LEVEL) IS NOT NULL
  ) t2
WHERE t1.name = '测试中'
AND t1.FJ IS NOT NULL;

避免在WHERE子句中使用replace函数,如果FJ字段中的逗号不多,可以考虑使用PL/SQL过程来手动拆分这些值

阿伟de大长腿

[Asm] 纯文本查看 复制代码

WITH Split_FJ AS (
    SELECT id, name, time, 
           FJ,
           LENGTH(FJ) - LENGTH(REPLACE(FJ, ',', '')) + 1 AS num_splits
    FROM table1
    WHERE name = '测试中'
),
Split_FJ_Index AS (
    SELECT id, name, time, 
           FJ,
           num_splits,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY LENGTH(FJ) - LENGTH(REPLACE(FJ, ',', '')) + 1) AS split_index
    FROM Split_FJ
)
SELECT id, name, time,
       SUBSTRING_INDEX(SUBSTRING_INDEX(FJ, ',', split_index), ',', -1) AS FJ_split
FROM Split_FJ_Index
WHERE split_index <= num_splits;

xiaoboy

[SQL] 纯文本查看 复制代码

WITH RECURSIVE split_data AS (
    SELECT
        id,
        name,
        time,
        FJ,
        SUBSTRING_INDEX(FJ, ',', 1) AS FJ_split,
        SUBSTRING_INDEX(SUBSTRING_INDEX(FJ, ',', 2), ',', -1) AS remaining_FJ,
        1 AS level
    FROM table1
    WHERE name = '测试中'
    
    UNION ALL
    
    SELECT
        id,
        name,
        time,
        FJ,
        SUBSTRING_INDEX(SUBSTRING_INDEX(FJ, ',', level + 1), ',', -1) AS FJ_split,
        SUBSTRING_INDEX(SUBSTRING_INDEX(FJ, ',', level + 2), ',', -1) AS remaining_FJ,
        level + 1 AS level
    FROM split_data
    WHERE remaining_FJ IS NOT NULL
)
SELECT
    id,
    name,
    time,
    FJ_split
FROM split_data
ORDER BY id, level;

melo520

WITH RECURSIVE split_fj (id, name, time, fj, fj_part, level) AS (
  SELECT
    id,
    name,
    time,
    fj,
    SUBSTR(fj, 1, INSTR(fj, ',') - 1),
    1
  FROM
    table1
  WHERE
    name = '测试中' AND fj IS NOT NULL AND INSTR(fj, ',') > 0

  UNION ALL

  SELECT
    id,
    name,
    time,
    fj,
    SUBSTR(fj, INSTR(fj, ',') + 1, INSTR(SUBSTR(fj, INSTR(fj, ',') + 1), ',') - 1),
    level + 1
  FROM
    split_fj
  WHERE
    INSTR(fj, ',') > 0
)
SELECT
  id,
  name,
  time,
  fj_part
FROM
  split_fj;

灰烬鬼舞

不支持SUBSTRING_INDEX



oracle database 11g Enterprise Edition Release 11.2.0.1.0 -64bit Production

灰烬鬼舞

bobo0121 发表于 2024-7-31 15:52
[mw_shl_code=sql,true]SELECT t1.id,
       t1.name,
       t1.time,

CROSS JOIN LATERAL

出错，未能正确执行

灰烬鬼舞

melo520 发表于 2024-7-30 18:33
WITH RECURSIVE split_fj (id, name, time, fj, fj_part, level) AS (
  SELECT
    id,

行1列16出错，缺少关键字

melo520

灰烬鬼舞 发表于 2024-7-31 16:54
行1列16出错，缺少关键字

最好加个表结构和2行测试数据之类的，盲写有点容易错

灰烬鬼舞

melo520 发表于 2024-7-31 16:59
最好加个表结构和2行测试数据之类的，盲写有点容易错

ID        name                 FJ
1        转子图纸规格书        342221,342222
2        阀门图纸规格书        350011
3        装配图纸规格书        362211,362212,362213,362214,362215


数据库结构非常简单，现在是数据迁移时候，一个数据下多个附件情况新数据库格式变了，一个附件一行，

需要将附件一栏根据逗号拆开，例如ID=1的，拆分结果是变成2行，