Neo-Oracle Home

#########################################################################################################################
시퀀스 생성
#########################################################################################################################
문법
CREATE SEQUENCE sequence_name
 [INCREMENT BY n]
 [START WITH n]
 [MAXVALUE n | NOMAXVALUE]
 [MINVALUE n | NOMINVALUE]
 [CYCLE | NOCYCLE]
 [CACHE n | NOCACHE];

INCREMENT BY n: 증가값으로 2라고 하면 초기값에서 2만큼씩 증가한다. 음수를 쓸 수도 있으며 -1로 하면 1씩 감소한다. 기본값 1
START WITH n: 초기값을 의미한다.
MAXVALUE n: 최대값
NOMAXVALUE: 최대 10의 27승만큼 할당 가능하다.(거의 무제한)
MINVALUE n: 최소값. START WITH n의 우선순위가 더 높으며 만약 START WITH n 값이 MINVALUE n 값보다 낮다면 시퀀스 생성이 안된다.
            ORA-04006: START WITH cannot be less than MINVALUE <-- 이 에러가 난다.
NOMINVALUE: 최소값이며 -10의 26승까지 가능하다. 단, 시퀀스 생성시 start with n을 설정하지 않는다고 nominvalue의 최소값인
            -10의 26승이 사용되는 건 아니다. 에러난다.(테스해봄)
CYCLE | NOCYCLE: MAXVALUE가 설정된 상황에서 최대값에 도달할 경우 다시 START WITH n 값으로 순환할지 말지 결정한다.
                 기본값은 NOCYCLE
CACHE n| NOCACHE: CACHE 옵션 사용시 n개만큼 미리 메모리에 생성시켜 놓고 해당 시퀀스를 호줄하게 되면 메모리에서 할당하게 된다.
    기본값은 CACHE 20이다.(11g, 10g 다 테스트해봄)

예제)
SQL> create sequence t_seq2
  2  increment by 1
  3  start with 100
  4  nomaxvalue
  5  minvalue 50
  6  ;

Sequence created.

SQL> select t_seq2.nextval from dual;

   NEXTVAL
----------
       100

SQL> create sequence t_seq
  2  increment by 1
  3  start with 50
  4  nomaxvalue
  5  minvalue 100;
create sequence t_seq
*
ERROR at line 1:
ORA-04006: START WITH cannot be less than MINVALUE

기본값으로 생성 예제)
create sequence t_seq4 start with 1;

확인해보면 아래처럼 cache_size는 20, maxvalue는 nomaxvalue로, minvalue는 start with 값을 따라가고, increment by 는 1이 되었다.
그리고 nocycle이다.

SQL> SELECT * FROM DBA_SEQUENCES WHERE SEQUENCE_NAME like 'T_SEQ%';

SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE  MAX_VALUE INCREMENT_BY  C O CACHE_SIZE LAST_NUMBER
-------------- ------------- ---------- ---------- ------------ - - ---------- -----------
SYS            T_SEQ4        1          1.0000E+27 1            N N         20          21

#########################################################################################################################
시퀀스 변경
#########################################################################################################################
문법
ALTER SEQUENCE sequence_name
 [INCREMENT BY n]
 [MAXVALUE n | NOMAXVALUE]
 [MINVALUE n | NOMINVALUE]
 [CYCLE | NOCYCLE]
 [CACHE n | NOCACHE];

create랑 비슷하지만 start with n 이 없다. 즉 start with는 변경 불가능하다.

예제
alter sequence t_seq
 increment by 2
 maxvalue 20000
 cache 10
 nocycle;

#########################################################################################################################
시퀀스 삭제
#########################################################################################################################
DROP SEQUENCE sequence_name;

#########################################################################################################################
시퀀스 정보 확인
#########################################################################################################################
select * from dba_sequnces;

이중에서 몇개만 설명한다.

CYCLE_FLAG: cycle nocycle 여부이다.
ORDER_FLAG: 시퀀스 증가시 오름차순인지 내림차순인지 여부...인데 increment by -1로 바꿔도 변경되지 않음을 확인했다. 좀 더 확인 필요.
LAST_NUMBER: 디스크에 내려써진 마지막 시퀀스 넘버. 만약 캐쉬를 사용한다면 이 값은 CURRVAL + CACHE_SIZE 일 것이다.
#########################################################################################################################
시퀀스 사용
#########################################################################################################################
NEXTVAL과 CURRVAL이 존재한다.

