INDEX 생성이나 REBUILD 시의 LOCKING 현상

INDEX 생성이나 REBUILD 시의 LOCKING 현상 (7.3 ~ 8.1)
====================================================

이 자료는 index 생성이나 rebuild와 관련된 lock 현상에 대해서 설명한다.
data가 점차 커지고, 24*7 업무가 많아지는 상황에서 index recreate나
rebuild 작업 진행 시 업무에 지장을 줄 수 있는 부분이 무엇인지를 확인할
필요가 있다. 특히 partition table의 경우 global index는 base table의
partition이 drop이나 split과 같은 변경이 가해지면 index recreate나
rebuild가 요구되어지므로, 이 때의 lock 발생에 대해 알아보도록 한다.

1. ORACLE 7.3 ~ 8.0
~~~~~~~~~~~~~~~~~~~
index create나 rebuild 중에는 그 table에 Share Mode (S) lock을 걸어,
그 table에 대한 DML이 불가능하게 된다.
v$lock에서 확인하여 보면 type은 TM이 되고, lmode는 4 (share)로 나타난다.
index를 완전히 재생성하는 것보다는 rebuild하여 lock이 걸리는 시간을
줄이도록 하는 것이 바람직하며, index 생성 시에는 NOLOGGING
(7.3에서는 UNRECOVERABLE) option을 사용하면 index 생성 시간을 줄일 수 있다.


2. ORACLE 8.1 (ONLINE option 사용)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oracle 8.1.x version부터는 index 생성이나 rebuild 시에 해당 table에 DML이
가능하도록 ONLINE option을 추가하였다. index 생성이나 rebuild 시에 table에는
Row Share (RS) table lock이 걸려서 DML은 가능하게 되나, table을 변경하고
삭제하는 등의 DDL은 물론 허용되지 않는다.

제약 상황: ONLINE option을 지정하여 index를 생성/rebuild 시에 해당 table에
대한 parallel DML은 지원되지 않는다. 이 때 parallel DML을
수행하면 오류가 발생하게 된다.

index 생성 중에 DML을 허용하면 consistency는 어떻게 보장되는가?
그것은 index 생성이나 rebuild 중에 발생하는 data의 변경에 대해서 temporary
journal table을 만들어서 그 변경 사항을 기록하게 된다.
journal은 IOT (Index Organized Table) table 형태로 만들어진다.

index 생성이나 rebuild가 끝나면 만들어진 index 내에 journal table의
record를 merge시킨다. merge시킬 때 journal table을 각각의 row 별로 scan을
하는데 20건에 한번 씩 commit을 수행한다. lock이 걸려 있는 row는 skip을
하게 되므로 완전한 merge를 위해서는 journal table을 여러번 내부적으로
scan하게 된다.

이러한 이유로 해당 table에 대해서, 트랜잭션이 매우 많이 발생하는 때에는
index 생성이나 rebuild를 하지 않도록 권하고 있다.

ONLINE option을 지정하는 방법은 다음과 같다.

SQL> create index dept_idx on dept(deptno) online;
SQL> alter index dept_idx rebuild online;