현재 이관작업을 진행하다가 3개의 테이블에 있는 데이터를 1개의 테이블로 이관하는 작업을 맡았다. 3개의 테이블을 합치는 과정에서 PRIMARY KEY가 중복되는 건을 응용팀에 전하니 테이블마다 우선순위를 두어 무조건 들어가야 하는 테이블을 알려주었다.
TEMP 테이블에 3개의 테이블 데이터를 저장하고 그 이후 중복건을 제거하는 방법에 대해 알아보기에 앞서 ROWID, ROWNUM 에 대해 알아보자
🕵ROWID
Oracle Table의 모든 Row는 ROWID라는 독보적인 주소를 할당받는다.
주로 말하는 인덱스에서 ROWID를 통해서 OLTP에서 빠른 응답을 해야 할 상황에서 사용되기도 한다.
rowid 는 18글자로 되어있으며 다음과 같다.
AAAArs(1) AAD(2) AAAAUa(3) AAA(4) 와 같다면
1) AAAArs : data object id ( 독보적인 Block Id를 말한다.)
2) AAD : relative file number (저장되어있는 공간에 따라서 변경될수 있다.)
3) EAAAAAUaAAAJT : block number (블럭 내부에서 Sequence Number이며 Physical Address를 말한다.)
4) AAA : row slot number ( 해당 블럭에서 몇번째 위치하고 있는지를 나타낸다.)
이는 앞에서 옵티마이저를 다룰때 RBO(Rule Based Optimizer)에서 제일 빠른 기법인
Single Row By ROWID 에서도 사용된다.
ROWNUM
ROWNUM 은 SELECT 문에 대해서 논리적인 일련번호를 부여한다.
주로 ROWNUM 크기만큼 쿼리를 보여주고자 할 때 , 컬럼과 함께 일련번호를 출력할 때 사용된다.
ROWNUM , ROWID의 비교하기
둘이 공통적인 부분은 둘다 pseudo columns 라는 점이다.
(pseudo columns : 테이블 컬럼처럼 행동하지만 실제 테이블에 적재되지 않은 컬럼을 말한다.
다른 예시로는 Currval , Nextval가 있다.)
차이점으로는 위에서 말한 것 처럼 rowid는 영구적인 유니크한 값이라면 rownum은 일시적인 유니크한 값이라는 것이다.
다시말해 ROWNUM은 특정 SQL 문에만 적용되는 연속된 숫자이고, 반대로 행에 대한 고유 ID는 ROWID이다. 자세한 사항은 이곳을 참고 바란다.
이제 본론으로 들어가서 이관중에서 중복 데이터를 지우는 과정에 대해서 알아보자.
중복 데이터 제거하기
먼저 구글에 중복 데이터 제거를 검색해 보았다.
1) 첫 번째 블로그
DELETE FROM TEMP
WHERE ROWID IN (
SELECT ROWID FROM (
SELECT * FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY 컬럼1, 컬럼2,
... ORDER BY 컬럼1, 컬럼2) AS num
FROM TMP_STRS_MOMT
) WHERE num > 1
)
);위의 쿼리는 하나의 테이블에서 데이터를 지우는데 ORDER BY 로 해당 PRIMARY KEY를 정렬하게 되면 테이블 간의 우선순위가 없어지게 되어 내가 찾는 방법이 아니었다.
그리고 나서 ROWID를 활용한 중복된 데이터를 제거하는 방법 이곳에서 찾게 되었다.
평상시에 ROWID를 검색해봤을 때 들어온 데이터 값들이 SEQUENCE 처럼 일정하게 늘어나는 것이 생각하여 데이터를 이관할 때 뒤에 들어온 데이터를 ROWID를 이용해서 중복을 처리하려고 하였다.
하지만 ROWID는 데이터가 들어온 순서를 보장하지 않는다.
앞에서 말한 것 처럼 ROWID값들이 들어온 순서대로 커지면 좋겠어서 좋겠지만이곳 을 보면 아니라는 것을 알 수 있다.
일부 데이터만 봤을때 단순 하게 증가하는 것 같지만 ALTER TABLE TMP_TEST MOVE
를 하게 되면 해당 TABLESPACE에서 재구성하는 쿼리만으로도 ROWID 값이 변하는 것을 알 수 있다.
이 외에도 ROWID가 변하는 것은 다음과 같다.
After a rowid is assigned to a row piece, the rowid can change in special circumstances. For example, if row movement is enabled, then the rowid can change because of partition key updates, Flashback Table operations, shrink table operations, and so on. If row movement is disabled, then a rowid can change if the row is exported and imported using Oracle Database utilities.
단순하게 데이터를 넣는 작업에서는 안 변하지 않을까? 라고 생각했는데 위에서의 so on.. 절을 통해서 무조건 안 변한다고 장담할 수 없는 것이다. 테이블의 우선순위에 따른 데이터를 지울 때 우선순위가 높은 테이블의 데이터가 rowid가 높게 나온다면 데이터 정합성을 보존하지 못할것이다.
어쩔 수 없이 서브쿼리를 사용해야 할까?
회사에서 계속 고민하고 어떻게 하면 효율적으로 중복데이터를 제거할까를 고민하다 결국에는 단순 where 과 단순 서브쿼리로는 해결 할 수 없다는 것을 깨달았다.
(해답을 알고 계신다면 답변 바랍니다 😄)
아래 코드는 이곳 에서 확인 할 수 있으며 자세한 설명을 참조 바란다.
insert /*+ APPEND */into TEMP
select * from
(
select /*+ FULL(T1) PARALLEL(T1 8) */ * , 0 as Priority from T1
union all
select *, 1 as Priority from T2
union all
select *, 2 as Priority from T3
) 우선 3개의 테이블의 컬럼에 전처리를 생략하면 위와 같으며 우선순위 컬럼을 생성하고 TEMP 테이블에 중복 데이터 건들을 넣어둔 다음에
DELETE FROM TEMP A
WHERE PRIORITY < (SELECT MIN(PRIORITY) FROM TEMP A WHERE A.KEY = B.KEY)와 같이 중복 데이터를 제거하고 본 테이블에 INSERT 하는 과정을 통해 중복을 제거한다.
👆추가적으로
테이블의 데이터 건수가 1억건이 넘는 테이블들이어서 이행작업의 속도를 높이고 싶었다.
따라서 앞에서 배운 APPEND 와 PARALLEL 옵션을 주기 위해서 해당 테이블에 NOLOGGING 작업과 /+ enable_parallel_dml / (12버전) 을 사용하거나 ALTER SESSION ENABLE PARALLEL DML 를 사용하도록 하자.
그리고 해당 타겟 테이블에 인덱스 혹은 제약조건이 있다면 이행시에 UNDO 로그를 사용하기 때문에 인덱스와 제약조건을 DROP하고 이행 완료 후 다시 생성해주는 것이 좋다.
이곳 을 참고 했을때 단순하게 DISABLE CONSTRAINT, UNUSABLE INDEX 만으로는 UNDO 를 사용한다고 한다.
또한 primary key를 제거할 때 ALTER TABLE TEMP DROP PRIMARY KEY 만 하게 된다면 Primary Key를 생성할 때 인덱스와 제약조건을 동시에 생성하면 삭제할 때도 동시에 삭제가 되고 이미 생성된 인덱스를 사용해서 Primary Key를 생성하면, 위 구문 수행 시 제약조건만 삭제가 되고인덱스는 남아 있게 된다.
해당 primiary key가 어떻게 생성되었는지 모르기 때문에 아래와 같은 방법을 사용하도록 하자
1) 항상 인덱스와 제약조건을 한번에 삭제하고 싶을 때
ALTER TABLE 테이블명 DROP PRIMARY KEY DROP INDEX 를 사용하고
2) 항상 제약조건만 삭제하고 인덱스는 남겨 놓고 싶을 때
ALTER TABLE 테이블명 DROP PRIMARY KEY KEEP INDEX 를 사용한다.
출처: https://dev4u.tistory.com/873
포스트를 마무리하면서 😃
현재 이관준비를 하면서 오라클 쿼리에 대해서 고민을 많이 했었던 것 같고 이를 기반으로 나중에 쿼리를 좀 더 효율적으로 짜는 개발자로 성장하고 싶다.
