#48 [SQL] SEQUENCE, INDEX

20_Sequence_Index

Sequence(시퀀스) : 호출(실행)할때마다 자동적으로 숫자를 증가시키는 것(객체)

 

* 용도

기본키(PK:유일 + not null) 용도로 사용 : 시퀀스는 숫자가 증가하므로 중복발생없음, null 아님

 

* 활용

예) 자유게시판 : No(1 ~ n), 고객 : 고객No(1 ~ n) => ex) 1씩 증가, 2씩 증가

 

* 용어

채번 : 자동적으로 1씩 증가시키는 값 (1 ~ n : 시퀀스대상)

---

예제 1) SAMPLE_SEQ 시퀀스를 1부터 시작해서 10씩 증가시키는 시퀀스 생성

 

[사용법] : 다시 감소할 수 없음 -> 되돌아가려면 시퀀스 삭제 후 재생성

CREATE SEQUENCE 시퀀스명
INCREMENT BY 증가값
START WITH 시작값
MINVALUE 최소값 (생략가능)
MAXVALUE 최대값 (생략가능) ;

CREATE SEQUENCE SAMPLE_SEQ
INCREMENT BY 10
START WITH 1;

[사용법]

시퀀스명.NEXTVAL : 시퀀스가 증가함 (NEXTVAL가 있는 문장을 실행할때마다 증가)

-- 실행 방법)
SELECT SAMPLE_SEQ.NEXTVAL FROM DUAL;

[사용법]

시퀀스명.CURRVAL : (증가된) 현재 시퀀스 값 보기

-- 현재 시퀀스 값 보기
SELECT SAMPLE_SEQ.CURRVAL FROM DUAL;

 

연습 1) SAMPLE_SEQ2 시퀀스를 1부터 시작해서 1씩 증가시키는 시퀀스 생성

CREATE SEQUENCE SAMPLE_SEQ2
INCREMENT BY 1
START WITH 1;

-- 실행
SELECT SAMPLE_SEQ2.NEXTVAL FROM DUAL;

-- 현재 시퀀스 값 보기
SELECT SAMPLE_SEQ2.CURRVAL FROM DUAL;

---

(참고) 시스템 테이블(딕셔너리 뷰 = 데이터 사전) : USER_SEQUENCES (DBA(관리자)가 주로 사용)

 

SELECT * FROM USER_SEQUENCES;

---

예제 2) 테이블에 INSERT 할 때 시퀀스 사용하기 
실습테이블 : 부서(DEPARTMENT) -> DEPT_TEMP

 

[사용법]

INSERT INTO 테이블명(컬럼명, 컬럼명2, ...)

VALUES(시퀀스명.NEXTVAL, 값, 값2, ...) : INSERT 문에 시퀀스 사용하기

-- 1. 테이블 복사
CREATE TABLE DEPT_TEMP
AS
SELECT * FROM DEPARTMENT
WHERE 1=2;

-- 2. 시퀀스 생성 : 초기값 1, 증가값 1
CREATE SEQUENCE DEPT_TEMP_SEQ
INCREMENT BY 1
START WITH 1;

-- 3. INSERT 문에 사용하기
INSERT INTO DEPT_TEMP(DNO, DNAME, LOC)
VALUES(DEPT_TEMP_SEQ.NEXTVAL, 'ACCOUNTING', 'NEW YORK');		// 여러번 누르기

-- 4. 데이터 확인
SELECT * FROM DEPT_TEMP;

-- 5. 확정
COMMIT;

---

예제 3) DEPT_TEMP_SEQ 시퀀스 삭제하기

 

[사용법]

DROP SEQUENCE 시퀀스명 : 시퀀스 삭제

DROP SEQUENCE DEPT_TEMP_SEQ;

 

---

Index(인덱스) : 조회속도 향상을 위해 컬럼에 인덱스를 생성함

 

* 사용 : 컬럼에 인덱스를 지정해서 생성

 

* 기본키(PK) : 유일(중복금지) + NOT NULL + 자동인덱스생성

 

* 인덱스를 만들어야 하는 기준

1. WHERE(조건절) 의 해당 컬럼이 많이 등장할 경우 인덱스 생성 고려

2. 테이블의 행의 개수가 굉장히 많을 경우 (데이터 건수가 많을 경우)

    ex) 10만건 이상

3. 주로 테이블 조인(2개 이상)의 공통컬럼으로 사용될 경우 인덱스 생성 고려

---

예제 4) 어떤 사이트에서 조회 시 이름검색(ENAME)이 많아 속도가 많이 느리다고 합니다. 인덱스를 생성해주세요

 

