오라클 SQL - 인덱스, 시퀀스 ( CREATE,ALTER,DROP)

인덱스 (INDEX)

 

인덱스(INDEX) 는 사전의 순차적으로 정의되어 있는 색인을 의미한다. 그래서 많은 데이터가 있을때 색인을 통해 데이터를 빠르게 검색하기 위해 사용한다. 데이터가 많지 않을경우엔 인덱스는 크게 의미는 없다. 

 

1. PK 인덱스 생성. (중복 데이터가 없을때 사용.)

CREATE UNIQUE INDEX PK_TEST_EMP ON TEST_EMP(EMP_NO); 

방식: CREATE UNIQUE INDEX 인덱스명 ON 테이블(컬럼명);

※ 테이블 생성시 PK 를 지정하면 자동으로 PK 인덱스 생성.

 

2. 결합인덱스 생성. 

CREATE INDEX IX1_TEST_EMP ON TEST_EMP(EMP_ID, DEPT_NO); 

CREATE INDEX 인덱스명 ON 테이블(컬럼명,컬럼명,...);

 

3. 해당 테이블에 INDEX 조회

--컬럼단위

SELECT * FROM USER_IND_COLUMNS 
WHERE TABLE_NAME = 'TEST_EMP';

 

--INDEX 단위

SELECT * FROM USER_INDEXES 
WHERE TABLE_NAME = 'TEST_EMP';

 

4. 인덱스 삭제

DROP INDEX IX1_TEST_EMP;

 

5. 함수 인덱스

CREATE INDEX IX2_TEST_EMP ON TEST_EMP(LENGTH(NAME)); 

LENGTH(NAME) 컬럼이 아닌 해당 방식으로도 인덱스를 만들수 있다.

 

시퀀스 (SEQUENCE)

 

오라클에서 시퀀스는 자동으로 값을 증가시켜 줄 때 사용한다. 게시글 번호가 PK 인경우 게시글번호를  MAX 로 조회해서 INSERT 를 한다면,  사용자가 동일한 시점에 INSERT 를 하게될때, 무결성 에러가 발생될수 있어서 시퀀스를 많이 사용한다.

 

1. 시퀀스 생성

CREATE SEQUENCE 시퀀스명 (필수)

INCREMENT BY 증가값 (생략가능 기본값1)

START WITH 시작값 (생략가능 기본값1))

MAXVALUE 최대값(생략가능)

MINVALUE 최소값(생략가능)

CYCLE 최대값에 도달했을경우 다시 시작값으로 돌아갈지 여부 CYCLE/NOCYCLE 선택

CACHE 시퀀스를 메모리에 할당할수 지정 CACHE/NOCACHE(선택 기본값 20)

 

예문1 - 기본옵션으로 생성도 가능하다.

CREATE SEQUENCE SEQ_TEST_EMP;

 

예문2 - 옵션 지정 SEQ 생성

CREATE SEQUENCE SEQ_TEST_EMP

INCREMENT BY 1

START WITH 0

MINVALUE 0

MAXVALUE 1000

NOCYCLE

NOCACHE;

 

2. 시퀀스 수정 - 시작명령어 CREATE 를 ALTER 로 변경하면 수정이된다.

  - 증가값2, CYCLE, 메모리할당 30으로 수정 

  - START WITH 은 사용할수 없다.

 

ALTER SEQUENCE SEQ_TEST_EMP

INCREMENT BY 2

CYCLE

CACHE 30;

 

3. 시퀀스 삭제

DROP SEQUENCE SEQ_TEST_EMP;

 

4. 시퀀스 조회

SELECT SEQ_TEST_EMP.CURRVAL FROM DUAL; // 현재값 조회

SELECT SEQ_TEST_EMP.NEXTVAL FROM DUAL;  //  다음 시퀀스

 

※조회한 시퀀스로 INSERT문

INSERT INTO TEST_EMP (EMP_NO, EMP_ID, NAME, DEPT_NO) VALUES (SEQ_TEST_EMP.NEXTVAL, 'first','첫번째사원',1);

 

5. 시퀀스 초기화

ALTER SEQUENCE SEQ_TEST_EMP RESTART;