테이블에 일련번호(순번)를 증가시키는 방법

방법1) 두 개의 SQL 문장으로 처리

  SELECT MAX(주문번호) + 1
  FROM :V_주문일련번호
  FROM 주문 ;
  
  INSERT INTO 주문(주문일련번호, COL2, COL3...)
  VALUES ( V_주문일련번호, 'XXX','XXX'...);

방법2) MAX(칼럼) + 1 을 이용한 채번 방법

  INSERT INTO 주문(주문일련번호, COL2, COL3... )
  SELECT DECODE(MAX(주문일련번호), NULL, 0, MAX(주문일련번호))+1 주문일련번호, :COL2값...
  FROM 주문

  INSERT INTO 주문(주문일련번호, COL2, COL3... )
  SELECT DECODE(MAX(주문일련번호), NULL, 0, MAX(주문일련번호))+1 주문일련번호, :COL2값...
  FROM 주문
  WHERE 지점코드 ='XX'
  AND   주문일자 ='2002-01-01 
  -- WHERE 절을 통해서 충분하게 값을 걸러주어 MAX 부하를 줄인다.

방법3) 시퀀스 테이블을 이용한 채번 방법

  CREATE SEQUECNE 주문_SEQ
  INCREMENT BY 1 ;
  INSERT INTO 주문( 주문일련번호, COL2, COL3 ... )
  VALUES ( 주문_SEQ.NEXTVAL, 'XXX','XXX'...);

방법4) 채번 테이블을 이용한 채번 방법

• 세 가지 SQL은 트랜잭션 하나로 처리 되어야 정확한 데이터 관리가 가능하다.

1) 신규 일련번호 채번 (  FOR UPDATE ~ )

  SELECT 최종일련번호 + 1
  INTO   :V_주문일련번호
  FROM 주문
  WHERE 지점코드 ='01'
  AND   주문구분 ='인터넷';

  
2) 본 테이블에 데이터 입력
  INSERT INTO 주문(주문일련번호, COL2, COL3... )
  VALUES (V_주문일련번호, 'XXX','XXX'...);

  
3) 채번 테이블 수정
  UPDATE 주문채번테이블
  SET 최종일련번호 = :V_주문일련번호
  WHERE 지점코드 = '01'
  AND   주문구분 = '인터넷'

방법4) 인덱스 역방향 정렬을 이용한 채번 방법

• 가장 좋은 방법

 INSERT INTO 주문(주문일련번호, COL2, COL3... )
 SELECT /*+ INDEX_DESC(B i_01) */ <-- 인덱스 i_01 일때 인덱스를 역정렬함
        DECODE(MAX(주문일련번호), NULL, 1, 주문일련번호+1), 'XXXX','XXX'...
 FROM 주문 A,
       ( SELECT 0 SER FROM DUAL ) B
 WHERE A.주문일련번호 = B.SER(+) <-- 데이타가 하나도 없을 때 채번하기 위함
 AND   ROWNUM = 1 ;

갑작스런 대용량 테이블의 성능 저하 원인과 조치 방법

주로 인덱스에서 문제가 발생한 경우다.

• 생성된 인덱스가 깨진 경우
• 인덱스 레벨이 깊어진 경우
• 인덱스 구조에 수정/삭제가 자주 발생한 결과 불필요한 인덱스로 인해
  성능이 저하되는 경우

1) 인덱스에 대해 ANALYZE 를 수행

               
   sys@ARTDOM>analyze index scott.BIG_INDEX estimate statistics ;
   Index analyzed.
   sys@ARTDOM>alter session set db_file_multiblock_read_count=256;
   Session altered.
   sys@ARTDOM>alter session set sort_area_retained_size=100000000;
   Session altered.
   sys@ARTDOM>alter session set sort_area_size=100000000;
   Session altered.
   sys@ARTDOM>analyze index scott.BIG_INDEX estimate statistics ;
   Index analyzed.

2)인덱스 레벨을 검사

• VLDB: 적정수준 100만 ROW - 3 LEVEL

   sys@ARTDOM>select blevel, leaf_blocks from dba_indexes where index_name ='BIG_INDEX';

      BLEVEL LEAF_BLOCKS
  ---------- -----------
           1         230

3) 인덱스를 대체한다.

• VLDB: 재생성한다

    sys@ARTDOM>alter index scott.big_index rebuild ;
    Index altered.