NEXTVAL: 다음 번호
CURRVAL: 현재 번호

주의할 점은 NEXTVAL을 조회할 때마다 INCREMENT BY n 만큼 바로 증가한다는 점이다.
또한 시퀀스 생성 후 맨 처음에는 CURRVAL을 조회할 수 없다. 한번은 NEXTVAL을 조회해야 CURRVAL 조회가 가능하다.

NEXTVAL과 CURVAL을 사용할 수 있는 조건
- 서브쿼리가 아닌 SELECT 문의 SELECT LIST에 사용
- INSERT절에 있는 서브쿼리의 SELECT LIST에 사용
- INSERT절의 VALUES 절에 사용
- UPDATE절의 SET절에 사용

사용할 수 없는 조건
- 뷰의 SELECT LIST
- DISTINCT가 있는 SELECT문
- GROUP BY 또는 ORDER BY 가 있는 SELECT문
- 서브쿼리

사용예시
SELECT 시퀀스이름.CURRVAL FROM DUAL;
SELECT 시퀀스이름.NEXTVAL FROM DUAL;
#########################################################################################################################
시퀀스 실무
#########################################################################################################################
스퀀스 공백이 발생하는 경우가 흔하다. 이유는 아래와 같다
- 시퀀스를 호출한 후 해당 번호를 사용하지 않았을 경우
- DB 인스턴스가 비정상 종료되었을 경우

예를 들어 시퀀스 호출 후 커밋하지 않고 롤백한다면 해당 번호는 영원히 사용하지 못하게 된다.
CACHE 옵션을 10으로 설정했다면 메모리에 항상 10개의 시퀀스 번호를 할당해 놓는데 DB 인스턴스가 비정상 종료된다면 해당 10개의
번호는 다시 할당받을 수 없다.

따라서 번호 공백이 해당 업무에 영향을 미칠 수 있다면 시퀀스 생성시 nochache 옵션으로 설정해야 하며, 하나의 번호도 공백이
없어야 한다면 시퀀스 사용을 자제하고 인덱스나 번호 할당 테이블 등을 이요하는 것도 고려해야 한다.

* 인덱스를 사용한 부서별 번호 할당
부서별로 사원번호를 할당해야 한다고 하자. 시퀀스를 이용한다면 부서 개수만큼 시퀀스가 존재해야만 한다. 해당 회사에 50개의
부서가 있다면 시퀀스는 50개가 생성되어 부서별로 서로 다른 시퀀스를 이용해야만 할 것이다.
이 경우 다음과 시퀀스 관리 어려움 등의 문제가 발생할 수 있다.

부서번호+번호로 인덱스를 생성한다면 다음과 같이 SQL을 수행함으로써 각 부서별 다음에 할당될 번호를 추출할 수 있다.

select /*+ index_desc(사원,사원 인덱스) */
 nvl(max(번호),0)+1
 from 사원 where 부서번호='10' and rownum=1;

#########################################################################################################################
시퀀스 공백 테스트

#########################################################################################################################

SQL> @1

SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            T_SEQ4                                  1 1.0000E+28            1 N N         20           1
SYS                            T_SEQ2                                  1 1.0000E+28           -1 N N         20          79
SYS                            T_SEQ3                                  1 1.0000E+28            1 N N          0           1

SQL> select t_seq2.currval from dual;

   CURRVAL
----------
        97

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  409194496 bytes
Fixed Size                  2228864 bytes
Variable Size             385879424 bytes
Database Buffers           16777216 bytes
Redo Buffers                4308992 bytes
Database mounted.
Database opened.
SQL> @1

SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            T_SEQ4                                  1 1.0000E+28            1 N N         20           1
SYS                            T_SEQ2                                  1 1.0000E+28           -1 N N         20          79
SYS                            T_SEQ3                                  1 1.0000E+28            1 N N          0           1

SQL> select t_seq2.currval from dual;
select t_seq2.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence T_SEQ2.CURRVAL is not yet defined in this session

SQL> select t_seq2.nextval from dual;
   NEXTVAL
----------
        79

SQL> @1
SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            T_SEQ4                                  1 1.0000E+28            1 N N         20           1
SYS                            T_SEQ2                                  1 1.0000E+28           -1 N N         20          59
SYS                            T_SEQ3                                  1 1.0000E+28            1 N N          0           1

97~80 의 공백이 생겼다.