sql - 加速拆分查询

Question

我有一个像下面这样的字符串:

       R#4039,4040,3508,3512,1006,4506,4514,4011,4035,4513,4518,2009,4012,1998,4037;FF#3007,2018,1005,4515,4020,4027,4029,1503,4516,1999,2003,4026,2002,4007,2011,1004,3006,4519

我想拆分我的字符串并添加一个随机 ID，我想要的输出是这样的:

name    id
4515    1234562
2002    1234562
1998    1234561
4514    1234561
3007    1234562
1005    1234562
4516    1234562
1999    1234562
4007    1234562
3006    1234562
4519    1234562
4012    1234561
4011    1234561
4506    1234561
3512    1234561
4039    1234561
2018    1234562
1004    1234562
4037    1234561
4513    1234561
1006    1234561
4518    1234561
4020    1234562
1503    1234562
2003    1234562
4026    1234562
2009    1234561
3508    1234561
4027    1234562
4029    1234562
2011    1234562
4035    1234561
4040    1234561

我使用以下查询，但响应需要很长时间(7 秒)。我想编写可以提供相同结果但速度非常快的查询。
我的查询是:

SELECT   distinct regexp_substr(TYPES,'[^,]+', 1,LEVEL),id FROM (


    select   REPLACE(regexp_substr(data ,'.+#', 1),'#','') AS NAME,REPLACE(regexp_substr(data ,'#.+', 1),'#','')AS TYPES , '123456'||id as id from (SELECT  LEVEL AS id, REGEXP_SUBSTR(REF_LEDGER_PROFIEL, '[^;]+', 1, LEVEL) AS data
   FROM (select 'R#4039,4040,3508,3512,1006,4506,4514,4011,4035,4513,4518,2009,4012,1998,4037;FF#3007,2018,1005,4515,4020,4027,4029,1503,4516,1999,2003,4026,2002,4007,2011,1004,3006,4519' as REF_LEDGER_PROFIEL from dual)
CONNECT BY REGEXP_SUBSTR(REF_LEDGER_PROFIEL, '[^;]+', 1, LEVEL) IS NOT NULL)


) 
       CONNECT BY  REGEXP_SUBSTR(TYPES, '[^,]+', 1, LEVEL) is not null

       ;

Answer 1

问题在于 DISTINCT
由于每个组的 Name 都是唯一的，因此请添加常见的 prior + sys_guid 条件并删除 DISTINCT

SELECT regexp_substr(types, '[^,]+', 1, LEVEL)
    ,id
FROM (
    SELECT replace(regexp_substr(data, '.+#', 1), '#', '') AS name
        ,replace(regexp_substr(data, '#.+', 1), '#', '') AS types
        ,'123456' || id AS id
    FROM (
        SELECT LEVEL AS id
            ,regexp_substr(ref_ledger_profiel, '[^;]+', 1, LEVEL) AS data
        FROM (
            SELECT 'R#4039,4040,3508,3512,1006,4506,4514,4011,4035,4513,4518,2009,4012,1998,4037;FF#3007,2018,1005,4515,4020,4027,4029,1503,4516,1999,2003,4026,2002,4007,2011,1004,3006,4519' AS ref_ledger_profiel
            FROM dual
            ) CONNECT BY regexp_substr(ref_ledger_profiel, '[^;]+', 1, LEVEL) IS NOT NULL
        )
    ) CONNECT BY PRIOR name = name -- add This and 
    AND PRIOR SYS_GUID() IS NOT NULL --This
    AND regexp_substr(types, '[^,]+', 1, LEVEL) IS NOT NULL;

阅读 this thread 以了解有关其工作原理的更多信息。