[사용법]

CREATE INDEX 인덱스명 ON 테이블명(컬럼명); : 인덱스 생성

CREATE INDEX IX_EMPLOYEE_ENAME ON EMPLOYEE(ENAME);

-- 속도 느림 대상 SQL 문 (조건절 검색) - WHERE 절에 있는 컬럼에 INDEX 만들면 속도 빨라짐
SELECT * FROM EMPLOYEE
WHERE ENAME LIKE 'SCO%'; -- 조건식 : 인덱스를 만들 대상

 

- 기본키 : 중복허용안됨 UNIQUE

- 이름 : 동명이인 있을 수 있어서 중복 허용하는 NONUNIQUE

---

(참고) 인덱스를 위한 시스템 테이블(딕셔너리 뷰 = 데이터 사전) : USER_IND_COLUMNS (DBA(관리자)가 주로 사용)

SELECT *
FROM USER_IND_COLUMNS
WHERE TABLE_NAME IN ('EMPLOYEE','DEPARTMENT');

---

예제 5) 인덱스 삭제

 

[사용법]

DROP INDEX 인덱스명;

DROP INDEX IX_EMPLOYEE_ENAME;

 

연습 2) DEPARTMENT(부서)의 DNAME(부서명)에 인덱스를 생성하세요. 인덱스명 : IX_DEPARTMENT_DNAME

CREATE INDEX IX_DEPARTMENT_DNAME ON DEPARTMENT(DNAME);

---

* 기타인덱스(특수 인덱스) (참고)

 

1. 결합 인덱스 : 컬럼 여러개를 동시에 인덱스 1개로 생성 => 결과는 같음, 속도가 빨라짐

 

[사용법]

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

예제 1) 아래의 SQL문이 자바에서 실행되는데 느리다고 합니다. 개선해주세요

SELECT * FROM DEPARTMENT
WHERE DNAME = 'SALES'
AND LOC = 'NEW YORK';

-- 결합 인덱스 생성
CREATE INDEX IX_DEPARTMENT_DNAME_LOC ON DEPARTMENT(DNAME, LOC);

 

---

2. 함수 인덱스 : SQL 함수 또는 산술식에 인덱스 걸기

 

[사용법]

CREATE INDEX 인덱스명 ON 테이블명(함수식);

예제 2) 아래의 SQL문이 자바에서 실행되는데 느리다고 합니다. 개선해주세요

SELECT * FROM EMPLOYEE
WHERE SALARY = SALARY * 12;

-- 함수 인덱스 생성
CREATE INDEX IX_EMPLOYEE_ANNSAL ON EMPLOYEE(SALARY * 12);

---

< Sequence(시퀀스) : 호출(실행)할때마다 자동적으로 숫자를 증가시키는 것(객체) >

 

1. 시퀀스 생성

CREATE SEQUENCE 시퀀스명
INCREMENT BY 증가값
START WITH 시작값

MINVALUE 최소값 (생략가능)
MAXVALUE 최대값 (생략가능) ;

 

2. 시퀀스 증가 : 시퀀스명.NEXTVAL

실행방법 : SELECT SAMPLE_SEQ.NEXTVAL FROM DUAL;

 

3. (증가된) 현재 시퀀스 값 보기 : 시퀀스명.CURRVAL 

실행방법 : SELECT SAMPLE_SEQ.CURRVAL FROM DUAL;

 

4. INSERT 문에 시퀀스 사용하기

INSERT INTO 테이블명(컬럼명, 컬럼명2, ...)

VALUES(시퀀스명.NEXTVAL, 값, 값2, ...);

 

5. 시퀀스 삭제 : DROP SEQUENCE 시퀀스명

 

(참고) 시스템 테이블(딕셔너리 뷰 = 데이터 사전) : USER_SEQUENCES (DBA(관리자)가 주로 사용)

 

 

 

< Index(인덱스) : 조회속도 향상을 위해 컬럼에 인덱스를 생성함 >

 

1. 인덱스 생성 : CREATE INDEX 인덱스명 ON 테이블명(컬럼명);

2. 인덱스 삭제 : DROP INDEX 인덱스명;

3. 결합 인덱스 : CREATE INDEX 인덱스명 ON 테이블명(컬럼, 컬럼2, ...);

4. 함수 인덱스 : CREATE INDEX 인덱스명 ON 테이블명(함수식);

 

(참고) 인덱스를 위한 시스템 테이블(딕셔너리 뷰 = 데이터 사전) : USER_IND_COLUMNS (DBA(관리자)가 주로 사용)