SQL 17편: 객체(데이터 사전, 인덱스, 뷰, 시퀀스, 동의어)

객체 종류

• 테이블
• 데이터 사전
• 인덱스
• 뷰
• 시퀀스
• 동의어

 

 

 

---

데이터 사전

데이터베이스에서 테이블은 1.사용자 테이블과 2.데이터 사전으로 나뉜다. 데이터 사전은 데이터베이스의 사용자, 권한 등의 데이터베이스 설정을 저장하기 위한 특별한 테이블로 이해하면 될 듯 하다. 사용자 테이블은 Normal Table, 데이터 사전은 Base Table이라고 불리기도 한다. 

 

메모리,성능,사용자,권한,객체 등 DB 운영에 중요한 데이터가 보관되어 있다.

데이터 사전 정보에 직접 접근이나 작업하는 것은 허용하지 않으며, SELECT문으로 정보 열람을 할 수 있게 해 둔다.

데이터 사전 뷰는 접두어를 보고 용도를 파악할 수 있다.

접두어	설명
USER_XXXX	현재 데이터베이스에 접속한 사용자가 소유한 객체 정보
ALL_XXXX	현재 데이터베이스에 접속한 사용자가 소유한 객체, 사용 허가를 받은 객체 등 사용 가능한 객체 정보
DBA_XXXX	데이터베이스 관리를 위한 정보이며 데이터베이스 관리 권한을 가진 SYSTEM, SYS 사용자만 열람 가능한 정보
V$_XXXX	데이터베이스 성능 관련 정보

 

사용 가능한 데이터 사전을 조회하고 싶다면 DICTIONARY 또는 DICT를 조회하면 된다. 사용 권한이 없는 데이터 사전을 조회하는 경우 해당 개체의 존재 여부조차 확인할 수 없으며, 일괄적으로 존재하지 않는다고 표현된다. 

SELECT * FROM DICT;

 

 

 

 

---

인덱스

검색 기능의 향상을 위해 테이블 열에 사용하는 객체이다.

테이블에 보관된 특정 행 데이터의 주소(위치 정보)를 책 페이지처럼 목록으로 만들어 놓은 것이다.

WHERE절의 조건식에서 인덱스가 걸린 열을 검색 조건으로하면 출력 속도가 빨라진다.  

열이 기본키 또는 고유키일 경우에 자동으로 생성되며, 사용자가 직접 특정 테이블의 열에 지정할 수도 있다. 

 

 

인덱스 생성

CREATE INDEX 인덱스 이름

ON 테이블이름(

열이름1 ASC or DESC(기본값: ASC)

열이름2 ASC or DESC

... );

 

 

인덱스 삭제

DROP INDEX 인덱스 이름;

 

 

 

 

---

뷰

가상 테이블이라고 부른다. 테이블을 조회하는 SELECT문을 저장한 객체를 뜻한다.

SELECT문을 저장하기 때문에 물리적 데이터를 따로 저장하지는 않는다. 

 

 

뷰의 목적

1. 가독성: SELECT 문의 복잡도를 줄여준다.

2. 보안: 가공을 거친 데이터만 SELECT한 뷰의 권한만 허용함으로써 테이블의 일부 민감한 열들을 노출하지 않을 수 있다. 

 

먼저 뷰 생성 권한이 없을 시 SQLPLUS에서 권한 부여해주는 과정이 필요하다.

SQLPLUS SYSTEM/oracle
GRANT CREATE VIEW TO SCOTT;

 

 

뷰 생성

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름(열이름1, 열이름2, ...)

AS (저장할 SELECT문)

[WITH CHECK OPTION [CONSTRAINT 제약 조건]]

[WITH READ ONLY [CONSTRAINT 제약 조건]] 

OR REPLACE	같은 이름의 뷰가 존재할 경우 대체함
FORCE	뷰가 저장할 SELECT문의 기반 테이블이 존재하지 않아도 강제로 생성
NOFORCE	뷰가 저장할 SELECT문의 기반 테이블이 존재할 경우에만 생성(기본값)
WITH CHECK OPTION	지정한 제약 조건을 만족하는 데이터에 한해 DML 작업이 가능하도록 뷰 생성
WITH READ ONLY	뷰의 열람, 즉 SELECT만 가능하도록 뷰 생성

 

 

뷰 삭제

DROP VIEW 뷰 이름;

 

 

뷰 VS 인라인 뷰

객체로 저장되는 뷰와는 달리, 인라인 뷰는 SQL문에서 일회성으로 만들어 사용하는 뷰를 인라인 뷰라고 한다.

SELECT 문에서 사용되는 서브쿼리, WITH절에서 미리 이름을 정의해두고 사용하는 SELECT문 등이 이에 해당된다. 

이 인라인 뷰와 ROWNUM을 사용하면 ORDER BY절을 통해 정렬된 결과 중 최상위 몇 개의 데이터를 출력하는 것이 가능해진다.

ROWNUM은 실제 테이블에 존재하지는 않지만, ROWNUM열을 SELECT하여 조회하면 조회되는 의사 열(pseudo column)이다. 테이블에 저장된 행이 조회된 순서대로 매겨진 일련번호이다. SELECT 문으로 조회되는 순간 정해지는 것이기 때문에 ORDER BY 절에 의하여 정렬된다고 하더라도 유지된다. 

 

 

 

 

---

시퀀스

특정 규칙에 맞는 연속 숫자를 생성하는 객체

 

 

시퀀스 생성하기

CREATE SEQUENCE 시퀀스 이름

[INCREMENT BY n]

[START WITH n]

[MAXVALUE n | NOMAXVALUE]

[MINVALUE n | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE n | NOCACHE]

 

*CACHE n / NOCACHE

CACHE n: 시퀀스가 생성할 번호를 메모리에 미리 할당해 놓은 수를 지정

NOCACHE: 미리 생성하지 않도록 설정

둘 다 생략할 경우 기본값은 20 

 

 

시퀀스 사용하기

시퀀스이름.CURRVAL : 마지막으로 생성한 번호를 반환

시퀀스이름.NEXTVAL : 다음 번호를 생성

 

 

시퀀스 수정

시퀀스 생성할 때 정했던 START WITH n 옵션을 제외하고는 모두 수정 가능하다. 

ALTER SEQUENCE 시퀀스 이름 

INCREMENT BY n

MAXVALUE n | NOMAXVALUE

MINVALUE n | NOMINVALUE

CYCLE | NOCYCLE

CACHE n | NOCACHE

 

 

시퀀스 삭제

DROP SEQUENCE 시퀀스이름;

 

 

 

 

---

동의어

테이블, 뷰, 시퀀스 등 객체 이름 대신 사용할 수 있는 다른 이름을 부여하는 객체. 

 

 

동의어 VS 테이블 별칭

동의어는 객체이기 때문에 일회성이 아니다. (테이블 별칭은 SELECT 문 내에서만 효력이 있다.)

 

 

동의어 생성하기

CREATE PUBLIC SYNONYM 동의어이름

FOR 사용자.객체이름;

 

*PUBLIC : 동의어를 데이터베이스 내 모든 사용자가 사용할 수 있도록 설정. 생략 시 생성한 사용자만 사용 가능

*사용자. : 생략 시 현재 접속한 사용자로 지정

 

 

동의어 삭제

DROP SYNONYM 동의어이름;