기억력 보존을 위한 지원군

 - 둘다 복구에 사용된다. Redo의 경우 '다시실행한다' 라는 의미로 제대로 반영되지 못한
   변경사항을 다시 재현하는 역할을 한다. 이를 'roll forward단계'라 한다.
   이때 버퍼 캐시에 최종커밋되지 않은 변경사항까지 모두 복구되기 때문에 이러한 사항들을
   롤백 해야 하는데 이를 Undo라고 하며 '되돌린다' 라는 의미로 'rollback 단계'라고 한다.

1. Redo

1) online redo 로그 : 실제 로그버퍼에 버퍼링된 로그 엔트리를 기록, 최소 2개 이상의 파일로

                                구성되며 라운드로빈 방식사용.

2) archived redo 로그 : 라운드로빈 방식에 의해 online redo 로그파일이 재사용 되기 전에 다른 위치로

                                    백업해둔 파일.

▶ Redo로그의 사용 목적
  - Database Recovery : 물리적으로 디스크가 깨지는 등의 문제 발생시 Archived Redo로그가

                                      사용된다.
  - Cache Recovery : 모든 DBMS는 I/O 성능 향상을 위해 버퍼 캐시가 사용되는데 이는 휘발성으로

                                  디스크 상의 데이터 블록에 기록되지 않은 상태에서 인스턴스가 비정상

                                  종료되면 작업내용을 모두 잃게됨.
                                  이에 재기동시 Online Redo 로그에 저장된 기록사항을 읽어 버퍼캐시에만 있고 

                                  데이터 블록에 반영되지 못한 변경사항을 재현함(roll forward단계)
  - Fast Commit : 변경된 메모리 버퍼블록을 딧크 상의 테이터 블록에 기록하는 작업은 Random 엑세스
                          방식으로 이루지기 때문에 느리지만, 로고는 Append 방식으로 기록하므로 상대적으로

                          매우 빠르다. 

  이에 트랜잭션 발생시마다 우선 변경사항을 Append 방식으로 빠르게 로그에 기록하고 
  메모리 블록과 데이터 블록의 동기화는 적절한 수단(DBWR, Checkpoint를 이용해

  나중에 배치처리) 으로 일괄 수행한다.
  이러한 내용들이 Redo로그에 기록되며 오라클은 Redo로그를 믿고 언제든 Recovery

  가 가능한 상태가 되므로 빠르게 Commit을 완료할 수 있다.
  이러한 Fast Commit은 빠르게 트랜잭션을 처리해야 하는 모든 DBMS의 공통적인 메커

  니즘이다.

   ▶ Write Ahead Logging : 버퍼 캐시에 있는 블록 버퍼를 갱신하기 전에 먼저 Redo 엔트리를
                                          로그 버퍼에 기록해야 하며, DBWR이 버퍼 캐시로부터 Dirty 블록들을

                                          디스크에 기록하기 전에 먼저 LGWR이 해당 Redo 엔트리를 모두 Redo로그

                                          파일에 기록했음이 보장되어야 한다.
 
