[SQL] 시퀀스(sequence), 동의어(synonym), 인덱스(index)

다른 유저의 테이블 참조

• 다른 유저가 소유한 테이블은 유저의 스키마에 없음
• 소유자의 이름을 접두어로 사용해야 함
  • ex) SELECT * FROM userA.employees;

✔️ 시퀀스(sequence)

• 고유 번호를 자동으로 생성 가능
• 공유할 수 있는 객체
• Primary key값을 생성하는데 사용 가능
• 응용 프로그램 코드를 대체함
• 시퀀스 값이 메모리에서 캐시된 경우, 액세스 속도가 향상됨

CREATE SEQUENCE 문

-- 괄호 안에 있는 건 전부 생략가능
CREATE SEQUENCE [ schema. ] sequence
	[ { START WITH | INCREMENT BY } integer
    | { MAXVALUE integer | NOMAXVALUE }
    | { MINVALUE integer | NOMINVALUE }
    | { CYCLE | NOCYCLE }
    | { CACHE integer | NOCACHE }
    | { ORDER | NOORDER }
];

• START WITH: sequence 시작점
• INCREMENT BY: sequence 증가치 설정 (default 값은 1 (1씩 증가))
• MAXVALUE: 최댓값 지정
• MINVALUE: 최솟값 지정
  • MAX/MINVALUE 설정 시 CYCLE과 함께 쓰임 (default는 NOCYCLE)
  • CYCLE을 지정하면 MAXVALUE에 도달 시 MINVALUE부터 재생성(CYCLE)
• CACHE: 메모리에 해당 수치만큼 데이터를 적재
  (데이터 불러오는 속도 개선)
• ORDER: sequence 번호가 요청 순서대로 생성되는지 여부 지정

시퀀스 생성

• departments 테이블의 Primary key에 사용할 dept_deptid_seq라는 시퀀스 생성
• CYCLE 사용 시, 숫자값의 중복 발생 가능

CREATE SEQUENCE dept_deptid_seq
                START WITH 280
                INCREMENT BY 10
                MAXVALUE 9999
                NOCACHE
                NOCYCLE;

📌 NEXTVAL 및 CURRVAL Pseudocolumn

NEXTVAL: nextvalue
CURRVAL: currentvalue

• NEXTVAL은 사용가능한 다음 시퀀스 값을 반환
  • 다른 유저인 경우도 포함하여 참조될 때마다 고유 값 반환
• CURRVAL은 가장 최근에 발생된 숫자값을 보여줌
• CURRVAL을 통해 확인된 숫자 이후에 숫자가 NEXTVAL을 통해 발생

CREATE SEQUENCE dept_deptid_seq
                START WITH 280
                INCREMENT BY 10
                MAXVALUE 9999
                NOCACHE
                NOCYCLE;

-- 280
SELECT dept_deptid_seq.NEXTVAL 
  FROM dual;

-- 290
SELECT dept_deptid_seq.NEXTVAL 
  FROM dual ; 

-- 290
SELECT dept_deptid_seq.CURRVAL
  FROM dual ; 

ID 2500에 "Support"라는 새 부서 삽입

insert into departments(department_id,
            department_name, location_id)
values    (dept_deptid_seq.nextval,
          'Support', 2500);

dept_deptid_seq 시퀀스의 현재 값 확인

select dept_deptid_seq.currval
from dual;

시퀀스 값 캐시

캐시: 메모리에 적재 해놓겠다는 의미

• 시퀀스 값을 메모리에 캐시하면 해당 값에 빠르게 액세스 가능
• 다음과 같은 경우 시퀀스 값에 간격이 발생할 수 있음
  • 롤백이 발생하는 경우
  • 시스템이 중단되는 경우
  • 시퀀스가 다른 테이블에서 사용되는 경우

시퀀스 수정

• 시퀀스의 소유자이거나 시퀀스에 대해 ALTER 권한이 있어야 함
• 후속 시퀀스 번호에만 적용됨
• START WITH는 수정 불가능
  • 다른 번호로 시퀀스를 재시작하려면 --> 시퀀스를 삭제하고 다시 생성해야 함
• 시퀀스 제거하려면 --> DROP문 사용

-- 오류 발생: 현재 시퀀스가 290이기 때문에
-- 300으로 하면 실행가능
ALTER SEQUENCE dept_deptid_seq
MAXVALUE 280 ;   

-- 시퀀스 제거
DROP SEQUENCE dept_deptid_seq ; 

시퀀스 정보

DESCRIBE user_sequences

---

✔️ 동의어(Synonym)

• 데이터베이스 객체
• 테이블 또는 기타 데이터베이스 객체에 대체 이름을 제공하기 위해 생성할 수 있음
• 데이터 딕셔너리 정보만 저장하고 다른 저장공간 필요 x
• 기본 스키마 객체의 ID 및 위치를 숨기는데 유용함

• 다른 유저가 소유한 테이블을 쉽게 참조 가능
• 긴 객체 이름을 짧게 만들 수 있음

CREATE [PUBLIC] SYNONYM synonym
FOR object;

동의어 생성

유저의 이름 붙여주기

매번 이렇게 붙이기 번거롭기 때문에

select * from ora1.emp;

synonym을 써주면 편리함

-- 이렇게 synonym을 정의하면
CREATE SYNONYM emp FOR ora1.emp ; 

-- 유저이름 안붙여도 검색됨
SELECT * FROM emp ;

-- 동의어 제거
DROP SYNONYM emp ; 

동의어 정보

DESCRIBE user_synonyms

SELECT * FROM user_synonyms;

---

✔️ 인덱스(Index)

• 스키마 객체
• 오라클 서버에서 포인터를 사용하여 행 검색 속도를 높이는데 사용가능
• 신속한 경로 액세스 방식을 사용하여 데이터를 빠르게 찾아
  디스크 I/O(입출력)를 줄일 수 있음
• 인덱스의 대상인 테이블에 종속적임
• 오라클 서버에서 자동으로 사용되고 유지 관리됨

인덱스가 생성되는 방식

🔸 자동

테이블 정의에서 PRIMARY KEY 또는 UNIQUE 제약 조건을 정의하면
고유 인덱스가 자동 생성됨

🔸 수동

행에 액세스하는 속도를 높이기 위해
유저가 열의 고유/비고유 인덱스를 생성할 수 있음

인덱스 생성

CREATE [UNIQUE] INDEX index
ON table (column[, column]...);

UNIQUE: 중복 허용하지 않겠다

인덱스 정보

DESCRIBE user_indexes

인덱스 제거

DROP INDEX index;