OracleSQL 핵심정리9 - 시퀀스와 인덱스

SEQUENCE

시퀀스 개념 이해와 시퀀스 생성

• 테이블 내의 유일한 숫자를 자동으로 생성하는 자동 번호 발생기
  • ex) CREATE SEQUENCE 시퀀스명 [ 시퀀스종류 ];
• 시퀀스종류
  • START WITH : 시퀀스 번호의 시작 값을 지정할 때 사용 [START WITH N]
  • INCREMENT BY : 연속적인 시퀀스 번호의 증가치를 지정할 때 사용 [INCREMENT BY N]
  • MAXVALUE N | NOMAXVALUE : MAXVALUE는 시퀀스가 가질 수 있는 최대값을 지정. NOMAXVALUE 는 10^27 또는 -1 로 설정
  • MINVALUE N | NOMINVALUE : MINVALUE는 시퀀스가 가질 수 있는 최소값을 지정. NOMINVALUE는 1 또는 10^26 로 설정
  • CYCLE | NOCYCLE : CYCLE은 시퀀스 값이 최대값까지 증가하면 다시 START WITH 옵션에 지정한 시작 값에서 시퀀스를 다시 시작하도록 함. NOCYCLE은 증가 완료시 에러 유발
  • CACHE | NOCACHE : CACHE는 메모리상의 시퀀스 값을 관리하도록 하는 것이며 기본값은 20. NOCACHE는 원칙적으로 메모리상에서 시퀀스를 관리하지 않음
• 시퀀스의 현재 값 CURRVAL, 다음 값 NEXTVAL 을 사용하여 알 수 있음
  • ex) 시퀀스명.CURRVAL; - 현재 시퀀스 값 출력, 시퀀스명.NEXTVAL; - 다음 시퀀스 값 출력(증가)

시퀀스의 사용

• 시퀀스는 거의 INSERT 연산과 같이 사용되어 컬럼 값을 자동으로 발생시키는 용도로 사용
• 시퀀스는 유일한 값을 생성해주고 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성할 수 있음
• 시퀀스명.NEXTVAL을 기본 키와 같은 컬럼 VALUE에 지정하여 사용

시퀀스 제거 및 수정

• DROP SEQUENCE 시퀀스명; - 시퀀스 제거
• ALTER SEQUENCE 시퀀스명 시퀀스종류; - 시퀀스 수정

---

INDEX

인덱스의 개요

• 빠른 검색을 위해 사용하며 시스템에 걸리는 부하를 줄여 전체 성능을 향상시킴(B-Tree 구조)
• 인덱스를 위한 추가적인 공간이 필요하며 생성하는데 시간이 걸림. 데이터의 변경이 자주 일어날 경우에는 오히려 성능이 저하
• 인덱스를 생성하지 않아도 기본키나 유일키에 대해서 자동으로 인덱스를 생성
• 서브쿼리문으로 복사한 테이블은 제약조건도 사라지므로 인덱스가 없음
• CREATE INDEX 인덱스명 ON 테이블명 (컬럼명); - 인덱스 생성
• DROP INDEX 인덱스명; - 인덱스 제거

인덱스의 사용

• 테이블 행의 수가 많고 WHERE문에 해당 컬럼이 많이 사용되며 검색 결과가 전체 데이터의 2~4% 정도일 때 자주 사용
• NULL을 포함하는 컬럼이 많은 경우에도 자주 사용
• 검색결과의 양이 10% 가 넘거나 DML이 자주일어날 때는 사용하지 말아야 함
• 인덱스를 제거해도 인덱스 엔트리는 남아서 필요없는 공간을 차지하기 때문에 재생성 시켜야 함
• ALTER INDEX 인덱스명 REBUILD - 인덱스 재생성

인덱스 종류

• 고유 인덱스, 비고유 인덱스, 단일 인덱스, 결합 인덱스, 함수 기반 인덱스
• 고유인덱스 : 기본키나 유일키처럼 유일한 값을 갖는 컬럼에 대해서 생성하는 인덱스 <-> 비고유 인덱스
  • ex) CREATE UNIQUE INDEX 인덱스명 ON 테이블명(컬럼명);
  • 비고유 인덱스에서 UNIQUE 속성을 추가하여 고유인덱스로 지정
• 결합인덱스 : 두 개 이상의 컬럼으로 인덱스를 구성하는 것
  • ex) CREATE INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...);
  • 두 개 이상의 컬럼명에 동일인덱스를 부여하여 결합인덱스 지정
• 함수 기반 인덱스 : 수식으로 검색하는 경우가 많다면 컬럼에 수식을 적용한 채로 인덱스를 생성
  • ex) CREATE INDEX 인덱스명 ON 테이블명 (수식컬럼);