[Oracle(SQL)] SEQUENCE, INDEX

SEQUENCE(시퀀스)
- 자동 번호 발생기 역할을 하는 객체
- 순차적으로 정수값을 자동으로 생성해줌

 

  CREATE SEQUENCE 시퀀스이름
  [INCREMENT BY 숫자] -- 다음 값에 대한 증가치, 생략하면 자동 1 기본
  [START WITH 숫자] -- 처음 발생시킬 값 지정, 생략하면 자동 1 기본
  [MAXVALUE 숫자 | NOMAXVALUE] -- 발생시킬 최대값 지정(10의 27승)
  [MINVALUE 숫자 | NOMINVALUE] -- 최소값 지정(-10의 26승)
  [CYCLE | NOCYCLE] -- 값 순환 여부
  CACHE 바이트크기 | NOCACHE] -- 캐쉬메모리 기본값은 20바이트, 최소는 2바이트

 

CREATE SEQUENCE SEQ_EMPID
START WITH 300
INCREMENT BY 5
MAXVALUE 310
NOCYCLE
NOCACHE;

 

- 시퀀스.CURRVAL : 해당 시퀀스가 가지고 있는 CURRENT VALUE(현재 값)
- 시퀀스.NEXTVAL : 해당 시퀀스가 가질 다음 값 리턴
- NEXTVAL를 1회 수행해야 CURRVAL를 알아올 수 있다

SELECT SEQ_EMPID.CURRVAL FROM DUAL;

SELECT SEQ_EMPID.NEXTVAL FROM DUAL; --300
SELECT SEQ_EMPID.CURRVAL FROM DUAL; --300
SELECT SEQ_EMPID.NEXTVAL FROM DUAL; --305
SELECT SEQ_EMPID.CURRVAL FROM DUAL; --305
SELECT SEQ_EMPID.NEXTVAL FROM DUAL; --310
SELECT SEQ_EMPID.NEXTVAL FROM DUAL; --MAXVALUE를 넘어서면 에러발생

 

- 데이터 딕셔너리를 통해 만들어진 SEQUENCE 조회

SELECT * FROM USER_SEQUENCES;

- SEQ_EID는 밑에서 나올 예제

 

- 시퀀스 변경

ALTER SEQUENCE SEQ_EMPID
INCREMENT BY 10
MAXVALUE 400
NOCYCLE
NOCACHE;

 

- START WITH 값은 변경이 불가능하므로 START WITH 값을 변경하려면 DROP으로 삭제 후 다시 생성해야 함
- SELECT문에서 사용 가능
- INSERT문에서 SELECT 구문 사용 가능
- INSERT문에서 VALUES 절에서 사용 가능
- UPDATE문에서 SET절에서 사용 가능

- 단, 서브쿼리의 문에서 사용 불가
- VIEW의 SELECT절에서 사용 불가
- DISTINCT 키워드가 있는 SELECT문에서 사용 불가
- GROUP BY, HAVING절이 있는 SELECT문에서 사용 불가
- ORDER BY절에서 사용 불가
- CREATE TABLE, ALTER TABLE의 DEFAULT값으로 사용 불가

---

INDEX(인덱스)

- SQL명령문의 검색 처리 속도를 향상시키기 위해 컬럼에 대해서 생성하는 오라클 객체
- 하드디스크의 어느 위치인지에 대한 정보를 가진 주소록
- DATA - ROWID로 구성
- ROWID 구조 : 오브젝트 번호, 상대파일 번호, 블록 번호, 데이터 번호

SELECT
       ROWID
     , E.EMP_ID
     , E.EMP_NAME
  FROM EMPLOYEE E;

 

- 인덱스의 내부구조는 이진트리 형식으로 구성되어 있고 인덱스를 생성하기 위해서는 시간이 필요함
- 또한 인덱스를 위한 추가 저장 공간이 필요하기 때문에 반드시 좋은 것은 아니다.
=> 인덱스가 생성된 컬럼에서 DML 작업이 빈번한 경우 처리 속도가 느려진다.
- 따라서 일반적으로 데이블 전체 로우의 15% 이하의 데이터를 조회할 때 인덱스를 생성한다.

 

장점
- 검색 속도가 빨라짐
- 시스템에 걸리는 부하를 줄여서 시스템 전체의 성능을 향상시킴

단점
- 인덱스를 위한 추가 저장 공간이 필요함
- 인덱스를 생성하는데 시간이 걸림
- 데이터의 변경작업(INSERT/UPDATE/DELETE)이 자주 일어나는 경우
- REBUILD 작업을 주기적으로 해주어야 하고, REBUILD를 자주 해주지 않으면
- 성능이 오히려 저하된다.

 