2. Undo
 - Undo 세그먼트는 구조적으로 볼 때 데이터를 저장하는 일반 세그먼트와 별반 다르지 않다.
   익스텐트 단위로 확장되고, 빠른 읽기/쓰기를 위해 Undo블록들을 버퍼 캐시에 캐싱하며,
   데이터 유실 방지를 위해 Redo로그에 로깅하는 점도 같다.
   다른 점이라면 저장되는 내용으로, 각 트랜잭션 별로 Undo세그먼트를 할당해주고
   (두 개의 이상의 트랜잭션이 하나의 Undo블록 세그먼트를 할당받아 같이 사용할 수 있음)
   그 트랜잭션이 발생시킨 테이블과 인덱스에 대한 변경사항들을 Undo 레코드 단위로
   Undo세그먼트 블록에 차곡차곡 기록된다.

 ▶ Undo 세그먼트의 사용 목적
  - Transaction Rollback : 트랜잭션에 의한 변경사항을 최종 커밋하지 않고 롤백하고자 할때
                                         Undo 데이터가 사용됨.
  - Transaction Recovery(rollback 단계) : Instance Crash 발생 후 Redo를 이용해서

                                                                'Roll forward단계'가 완료되면 최종커밋되지 않은 변경사항

                                                                까지 모두 복구되는데 이러한 트랜잭션들을 롤백하는데

                                                                사용된다.
  - Read Consistency : DB2, MS-SQL, Sybase 등은 Lock을 통해 읽기 일관성을 구현하지만 오라클은

                                    Undo데이터를 이용해서 이를 구현한다.

 ※ 상세 과정

 트랜잭션을 시작하려면 먼저 Undo 세그먼트 헤더에 있는 트랜잭션 테이블로 부터 슬롯을 할당받아야 하며, 할당받은 슬롯의 헤더에 자신이 현재 Active 상태임을 표시하고서 갱신을 시작한다.
 Active 상태의 트랜잭션이 사용하는 Undo블록과 트랜잭션 테이블 슬롯은 상태정보가 commited로 변경되기 전까지는 절대 다른 트랜잭션에 의해 재사용되지 않는다.
 이때부터 트랜잭션이 발생시키는 데이터 또는 인덱스 블록에 대한 변경사항은 Undo블록에 레코드로서 하나씩 차례대로  기록된다. Undo 세그먼트 헤더는 가장 마지막 Undo 레코드와 Last UBA(Undo Block Address)라는 일종의 포인터로 연결되어 있으며 각 Undo 레코드 간에는 체인형태로 연결되어 있다.
 v$transaction 뷰에 있는 used_ublk와 used_urec 칼럼을 통해 각각 현재 사용중인 Undo블록 개수와 Undo레코드 양을 확인할 수 있다. 인덱스가 전혀 없는 테이블이라면 한건을 갱신할때마다 used_urec 값이 하나씩 증가하지만 인덱스가 있다면 인덱스의 갱신내용까지 레코드로 추가된다.

 ▶ 블록 헤더 ITL 슬롯
  - Undo 세그먼트 헤더에 트랜잭션 테이블 슬롯이 있다면 각 데이터 블록과 인덱스 블록헤더에는

     ITL슬롯이 있다.
  - 특정 블록에 속한 레코드를 갱신하려면 먼저 블록 헤더로부터 ITL슬롯을 확보해야 한다.
    거기에 트랜잭션 ID를 기록하고 현재 해당 트랜잭션이 Active 상태임을 표시한 후에 블록 갱신이

    가능하다.
  - 오라클에서는 ITL슬롯 부족으로 인해 트랜잭션이 블로킹 되는 현상을 최소화할 수 있도록 3가지

     옵션을 제공한다.
    테이블을 생성할 때 initrans, maxtrans, pctfree 파라미터를 지정한다.
    각각 ITL슬롯 초기할당 개수, ITL슬롯 최대할당 개수, ITL슬롯 할당에 사용될 수 있는 예약된 데이터

    공간이다.

 ▶ Lock Byte
  - 블록헤더에서 블록으로 내려오면 Lock Byte에 대해 알아볼 필요가 있다.
    오라클은 레코드가 저장되는 로우마다 그 헤더에 Lock Byte를 할당해서 해당 로우를 갱신 중인

    트랜잭션의 ITL슬롯번호를 기록해둔다. 이것이 로우단위 Lock이다.
  - 레코드를 생신하려고 할 때 대상 레코드의 Lock Byte가 활성화 되어 있으면 ITL 슬롯을 찾아가고,
    ITL슬롯이 가리키는 트랜잭션 테이블 슬롯을 찾아가서 아직 Active 상태라면 트랜잭션이 완료될때까지

    대기한다.
  - ITL 슬롯에 기록되는 내용중 UBA(Undo Block Address)가 트랜잭션에 의해 발생한 변경 이전

    데이터가 저장된 Undo 블록주소를 가리키는 포인터 정보로 '문장수준 읽기 일관성' 구현을 위해

    CR Copy를 생성해서 과거버전으로 되돌리려고 할때 사용된다.   
 

Oracle에서는 정렬시 null값을 제일 마지막에 위치시킨다.(ms-sql은 반대) 
null 값을 제일 큰 값으로 인식한다는 말이다.

즉, 아래와 같다.

​-----------------------------------------------------------------

​
하지만 order by에 옵션을 주어 이러한 null값에 순위를 바꿔줄 수가 있다. 

select *
from
(
 select 'aaa' col_1 from dual
 union all
 select 'bbb' from dual
 union all
 select null from dual
)
order by col_1 nulls first;

