[oracle] 시퀀스, 인덱스, 동의어

재현·2024년 6월 17일

🔍 시퀀스

✍ 시퀀스(Sequence)는 데이터베이스 객체 중 하나로, 일련번호(Sequence number)를 생성하는 데 사용된다. 주로 유일한 숫자 값을 생성하여 기본 키 값이나 다른 열의 값을 자동으로 증가시킬 때 사용되며 시퀀스는 여러 세션에서 공유되어 사용될 수 있고, 데이터베이스의 성능을 향상시키고 데이터의 무결성을 유지하는 데 중요한 역할을 한다.

종류	설명
INCREMENT BY	시퀀스 실행 시 증가시킬 값
START WITH	시퀀스의 시작값
NOMAXVALUE/MAXVALUE	디폴트값 설정, 증가일때 10^27, 감소일때 -1, 최대값 설정, 시작숫자와 같거나 커야하고 MINVALUE보다 커야함
NOMINVALUE/MINVALUE	디폴트값 설정, 증가일때 1, 감소일때 -10^28, 최소값 설정, 시작숫자와 작거나 같아야하고 MAXVALUE보다 작아야함
NOCACHE/CACHE	NOCACHE(사용안함), CACHE(캐시를 사용하여 미리 값을 할당해 놓아서 속도가 빠르며, 동시 사용자가 많을 경우 유리)

NOCYCLE/CYCLE	NOCYCLE(반복안함), CYCLE(시퀀스의 최댓값에 도달 시 최솟값 1부터 다시시작)
NOORDER/ORDER	NOORDER(사용안함), ORDER(요청 순서로 값을 생성하여 발생 순서를 보장하지만 조금의 시스템 부하가 있음)

-- 시퀀스
CREATE SEQUENCE DEPTS_SEQ
    INCREMENT BY 1 -- 증가값
    START WITH 91 -- 시작값
    MAXVALUE 100 -- 최댓값
    NOCACHE -- 캐쉬 여부
    NOCYCLE; -- 최댓값 도달 시 시작 값부터 다시 반복여부
    
-- 시퀀스 확인
SELECT * FROM USER_SEQUENCES;

-- USER_OBJECTS 데이터 딕셔너리 조회
SELECT OBJECT_NAME FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'SEQUENCE';

DROP TABLE DEPTS CASCADE CONSTRAINTS;

CREATE TABLE DEPTS(
    DEPTNO NUMBER(2),
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13),
    CONSTRAINT DEPTS_DNAME_UK UNIQUE(DNAME),
    CONSTRAINT DEPTS_DEPTNO_PK PRIMARY KEY(DEPTNO)
);

INSERT INTO DEPTS(DEPTNO, DNAME, LOC)
VALUES (DEPTS_SEQ.NEXTVAL, 'MARKETING', 'SAN DIEGO');

SELECT * FROM DEPTS;

-- 현재 시퀀스 확인
SELECT DEPTS_SEQ.CURRVAL FROM DUAL;

-- 시퀀스 수정 (START WITH 제외) 
ALTER SEQUENCE DEPTS_SEQ MAXVALUE 99999;

-- 시퀀스 삭제
DROP SEQUENCE DEPTS_SEQ;

🔍 인덱스

✍ 데이터베이스에서 검색 속도를 향상시키기 위해 사용되는 자료 구조다. 인덱스는 테이블의 하나 이상의 열에 대한 정렬된 데이터 구조로, 데이터베이스가 레코드를 더 빠르게 검색하고 필터링할 수 있도록 도와주고, 특히 대량의 데이터에서 원하는 행을 빠르게 찾을 때 인덱스는 매우 중요한 역할을 한다.

-- 인덱스
SELECT * FROM EMPS WHERE FIRST_NAME = 'David';

-- 인덱스 생성
CREATE INDEX EMPS_FIRST_NAME_IDX
ON EMPS(FIRST_NAME);

-- 인덱스 삭제
DROP INDEX EMPS_FIRST_NAME_IDX;

-- 인덱스 확인
SELECT IC.INDEX_NAME, IC.COLUMN_NAME, IX.UNIQUENESS
FROM USER_INDEXES IX, USER_IND_COLUMNS IC
WHERE IC.INDEX_NAME = IX.INDEX_NAME
AND IC.TABLE_NAME='EMPLOYEES';

-- BITMAP INDEX는 11g에서 지원하지 않는다.
CREATE BITMAP INDEX EMPS_COMM_IDX ON EMPS(COMMISSION_PCT);

-- CTAS로 생성한 EMPS 테이블이기 떄문에 UNIQUE 제약조건이 안걸려 있음
SELECT * FROM EMPS WHERE EMAIL = 'DAUSTIN';

-- UNIQUE INDEX를 생성
CREATE UNIQUE INDEX EMPS_EMAIL_IDX ON EMPS(EMAIL);

SELECT * FROM EMPS;
DESC EMPS;

-- ERROR = UNIQUE INDEX를 생성하면 그 열은 UNIQUE 제약조건이 자동으로 생성
INSERT INTO EMPS 
    (EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID)
VALUES
    (200, 'HEO', 'DAUSTIN', '02/02/01', 'IT_PROG');

SELECT * FROM EMPS
WHERE COALESCE(SALARY+SALARY*COMMISSION_PCT, SALARY) > 10000;

-- 함수기반 인덱스  
CREATE INDEX EMP_ANNUSAL_IDX
ON EMPS(COALESCE(SALARY+SALARY*COMMISSION_PCT, SALARY));

SELECT * FROM EMPS
WHERE FIRST_NAME='Peter' AND LAST_NAME = 'Hall';

-- 복합 인덱스
CREATE UNIQUE INDEX EMPS_NAME_IDX
ON EMPS(FIRST_NAME, LAST_NAME);

🔍 동의어

✍ 특정 객체(예: 테이블, 뷰, 시퀀스, 프로시저 등)에 대해 간단한 별칭을 제공하는 객체. 동의어를 사용하면 객체의 실제 이름이나 위치에 관계없이 쉽게 접근할 수 있으며, 이를 통해 데이터베이스 객체의 접근성과 관리가 용이해지고, 객체 이름의 변경이나 복잡한 이름 구조의 사용을 줄일 수 있다.

-- 동의어 생성
CREATE SYNONYM EMP60 FOR EMP_DEPT60;

SELECT * FROM EMP60;
SELECT * FROM EMP_DEPT60;

-- 동의어 삭제
DROP SYNONYM EMP60;

-- 조회불가
SELECT * FROM EMP60;