여러개의 반복되는 쿼리 수동으로 만들기 귀찮을 때...
비싼 오라클에게 일을 맡기자.
예를 들어 AAA 테이블의 data 를 select 해서 AAA2 로 insert 하는 경우
insert into AAA2 select * from AAA
이렇게 하면 된다.
하지만 AAA 의 데이터량이 많아서 운영중에 대량 insert 하기 부담스러운 상황일 경우..
마침 AAA 테이블의 key_date 컬럼이 있다고 치고 key_date 의 값에 따라 쪼개서 insert 를 할수 있다.
i.e) insert into AAA2 select * from AAA
where key_date between TO_DATE('20170915') +9/24 and TO_DATE('20170915') +10/24
and key_date < TO_DATE('20170915') +10/24;
이런식으로 9월 15일 9시부터 10시 까지의 데이터를 insert 하고 10시 부터 11시 계속 1시간 단위로
쪼개서 SQL 을 실행시킬수 있다.
근데 만약, 시간을 많이 쪼개야 한다거나 테이블이 AAA 와 동일하게 BBB, CCC, DDD 도 있다면..
손으로 일일이 작성하기 귀찮고 오타가 날 확률도 높아진다.
아래처럼 해보자.
select 'INSERT INTO '||TAB||'2 SELECT * FROM '||TAB||' WHERE KEY_DATE BETWEEN TO_DATE('||''''||'20170914'||''''||
') +'||(17 + RN) ||'/24 and TO_DATE('||''''||'20170914'||''''||') +'||(18 + RN)||'/24 and KEY_DATE < TO_DATE('||''''||'20170914'||''''||') +'||(18 + RN)||'/24;' stmt
from
(SELECT 'AAA' AS TAB FROM DUAL
UNION ALL
SELECT 'BBB' FROM DUAL
UNION ALL
SELECT 'CCC' FROM DUAL
UNION ALL
SELECT 'DDD' FROM DUAL
)T,
(SELECT ROWNUM RN
FROM DBA_TABLES
WHERE ROWNUM < 7
)
ORDER BY TAB, RN
========= 결과물 ===========================================================
INSERT INTO AAA SELECT * FROM AAA WHERE KEY_DATE BETWEEN TO_DATE('20170914') +18/24 and TO_DATE('20170914') +19/24 and KEY_DATE < TO_DATE('20170914') +19/24;
INSERT INTO AAA SELECT * FROM AAA WHERE KEY_DATE BETWEEN TO_DATE('20170914') +19/24 and TO_DATE('20170914') +20/24 and KEY_DATE < TO_DATE('20170914') +20/24;
INSERT INTO AAA SELECT * FROM AAA WHERE KEY_DATE BETWEEN TO_DATE('20170914') +20/24 and TO_DATE('20170914') +21/24 and KEY_DATE < TO_DATE('20170914') +21/24;
INSERT INTO AAA SELECT * FROM AAA WHERE KEY_DATE BETWEEN TO_DATE('20170914') +21/24 and TO_DATE('20170914') +22/24 and KEY_DATE < TO_DATE('20170914') +22/24;
INSERT INTO AAA SELECT * FROM AAA WHERE KEY_DATE BETWEEN TO_DATE('20170914') +22/24 and TO_DATE('20170914') +23/24 and KEY_DATE < TO_DATE('20170914') +23/24;
INSERT INTO AAA SELECT * FROM AAA WHERE KEY_DATE BETWEEN TO_DATE('20170914') +23/24 and TO_DATE('20170914') +24/24 and KEY_DATE < TO_DATE('20170914') +24/24;
INSERT INTO BBB SELECT * FROM BBB WHERE KEY_DATE BETWEEN TO_DATE('20170914') +18/24 and TO_DATE('20170914') +19/24 and KEY_DATE < TO_DATE('20170914') +19/24;
INSERT INTO BBB SELECT * FROM BBB WHERE KEY_DATE BETWEEN TO_DATE('20170914') +19/24 and TO_DATE('20170914') +20/24 and KEY_DATE < TO_DATE('20170914') +20/24;
INSERT INTO BBB SELECT * FROM BBB WHERE KEY_DATE BETWEEN TO_DATE('20170914') +20/24 and TO_DATE('20170914') +21/24 and KEY_DATE < TO_DATE('20170914') +21/24;
INSERT INTO BBB SELECT * FROM BBB WHERE KEY_DATE BETWEEN TO_DATE('20170914') +21/24 and TO_DATE('20170914') +22/24 and KEY_DATE < TO_DATE('20170914') +22/24;
INSERT INTO BBB SELECT * FROM BBB WHERE KEY_DATE BETWEEN TO_DATE('20170914') +22/24 and TO_DATE('20170914') +23/24 and KEY_DATE < TO_DATE('20170914') +23/24;
INSERT INTO BBB SELECT * FROM BBB WHERE KEY_DATE BETWEEN TO_DATE('20170914') +23/24 and TO_DATE('20170914') +24/24 and KEY_DATE < TO_DATE('20170914') +24/24;
INSERT INTO CCC SELECT * FROM CCC WHERE KEY_DATE BETWEEN TO_DATE('20170914') +18/24 and TO_DATE('20170914') +19/24 and KEY_DATE < TO_DATE('20170914') +19/24;
INSERT INTO CCC SELECT * FROM CCC WHERE KEY_DATE BETWEEN TO_DATE('20170914') +19/24 and TO_DATE('20170914') +20/24 and KEY_DATE < TO_DATE('20170914') +20/24;
INSERT INTO CCC SELECT * FROM CCC WHERE KEY_DATE BETWEEN TO_DATE('20170914') +20/24 and TO_DATE('20170914') +21/24 and KEY_DATE < TO_DATE('20170914') +21/24;
INSERT INTO CCC SELECT * FROM CCC WHERE KEY_DATE BETWEEN TO_DATE('20170914') +21/24 and TO_DATE('20170914') +22/24 and KEY_DATE < TO_DATE('20170914') +22/24;
INSERT INTO CCC SELECT * FROM CCC WHERE KEY_DATE BETWEEN TO_DATE('20170914') +22/24 and TO_DATE('20170914') +23/24 and KEY_DATE < TO_DATE('20170914') +23/24;
INSERT INTO CCC SELECT * FROM CCC WHERE KEY_DATE BETWEEN TO_DATE('20170914') +23/24 and TO_DATE('20170914') +24/24 and KEY_DATE < TO_DATE('20170914') +24/24;
INSERT INTO DDD SELECT * FROM DDD WHERE KEY_DATE BETWEEN TO_DATE('20170914') +18/24 and TO_DATE('20170914') +19/24 and KEY_DATE < TO_DATE('20170914') +19/24;
INSERT INTO DDD SELECT * FROM DDD WHERE KEY_DATE BETWEEN TO_DATE('20170914') +19/24 and TO_DATE('20170914') +20/24 and KEY_DATE < TO_DATE('20170914') +20/24;
INSERT INTO DDD SELECT * FROM DDD WHERE KEY_DATE BETWEEN TO_DATE('20170914') +20/24 and TO_DATE('20170914') +21/24 and KEY_DATE < TO_DATE('20170914') +21/24;
INSERT INTO DDD SELECT * FROM DDD WHERE KEY_DATE BETWEEN TO_DATE('20170914') +21/24 and TO_DATE('20170914') +22/24 and KEY_DATE < TO_DATE('20170914') +22/24;
INSERT INTO DDD SELECT * FROM DDD WHERE KEY_DATE BETWEEN TO_DATE('20170914') +22/24 and TO_DATE('20170914') +23/24 and KEY_DATE < TO_DATE('20170914') +23/24;
INSERT INTO DDD SELECT * FROM DDD WHERE KEY_DATE BETWEEN TO_DATE('20170914') +23/24 and TO_DATE('20170914') +24/24 and KEY_DATE < TO_DATE('20170914') +24/24;
자신의 상황에 따라 응용이 가능하다.
