코드 조각-Android, Java, Spring, JavaScript, C#, C, C++, PHP, HTML, CSS, Delphi

Oracle의 ROLLUP 함수의 개념

아래와 같은 테이블이 있다고 할때 부서별 연봉(SAL) 소계와 전체 SAL 총계를 도출해 내고자 할때 사용할수 있는 함수가 ROLLUP 함수이다.
한마디로 말하면 GROUP BY로 묶을 칼럼의 소계를 도출하는 함수이다.

DEPT      |  RANK  |  SAL
--------------------------------
기획부    |  부장   | 3800
기획부    |  부장   | 4000
기획부    |  차장   | 1800
마케팅부 |  부장   | 4000
마케팅부 |  차장   | 2000
마케팅부 |  과장   | 1500

SELECT DEPT, RANK, COUNT(RANK) "RANK_COUNT", SUM(SAL) FROM TEST GROUP BY ROLLUP(DEPT, RANK);

위 쿼리가 의미하는 바는 DEPT와 RANK라는 두 칼럼의 데이터가 같은 튜플들을(row) 하나로 묶어(grouping) SAL의 합을 도출하고
DEPT 칼럼의 데이터가 같은 튜플들을 하나로 묶어 SAL의 소계를 도출하고 최종적으로는 전체 튜플들 모두의 합(총계)를 도출하는 기능을 하는 것이 ROLLUP() 함수이다.

DEPT    |  RANK  |  RANK_COUNT | SAL
------------------------------------------------------------------------------
기획부    |  부장  | 2                   | 7800 ==> 기획부, 부장의 합계
기획부    |  차장  | 1                   | 1800
기획부    |  (null) | 3                   | 9600 ==> 기획부 소계
마케팅부 |  과장  | 1                   | 1500
마케팅부 |  부장  | 1                   | 4000
마케팅부 |  차장  | 1                   | 2000
마케팅부 |  (null) | 3                   | 7500 ==> 마케팅부 소계
(null)        |  (null) | 6                   | 17100 ==> 전체 총계

오라클에서 다음과 같은 상황에 대해 DB를 어떻게 변경하는지에 대해 포스팅하고자 한다.

상황 : 
-. 테이블을 새로 생성할수 있는 상황이 아닌 현재 이미 사용하고 있어 데이터가 적재되어 있는 테이블의 PK 변경 및 새로운 칼럼 추가하고자 하는 상황이다.
-. 현재 3개의 PK가 잡혀있는데 이 3개의 PK를 삭제하고 새로 추가하는 칼럼을 PK로 잡고자 한다.
-. PK로 잡히게 될 새로 추가하는 칼럼은 auto increment가 되는 칼럼이다.
-. 테이블 이름은 TB_MEMBER로 가정한다.
-. 새로운 PK로 사용하게 될 새롭게 생성하게 될 칼럼은 RSV_SEQ로 가정한다.

이상의 상황에서 다음 단계를 밟아 원하는 DB를 새롭게 구성할수 있다.

(1) 현재 사용하고 있는 3개의 PK를 삭제한다. TB_MEMBER라는 테이블에서 현재 사용하고 있는 PK들이 삭제된다. PK라는 속성이 제거되는것이지 해당 칼럼 자체가 삭제되는 것은 아니다.

ALTER TABLE TB_MEMBER DROP PRIMARY KEY; 

(2) 새로운 PK로 사용할 칼럼을 추가한다. 새로 추가될 칼럼인 RSV_SEQ를 추후 PK로 사용하게 될 것이고 이 칼럼은 auto increment되는 값을 갖게 될 칼럼이다. 그러나 지금의 시점에서는 단지 새로운 칼럼을 하나 추가하는 것 뿐이다. 

ALTER TABLE TB_MEMBER ADD(RSV_SEQ NUMBER); 

(3) PK로 사용하게 될 새로 추가할 칼럼의 검색 속도를 높이기 위해 index를 생성한다.

CREATE UNIQUE INDEX TB_MEMBER_PK ON TB_MEMBER(RSV_SEQ); 

(4) 새로 추가하게 될 PK 칼럼이 auto increment이므로 이에 대한 sequence를 생성한다. 오라클은 MySQL과 달리 해당 칼럼에 직접 auto_increment 속성을 지정할수가 없어서 별도의 시퀀서를 만들어 사용해야 한다. sequence의 이름은 TB_MEMBER_SEQ이고 1부터 1씩 자동 증가하는 방식의 sequence이다.

CREATE SEQUENCE TB_MEMBER_SEQ START WITH 1 INCREMENT BY 1; 

(5) 이상의 과정까지에서 PK로 사용하게 될 새로운 칼럼에는 아래와 같이 모두 null이 들어 있을 것이다.
---------------------------------
rsv_seq | aaa | bbb | ccc ...
---------------------------------
 null      | ...    | ...    | ...
 null      | ...    | ...    | ...
 null      | ...    | ...    | ...
---------------------------------
...
새로 생성한 칼럼인 RSV_SEQ에 대해 앞에서 생성한 sequence를 이용해서 auto increment된 값을 RSV_SEQ 칼럼에 update해 줘야 한다. 아래의 쿼리에 의해 기존 테이블의 각 row의 RSV_SEQ의 값은 1부터 시작해서 1씩 증가된 값들이 비로소 저장되게 된다.

UPDATE TB_MEMBER SET RSV_SEQ=TB_MEMBER_SEQ.nextval; 

---------------------------------
rsv_seq | aaa | bbb | ccc ...
---------------------------------
 1          | ...    | ...     | ...
 2         | ...    | ...     | ...
 3         | ...    | ...     | ...
---------------------------------
...

(6) 이제 비로소 새로 생성한 칼럼을 PK로 설정할수 있다. 아래와 같이 

ALTER TABLE TB_MEMBER ADD CONSTRAINT TB_MEMBER_PK PRIMARY KEY(RSV_SEQ); 

테스트 단계이거나 개발 단계에서 기존 적재되어 있는 데이터를 모두 날려 버리고 테이블을 새로 생성할수 있다면 굳이 위와 같은 다소 복잡한 과정 필요없이 그냥

drop table ...
create table ...로 간단히 변경된 내용이 적용된 DDL로 원하는 테이블을 구성할수 있을것이나 그러나 이미 사용하고 있는 경우나 기존 있는 데이터를 유지해야할 경우 오라클에서는 위와 같은 단계를 밟아서 처리를 해야 한다.