OLTP기술(문서번호 처리기술, 시퀀스와 ROWNUM)

1. 문서번호 처리 기술

1.1 SELECT MAX 방식

• 개발시 자주 만나게 되는것중 하나가 키값 부여, ID나문서 번호를 부여하는것을 채번이라한다
• PO1001 등과같은 키값은 PO를 잘라내고 1001을 증가하여 채번, 이런 방식을 SELECT~MAX방식이라고한다.
• SELECT MAX방식은 여러명이 사용할 경우 잠재적 오류와 성능이슈를 가지고있다

1.2 SELECT MAX 방식의 성능

• 채번시에도 TABLE ACCESS FULL이 발생할수 있다, 조건에 인덱스가 없을경우.
• INDEX RANGE SCAN(MIN/MAX)를 이용해 최댓값이나 최솟값을 빠르게 구할수있다.
• 제일 앞이나 제일뒤의 1건만 읽기때문에 성능이 매우 훌륭
• 인덱스 선두컬럼과 등치(=) 조건일때만 INDEX RANGE SCAN(MIN/MAX)사용가능
• PO_NO를 이용한 SELECT ~MAX
  인덱스 사용하지않고도 채번을 효율적으로 할수있다.
  PK인 PO_NO를 활용하여 INDEX RANGE SCAN(MIN/MAX)가 작동된다.

  SELECT MAX(T1.PO_NO)
  FROM T_PO T1
  WHERE T1.PO_NO >= 'PO' || '20170302'
  AND T1.PO_NO < 'PO' || TO_CHAR(TO_DATE('20170302','YYYYMMDD') + 1, 'YYYYMMDD')

1.3 SELECT MAX 방식의 중복 오류

• SELECT~MAX방식의 채번은 중복오류가 발생할 가능성이있다.(여러명이 작업중일때)
• 세션 1에서 채번후 COMMIT전에 세션2에서 채번하면 WAIT가되며, 세션1이 COMMIT하면 세션2는 중복오류가 발생
• 이와같은 문제로 매우 빈번하게 발생하는 채번이라면 'SELECT~MAX'방식을 사용하지 않는 것이 좋다.
• SELECT ~ MAX방식은 별도의 객체(테이블,시퀀스, 사용자함수)를 생성할 필요가 없어 편리하나,중복오류와 인덱스가적절하지못하면 성능이슈가 발생할 수 있다.

1.4 채번 테이블

• 중복오류를 피하기 위해 채번 테이블을 사용할 수 있다(채번테이블도 100%완벽하게 오류제거못함)
• 채번이 이루어질때마다 해당일자의 마지막 구매오더번호를 UPDATE해 관리.
• 중복오류, 동시성저하, 관리비용의 문제가있다.

1.5 채번 함수

• 사용자정의 채번함수를 뜻한다.

2. 시퀀스와 ROWNUM

2.1 시퀀스 객체

• 증가하는 숫자를 얻을수있으며, PK부여에 많이 사용, PK부여과정에서 성능향상이나 모델단순화를 위해 사용
• 무조건적인 시퀀스 사용은 좋지않다. 명확하고 간단한 PK후보가 있다면 해당컬럼을 PK로 잡아주는 것이 좋다.
• PK를 구성하는 컬럼이 너무많거나 PK값 처리 성능이 걱정된다면 시퀀스를 고려
• 시퀀스사용시 1,2,4 와같이 3이빠지는 경우가 발생(COMMIT, ROLLBACK등에 의해)

2.2 잘못 활용한 시퀀스

• 절대사용하면 안되는 패턴이있음, 시스템을 망가트리는 주범이 될수있다.
• SEQ.NEXVAL로 INSERT후 바로 SELECT MAX(PK) ~ 를 사용하면 절대안된다.
• 이어지는 후행 처리가 필요할경우 CURRVAL이나 NEXVAL을 변수에 담아놓고 사용해야한다.

2.3 최근 데이터를 가져오는 기술

• 최근 판매 3건 OR 마지막 주문등을 조회하는경우가 있다.
• 이때ROWNUM과 적절한 인덱스 이용하면 최소비용으로 데이터를 읽어낼 수 있다.
• 인덱스에 포함되어있는 컬럼으로 ORDER BY후 ROWNUM으로 필요한만큼 끊어내면 효율적으로 최신 OR 오래된 데이터순으로 데이터를 가져올수있다
• ROWNUM을 사용하 SQL에는 적절한 인덱스가 필요.
• ROWNUM을 위한 복합 인덱스 컬럼 선정 규칙
  아래 규칙보다 실행계획에서 원하는 방향으로 처리되었는지 확인하는것이 더욱 중요
  • WHERE절에 조건으로 사용된 컬럼을 복합 인덱스의 선두컬럼으로
    조건이 여러개라면 등치(=)조건 컬럼을 앞에, 범위 조건을 뒤쪽에
  • ORDER BY에 사용된 컬럼을 1번에서 정의한 컬럼뒤에 차례대로 위치시킨다
• ORDER BY 위치 : ORDER BY가 인라인뷰 안에있고 ROWNUM조건은 인라인뷰 밖에있어야한다.
  ORDER BY와 ROWNUM이 같은 블록안에있으면 ROWNUM처리후 ORDER BY 처리
• 그외 추가조건은 ROWNUM 바깥 블록에사용!!!