농부지기

[ Oracle- 자료 복제 기본 ]

--0. 행복제 응용 문제 1 SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 10; --1. 행복제 기본 유형과 조인 -- ==> 쓸데 없이 많은 수(99) 만큼 복제하여 조인하는 비효율 발생 -- ==> MAX(cnt) 만큼만 복제 하면 어떨까? 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 pk, cnt, lv FROM t , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99) WHERE lv <= cnt ORDER BY pk, lv ; --2. MAX(cnt) 만큼만 복제 하는 방법 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 pk, cnt, lv FROM t , (--a. 여기에서 가장 큰 cnt를 구한다. (이 수만큼 복제 된다.) 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 ; --○ 행복제 응용 문제 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 ;

--http://www.gurubee.net/article/55635#comment_116470

--http://rwijk.blogspot.kr/2007/11/interval-based-row-generation.html