SQL의 OBJECT종류 : 시퀀스(Sequence)

SQL의 OBJECT 종류

Object	특징	사용방법	정의방법
테이블(Table)	행과 열의 조합으로 구성된 기본 저장단위	select, insert, update, delete	create table, alter table, drop table, truncate table
뷰(View)	하나 또는 그 이상의 base테이블로부터 논리적으로 데이터를 추출한 부분집합으로 가상의 논리적인 테이블	select, insert, update, delete	create [or replace] view, drop view
시퀀스(Sequence)	자동으로 고유한 숫자값을 생성해주며 주로 기본키 값을 생성하기 위해 사용	시퀀스명.nextval, 시퀀스명.currval	create sequence, alter sequence, drop sequence
인덱스(Index)	쿼리(Query) 속도를 향상시키기 위해 사용	X	create index, alter index, drop index
시노님(Synonym)	object에 대한 또다른 이름의 alias역할	X	create synonym, drop synonym

시퀀스 생성(create sequence)

• 자동으로 고유한 번호를 반환해 주는 번호생성기와 같은 오브젝트.
• 주로 PK제약조건과 함께 사용한다.
• 주로 insert와 update사용한다.
• 옵션여러개 기입시 콤마(,)필요없다! 절대 찍지말것!

시퀀시 생성시 사용가능한 옵션

1. increment by 숫자 : 시퀀스번호사이에 간격을 지정하는 옵션.

   • default값은 1이다. 생략하면 1씩 증가하는 값을 반환해주는 시퀀스가 생성된다.
   • 예시 : increment by 5 : 5씩 증가하는 값을 반환해주는 시퀀스를 만든다는 의미.
   • 예시 : increment by -1 : 1씩 감소하는 값을 반환해주는 시퀀스를 만든다는 의미.

2. start with 숫자 : 시퀀스번호의 시작값을 지정하는 옵션.

   • default값은 1이다.
   • 예시 : start with 100 : 100부터 시작하는 시퀀스를 만든다는 의미.

3. maxvalue와 minvalue :시퀀스번호의 최댓값, 최소값을 지정하는 옵션.

   • default값은 nomaxvalue와 nominvalue이다. 즉 시스템에 정의되어있는 최대값과 최소값을 쓰겠다는 의미.
   • 시스템 최대값 : 10의 27승
   • 시스템 최소값 : 10의 -26승

4. cycle

   • 사용하고자하는 컬럼이 PK나 UQ제약조건이 있는 경우 cycle 옵션을 건 경우 중복값이 발생해 에러가 발생할 수 있다.
   • 디폴트값은 nocycle이다.
   • maxvalue나 minvalue를 끝으로 순환한다 즉 다시 처음부터 시작한다.
   • 예시 : maxvalue가 100이면 100값이 지난뒤 다시 1부터 시작

1. cache 숫자
   • 원하는 숫자만큼 미리 만들어 Shared Pool의 Library Cache에 상주시킴.
   • 메모리에 시퀀스 값을 미리 할당 for 성능향상.
   • 디폴트값은 cache 20이다.
   • cache를 사용하고싶지않다면 nocache를 입력해야한다.
   • CACHE는 시스템 번호를 생성하는 데 필요한 디스크 IO 수를 최소화하여 시퀀스 개체를 사용하는 응용 프로그램의 성능을 향상시킵니다. 전원 오류와 같은 예기치 않은 종료로 인해 캐시에 남아 있는 시퀀스 번호가 손실될 수 있습니다. 값을 1로 설정하는 경우 캐쉬가 없이 스퀀스가 실행되므로 디스크 IO가 매번 일어 납니다.
   • 예시 : cache 5인경우, 시퀀스가 호출될때 미리 만들어진 캐시메모리를 빨리 전달해줌.

//기본 => 모든 옵션생략시 6개의 옵션의 디폴트값으로 적용됨
create sequence 시퀀스명

//기본+옵션
create sequence 시퀀스명
	[increment by n]
	[start with n]
	[maxvalue n 또는 nomaxvalue]
	[minvalue n 또는 nominvalue]
	[cycle 또는 nocycle]
	[cache n 또는 nocache];
	
//예시 (옵션여러개 기입시 콤마필요없다! 절대 찍지말것)
create sequence dept_id_seq
	increment by 10
	start with 350
	maxvalue 1000;

시퀀스 사용(nextval와 currval)

• 시퀀스사용하는 방법은 두가지이다 : nextval와 currval
• 컬럼과 데이터 삽입시 시퀀스 활용하면 용이하다.
• 여러 테이블에 사용가능하다. 테이블 바뀐다고해서 시퀀스가 처음값으로 반환되는 건 아니다.
• 시퀀스는 insert, update, select구문을 날릴때마다 시퀀스 값이 올라간다
  • 따라서 의미없는 시퀀스 사용을 자제해야한다
  • 그렇다면 시퀀스를 쓰지않고 마지막 시퀀스 사용값을 어떻게 확인 할 수 있을까?
  • nextval대신 currval을 쓰면 된다 = > select dept_id_seq.currval from dual;
  • WHY? 데이터딕셔너리에 값이 남기때문에!
• 예시 : A테이블에서 기본 시퀀스를 쓰며 컬럼을 INSERT 5번(마지막 시퀀스 5)했다. 그리고 테이블을 바꿔서 B테이블에서 INSERT를 1번했다 그렇다면 B테이블의 첫번째컬럼 시퀀스는 몇번일까?
  • 정답은 6이다. A테이블에서 마지막 시퀀스 5였으므로 B테이블의 첫번째컬럼은 시퀀스 6이다.

//기본 두가지
시퀀스명.nextval
시퀀스명.currval

//예시 : nextval
insert into departments
values(dept_id_seq.nextval, 'XXX', 108, 1700);

select * from departments;

//예시 : currval
select dept_id_seq.currval
from dual;

시퀀스사용시 갭발생하는 이유

• 주로 rollback때문에 발생한다.
• rollback때문에 gab이 발생할 수 있다.
• rollback은 테이블과 관련되어있는 DML을 취소시켜주는 것이지 시퀀스를 취소시켜주는 것은 아니다.
• 시퀀스를 돌이킬수는 없다. 따라서 갭이 발생했다면 수동으로 데이터를 처리(insert, update등)하면 된다.
• 예시 : A테이블에서 기본 시퀀스를 쓰며 컬럼을 INSERT 5번(마지막 시퀀스 5)한 뒤 commit을 했다. 그리고 테이블을 바꿔서 B테이블에서 INSERT를 1번했다가 B테이블의 첫번째컬럼 시퀀스는 몇번일까?
  • 정답은 6이다. A테이블에서 마지막 시퀀스 5였으므로 B테이블의 첫번째컬럼은 시퀀스 6이다.

시퀀스 수정(alter)

• 변경가능 : 시퀀스간격, 최대값, 최소값, cycle옵션, cache옵션
• 변경불가능 : 시작값 (start with n)
  • 시작값(start with n)을 변경하고 싶다면 시퀀스 삭제하고 새로 생성하면 된다.

//기본
alter sequence 시퀀스명
	[increment by n]
	[maxvalue n 또는 nomaxvalue]
	[minvalue n 또는 nominvalue]
	[cycle 또는 nocycle]
	[cache n 또는 nocache];

//예시
alter sequence dept_deptid_seq
	increment by 20	
	maxvalue n 9999
	nocycle	
	nocache;

시퀀스 삭제(drop)

//기본
drop sequence 시퀀스명;

//예시
drop sequence dept_deptid_seq;