데이터베이스 질문과 답변
안녕하세요 정규식 표현으로 인한 속도 저하로 도움 요청 드립니다.
아래 내용은 하나의 id 값의 0000,0001,0002,0003 과 같이 들어가 있는 data를 row로 변환하는것을 짯는데 데이터들이 들어가니 조회 자체가 안됩니다.
row당 한 30개정도 들어가있고 총 한 60개 id값을 가지고 있는데.. 개선 하다하다가 안되어있네요 인덱스도 없고.. pk id 값 인덱스만 걸려있네요 도움 요청 드립니다.
SELECT 'A' AS ID
,
'0000,0001,0002,0003,0004,0005,0006,0007,0008,0009,0010,0011,0012,0013,0014,0015,0016,0017,0018'
AS C_ID
FROM DUAL
UNION ALL
SELECT 'B' AS ID
,
'0000,0001,0002,0003,0004,0005,0006,0007,0008,0009,0010,0011,0012,0013,0014,0015,0016,0017,0018'
AS C_ID
FROM DUAL
SELECT A.ID
FROM (SELECT ID, C_ID
FROM ID_M
WHERE NEW_PATH IS NOT NULL AND C_ID IS NOT NULL) A
, (SELECT DISTINCT REGEXP_SUBSTR (AA.C_ID
,'[^,]+'
,1
,LEVEL)
C_ID
FROM (SELECT REPLACE (AAA.C_ID, ',', ',') C_ID
FROM (SELECT ID, C_ID
FROM ID_M
WHERE NEW_PATH IS NOT NULL AND C_ID IS NOT NULL)
AAA) AA
CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (AA.C_ID, '[^,]+', ''))
+ 1) B
WHERE INSTR (','
|| A.C_ID
|| ','
,','
|| B.C_ID
|| ',') > 0
도움 요청 드립니다 .
WITH id_m AS
(
SELECT 'A' id, 'x' new_path
, '0000,0001,0002,0003,0004,0005,0006,0007,0008,0009,0010' c_id
FROM dual
UNION ALL SELECT 'B', 'x', '0001,0003,0005,0007,0009' FROM dual
)
SELECT id
, lv
, SUBSTR(c_id, lv * 5 - 4, 4) c_id
FROM id_m
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99)
WHERE new_path IS NOT NULL
AND c_id IS NOT NULL
AND lv <= (LENGTH(c_id) + 1) / 5
ORDER BY id, lv
;
감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.