WITH m_data
AS
(
	SELECT col_nm,COMMENTS
		,(LENGTH(COL_NM) - LENGTH(REPLACE(COL_NM, '_', '')) + 1) AS CNT
	from(
		SELECT 'COL_QTY_1' AS COL_NM, '컬럼1' AS COMMENTS
		FROM DUAL
		UNION ALL
		SELECT 'COL_QTY_2' AS COL_NM, '컬럼2' AS COMMENTS
		FROM DUAL
		UNION ALL
		SELECT 'COL_QTY_AMT_1' AS COL_NM, '컬럼금액1' AS COMMENTS
		FROM DUAL
		UNION ALL
		SELECT 'AMT1' AS COL_NM, '금액1' AS COMMENTS
		FROM DUAL
	)
)
SELECT *
FROM(
	SELECT ROW_NUMBER() OVER ( PARTITION BY col_nm ORDER BY LEVEL) AS RN, LEVEL,M.*
	FROM M_DATA M
	WHERE 1=1
	CONNECT BY LEVEL <= CNT
	ORDER BY col_nm,LEVEL
)
WHERE 1=1
  AND RN <= CNT
ORDER BY col_nm

이렇게 까지작성은했는데 샘플말고 실제 테이블가지고 조회할떄는 너무 오래걸리는데요 혹시 속도 향상시키면서 아래 원하게 나올수 있을까요?

여기 col_nm 조회를 해보시면 "_" 조합으로 이루어져있습니다.

그래서 cnt 만큼 나눠주려고 합니다.(as_is 컬럼을 가지고 단어를 만드려는 작업)

ex) COL_QTY_AMT_1

-- 로우를

col 컬럼금액1

qty 컬럼금액1

amt 컬럼금액1

1 컬럼금액1

이렇게 나눠서 조회르하려고합니다.

어떤식으로 쿼리를 짜야 될까요??