​

위와 같이 설정하게 되면 null 값이 제일 위에 위치하게 된다.
주의할 점은 해당 옵션은 null이 아닌 나머지 데이터 정렬에는 어떠한 영향도 주지 않는다.
단지 null값의 순위를 '처음' 또는 '끝'으로 바꿔준다. 

2, 3번의 경우 제약조건을 활성화시키라는 건데

​기존데이터의 제약조건 위배 여부를 체크할지 말지에 따라 다르게 사용된다.

​

​2번의 경우 제약조건 활성화시 기존에 있던 데이터의 제약조건 위배사항도 함께 체크하여
위배되는 데이터가 있을시 활성화되지 않는다.
3번의 경우 기존에 있던 데이터의 제약조건 위배사항은 체크하지 않기 때문에
활성화하려는 제약조건에 위배되는 기존데이터가 있더라도 정상적으로 활성화가 가능하다.

​
일반적으로 정합성을 지키기 위해서는 2번을 사용해야 할 것 같지만
3번을 사용하는 경우는 언제인지 모르겠다...

​

disable도 기존데이터 체크여부를 지정할 수 있지만 novalidate만 가능하다.
아무것도 지정하지 않으면 novalidate가 기본값이기 때문에 명시적으로 선언이 가능하나
validate의 경우 구문에러를 발생시킨다. 

1. 부모키 삭제시 자식키를 null로 update

alter table 테이블명 add constraint 제약조건명

foregin key (외래키지정로 지정할 칼럼명)

references 참조할테이블명(참조할칼럼명)

on delete set null; 

---------------------------------------------------

- MERGE문은 특정 테이블에 대해 조건에 따라 삽입,갱신,삭제 작업을 한번에 처리할 수 있다.

merge
into t_test A
using (select 13 no from dual) B
on (A.no = B.no)
when matched then
  update set name = '개발자133'
  delete where (parent_no = 3)
when not matched then
  insert (no, name, parent_no)
  values(14, '개발자14', 3);

- INTO : DATA가 UPDATE되거나 INSERT 될 테이블 또는 뷰를 지정.

- USING : 비교할 테이블 또는 뷰를 지정.

  자기자신(INTO절에서 정의한 뷰 또는 테이블)과 비교하려면 아래와 같이 

  using dual 구문을 사용하도록한다.

merge
into t_test A
using dual

on (A.no = 13)
when matched then
  update set name = '개발자133'
  delete where (parent_no = 3)
when not matched then
  insert (no, name, parent_no)
  values(14, '개발자14', 3);​

- ON : 비교할 조건을 명시 

- WHEN MATCHED : ON 조건절이 TRUE인 ROW에 수행 할 작업을 명시

  * update, delete만 명시 가능하다.

  * on 절에서 조건으로 사용된 칼럼은 update, delete 대상이 될 수 없다.

  * update절은 단독으로 사용가능하나, delete절은 단독으로 사용할 수 없다.

  * insert만 명시 가능하다.​

1. 구현 조건

1) 원글은 내림차순, 답변글은 오름차순 정렬을 하도록 한다.
2) 원글이 삭제된 경우에도 답변글은 유지하도록 한다.  
 - 기본적으로 계층처리시 level을 유지시키기 위해 데이터를 삭제하더라도
   실제로 삭제하지는 않고 삭제를 의미하는 칼럼을 추가하여 삭제여부를 저장한다.
   실제로 삭제를 하게 되면 delete가 아니라 update를 통해 해당 칼럼을
   수정하고, 해당 레코드의 바로 아래 자식의 title에 [원글삭제] 라는 접두어를 추가하는
   procedure를 만들어서 삭제 수행시 해당 procedure를 사용하도록 하자.
   이렇게 되면 원글이 삭제되더라도 filtering하여 원글은 보이지 않지만
   답변글은 level이 유지된 채로 확인이 가능하다.

2. 테스트용 테이블 생성

​
create table t_replayboard_test(
 board_no number(10),    --기본키
 title varchar2(100),    --제목
 c_date date default sysdate,  --등록일
 parent_no number(10),    --상위조상
 prototype_no number(10) not null, --최상위조상(그룹번호)
 del_yn char(1) default 'N'
);

--기본키 생성
alter table t_replayboard_test add constraint t_replayboard_test_pk
primary key (board_no);

