단일행을 여러행으로 만들기(행복제, Row-Generation)

○ 행복제 기본 유형

0. 행복제 기본 유형
SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 10;

○ 행복제 기본 유형 사용시 유의사항
- dual 테이블을 이용하여 간단하게 원하는 수 만큼의 행을 생성 할 수 있습니다. 참~ 쉽죠잉~
- 유의 할 점은 dual 과 같이 1행 집합에만 적용해야 한다는 것입니다.
- 예를들어 10행의 자료를 각각 10건씩으로 복제 한다고 가정하고 위의 방법을 적용하면...
- 10 * 10 = 100개 행이 조회될꺼라 생각한다면? 아니아니 아니되오~
- 10행의 집합에 Connect By LEVEL <= 10 조건을 주었을 때 결과는?
- 놀라지 마세요~ 11,111,111,110 건이 발생됩니다.
- 수식까지 알 필요는 없지만 굳이 알고자 하신다면? (10^1 + 10^2 + 10^3 + ... + 10^10)
- 암튼 결론은 이렇게 쓰지 말아라 하는것입니다.

○ 행복제 응용 문제 1
레코드에 저장되어 있는 건수만큼 복제하기

-- 테스트용 데이터 --
WITH t AS
(
SELECT 1 pk, 2 cnt FROM dual
UNION ALL SELECT 2, 4 FROM dual
UNION ALL SELECT 3, 6 FROM dual
)
SELECT * FROM t;

1. 행복제 기본 유형과 조인
SELECT pk, cnt, lv
  FROM t
     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99)
 WHERE lv <= cnt
 ORDER BY pk, lv
;
==> 쓸데 없이 많은 수(99) 만큼 복제하여 조인하는 비효율 발생
==> MAX(cnt) 만큼만 복제 하면 어떨까?

2. MAX(cnt) 만큼만 복제 하는 방법
SELECT pk, cnt, lv
  FROM t
     , (SELECT LEVEL lv FROM (SELECT MAX(cnt) cnt FROM t) CONNECT BY LEVEL <= cnt)
 WHERE lv <= cnt
 ORDER BY pk, lv
;
==> 테이블을 두번 읽는 비효율 발생
==> 테이블에 직접 Connect By Level 조건을 걸면 한번만 읽고 가능하긴 한데...
==> 위에 언급한 유의 사항 때문에 안되겠군... 어떻게 안될까?

3. Connect_By_Root 조건 삽입.
SELECT pk, cnt, LEVEL lv
  FROM t
 CONNECT BY LEVEL <= cnt
 AND CONNECT_BY_ROOT pk = pk
;
==> 어라? 잘 되네? 이게 왜 되는거야? 뭔가 불안한데...
==> 11G 에서는 안됩니다. 고로 사용 불가...
==> ORA-30007: CONNECT BY ROOT 연산자는 START WITH 또는 CONNECT BY 조건에서 지원되지 않습니다.

4. dbms_random.value 조건 삽입.
SELECT pk, cnt, LEVEL lv
  FROM t
 CONNECT BY LEVEL <= cnt
 AND PRIOR pk = pk
 AND PRIOR dbms_random.value IS NOT NULL
;
==> 어라? 잘 되네? 이건 또 왜 되는거야? 불안한데...
==> 이것 역시 11G 에서는 안됩니다. 고로 사용 불가...라고 쓸려고 했는데.
==> 으미.. 잘 되네..참고 싸이트에서는 안된다고 쓰여 있던데...
==> 참고로 내 11g 버전은 11.2.0.1.0
==> 이걸 써야 되는 거야? 쓰지 말아야 하는거야? 고민되네...

5. Model 절 활용
SELECT pk, cnt, lv
  FROM t
 MODEL
 PARTITION BY (pk)
 DIMENSION BY (1 lv) 
 MEASURES(cnt)
 RULES(cnt[FOR lv FROM 1 TO cnt[1] INCREMENT 1] = cnt[1])
;

6. Multiset 을 이용한 방법
SELECT pk, cnt
     , TO_NUMBER(column_value) lv
  FROM t
     , TABLE(CAST(MULTISET(SELECT LEVEL FROM dual CONNECT BY LEVEL <= cnt) AS sys.dbms_debug_vc2coll))
;

7. 11G Recursive SQL
WITH t AS
(
SELECT 1 pk, 2 cnt FROM dual
UNION ALL SELECT 2, 4 FROM dual
UNION ALL SELECT 3, 6 FROM dual
)
, t1(pk, cnt, lv) AS
(
SELECT pk, cnt
     , 1 lv
  FROM t
 UNION ALL
SELECT pk, cnt
     , lv + 1 lv
  FROM t1
 WHERE lv + 1 <= cnt
)
SELECT *
  FROM t1
 ORDER BY pk, lv
;

별 별 방법이 다 있군요....

참고 : http://rwijk.blogspot.com/2007/11/interval-based-row-generation.html

○ 행복제 응용 문제 2
한 필드에 여러 값이 구분자로 저장되어 있을 때 구분자로 구별하여 행 나누기

WITH t AS
(
SELECT 1 pk, '1,3,5' v FROM dual
UNION ALL SELECT 2, '2,4,6,8' FROM dual
UNION ALL SELECT 3, '9,10,11' FROM dual
)
SELECT pk, lv
     , REGEXP_SUBSTR(v, '[^,]+', 1, lv) v
  FROM t
     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99)
 WHERE lv <= LENGTH(v) - LENGTH(REPLACE(v, ',')) + 1
 ORDER BY pk, lv
;