인덱스를 관리하는 데이터 딕셔너리
- PK, UNIQUE 제약 조건이 있으면 자동으로 INDEX 객체가 생성 된다.

SELECT
       UIC.*
  FROM USER_IND_COLUMNS UIC;

 

인덱스 종류

- 1. 고유 인덱스(UNIQUE INDEX)
- 2. 비고유 인덱스(NONUNIQUE INDEX)
- 3. 단일 인덱스(SINGLE INDEX)
- 4. 결합 인덱스(CONPOSITE INDEX)
- 5. 함수기반 인덱스(FUNCTION BASED INDEX)

 

1. UNIQUE INDEX

- UNIQUE INDEX로 생성된 컬럼에는 중복값이 포함될 수 없음
- 오라클 PRIMARY KEY, UNIQUE 제약조건을 생성하면 자동으로 해당 컬럼에 UNIQUE INDEX가 생성됨
- 해당 컬럼으로는 ACCESS 하는 경우 성능 향상의 효과가 있음

 

인덱스 힌트
- 일반적으로는 옵티마이저가 적절한 인덱스를 타거나 풀 스캐닝을 해서 비용이 적게 드는 효율적인 방식으로 검색함
- 하지만 우리가 원하는 테이블에 있는 인덱스를 사용할 수 있도록 해주는 구문(힌트)를 통해 선택 가능
- SELECT절 첫 줄에 힌트 주석(/*_ 내용 */)을 작성하여 적절한 인덱스를 부여할 수 있다.

SELECT /*+ INDEX(E 엔터티1_PK)*/
       E.*
  FROM EMPLOYEE E;

 

 

- 인덱스가 내림차순으로 생성 되어서 인덱스 영역에서 역방향으로 스캔하라는 뜻
- 예전에 넣었던 데이터부터 순서대로 나오도록 정렬

SELECT /*+ INDEX_DESC(E 엔터티1_PK)*/
       E.*
  FROM EMPLOYEE E;

 

UNIQUE 제약 조건에 의해 이미 인덱스가 존재하여 생설할 수 없음

CREATE UNIQUE INDEX IDX_EMPNO
ON EMPLOYEE(EMP_NO);

 

PK나 UNIQUE 제약 조건으로 인해 생성된 인덱스는 DROP할 수 없음(EMP_NO 제약조건)

DROP INDEX SYS_C008489;

중복 값이 있는 컬럼은 UNIQUE 인덱스 생성하지 못함

CREATE UNIQUE INDEX IDX_DEPTCODE
ON EMPLOYEE(DEPT_CODE);

 

2. NONUNIQUE INDEX

- WHERE절에서 빈번하게 사용되는 일반 컬럼을 대상으로 생성
- 주로 성능 향상을 위한 목적으로 생성함

(위의 UNIQUE INDEX에선 생성이 안됐는데 NONUNIQUE INDEX로는 생성 가능)

CREATE INDEX IDX_DEPTCODE
ON EMPLOYEE(DEPT_CODE);

 

3. 결합 인덱스(COMPOSITE INDEX)
- 결합 인덱스는 중복 값이 낮은 값이 먼저 오는 것이 검색 속도를 향상시킨다.

CREATE INDEX IDX_DEPT
ON DEPARTMENT(DEPT_ID, DEPT_TITLE);

 

0보다 크단 것은 모든걸 포함시킬 수 있는 조건

SELECT /*+ INDEX_DESC(D IDX_DEPT)*/
       D.DEPT_ID
  FROM DEPARTMENT D
 WHERE D.DEPT_TITLE > '0'
   AND D.DEPT_ID > '0';

 

4. 함수 기반 인덱스
- SELECT절이나 WHERE절에서 산술 계산식이나 함수가 사용된 경우
- 계산에 포함된 컬럼은 인덱스의 적용을 받지 않는다
- 계산식으로 검색하는 경우가 많다면, 수식이나 함수식으로 이루어진 컬럼을 인덱스로 만들 수도 있다.

CREATE INDEX IDX_EMP_SALCALC
ON EMPLOYEE((SALARY + (SALARY * NVL(BONUS, 0))) * 12);

 

SELECT /*+ INDEX_DESC(E IDX_EMP_SALCALC)*/
       E.EMP_ID
     , E.EMP_NAME
     , ((E.SALARY + (E.SALARY * NVL(E.BONUS, 0))) * 12) 연봉
  FROM EMPLOYEE E
 WHERE ((E.SALARY + (E.SALARY * NVL(E.BONUS, 0))) * 12) > 10000000;