--자기참조 모델링

--바로 위의 부모를 나타내는 parent_no의 경우 board_no에 존재하는 번호가 등록가능하다.​
--기본적으로 답변글이 있는 데이터는 삭제할 수 없도록 한다.
alter table t_replayboard_test add constraint t_replayboard_test_fk01
foreign key (parent_no) references t_replayboard_test(board_no);

--최상위조상 을 나타내는 번호의 경우 board_no에 존재하는 번호만 등록가능하다.
alter table t_replayboard_test add constraint t_replayboard_test_fk02
foreign key (prototype_no) references t_replayboard_test(board_no);

--del_yn 입력값 제한
alter table t_replayboard_test add constraint t_replayboard_test_ck01
check (del_yn in('Y','N'));

--계층처리 및 삭제로직을 위한 인덱스 생성
create index t_replayboard_test_idx02
on t_replayboard_test(parent_no);

2. 테스트용 데이터 입력

--원글 등록
begin
  for i in 1..10
  loop
    insert into t_replayboard_test(board_no, title, c_date, prototype_no)
    values(i, '원글'||i, sysdate+(i/24/60/60), i); --등록시간은 1초의 차이가 나도록    
  end loop;
  commit;
end;

​

--답변등록

--답변1
insert into t_replayboard_test(board_no, title, parent_no, prototype_no, c_date)
values(11, '[RE]원글1의 답변', 1, 1, sysdate+(1/24/60/60));
--values(시퀀스, [RE]가 앞에 붙은 제목, 부모키, 최고조상키, 등록일);

--답변1의 답변1
insert into t_replayboard_test(board_no, title, parent_no, prototype_no, c_date)
values(12, '[RE][RE]원글1의 답변의 답변1', 11, 1, sysdate+(2/24/60/60));

--답변1의 답변1의 답변1
insert into t_replayboard_test(board_no, title, parent_no, prototype_no, c_date)
values(13, '[RE][RE][RE]원글1의 답변의 답변의 답변', 12, 1, sysdate+(3/24/60/60));

--답변1의 답변2
insert into t_replayboard_test(board_no, title, parent_no, prototype_no, c_date)
values(14, '[RE][RE]원글1의 답변의 답변2', 11, 1, sysdate+(4/24/60/60));

--답변2
insert into t_replayboard_test(board_no, title, parent_no, prototype_no, c_date)
values(15, '[RE]원글9의 답변', 9, 9, sysdate+(5/24/60/60));

--답변2의 답변1
insert into t_replayboard_test(board_no, title, parent_no, prototype_no, c_date)
values(16, '[RE][RE]원글9의 답변의 답변1', 15, 9, sysdate+(6/24/60/60));

--답변2의 답변2
insert into t_replayboard_test(board_no, title, parent_no, prototype_no, c_date)
values(17, '[RE][RE]원글9의 답변의 답변2', 15, 9, sysdate+(7/24/60/60));

--답변2의 답변1의 답변1
insert into t_replayboard_test(board_no, title, parent_no, prototype_no, c_date)
values(18, '[RE][RE][RE]원글9의 답변의 답변1의 답변1', 16, 9, sysdate+(8/24/60/60));

--답변2의 답변1의 답변2
insert into t_replayboard_test(board_no, title, parent_no, prototype_no, c_date)
values(19, '[RE][RE][RE]원글9의 답변의 답변2의 답변2', 16, 9, sysdate+(9/24/60/60));

commit;

​

4. 계층데이터 select 쿼리

select lpad(' ', (level-1)*5)||title title,
       board_no,
       parent_no,
       prototype_no,
       c_date      
from t_replayboard_test
where del_yn = 'N'
start with parent_no is null
connect by parent_no = prior board_no
order siblings by prototype_no desc, board_no asc;

​

​

4. 글 삭제 로직

 - 아래는 익명블록으로 생성하였지만 실제 시스템에서는 procedure로 만들어서 사용하면 되겠다.

--삭제로직
--board_no=16 삭제
declare
  p_board_no number(10);
begin
  p_board_no := 16;
 
  update t_replayboard_test
  set del_yn = 'Y'
  where board_no = p_board_no;
 
  update t_replayboard_test
  set title = '[원글삭제] '||title
  where parent_no = p_board_no;
 
  commit;
end; 

​

​