[오라클로 배우는 데이터베이스 입문] 13. 객체 종류

1. 데이터베이스를 위한 데이터를 저장한 데이터 사전

- 데이터사전이란? 데이터베이스를 구성하고 운영하는 데 필요한 모든 정보를 저장하는 특수한 테이블, 데이터베이스 생성 시점에 자동으로 만들어짐

- 데이터 사전에는 오라클 데이터베이스 운영에 중요한 데이터가 보관되어 있으므로 사용자가 사전 정보에 접근하거나 작업하는 것을 허용하지 않음, 따라서 데이터 사전 뷰를 제공하여 SELECT문으로 열람할 수 있게 함

접두어	설명
USER_XXXX	현재 데이터베이스에 접속한 사용자가 소유한 객체 정보
ALL_XXXX	현재 데이터베이스에 접속한 사용자가 소유한 객체 또는 다른 사용자가 소유한 객체 중 사용 허가를 받은 객체, 즉 사용가능한 모든 객체 정보
DBA_XXXX	데이터베이스 관리를 위한 정보(데이터베이스 관리 권한을 가진 SYSTEM, SYS 사용자만 열람 가능)
V$_XXXX	데이터베이스 성능 관련 정보(X$_XXXX 테이블의 뷰)

그 외 데이터 사전은 DICTIONARY 또는 DICT을 조회하면 알 수 있음

SCOTT 계정에서 사용할 수 있는 데이터 사전 살펴보기

- USER_접두어를 가진 데이터 사전

※ USER_, ALL_, DBA_ 접두어 다음에 오는 객체는 복수형 단어가 나옴

- ALL_접두어를 가진 데이터 사전

열 이름	자료형	NULL여부	설명
OWNER	VARCHAR2(30)	NOT NULL	테이블을 소유한 사용자 (ALL_TABLES에만 존재)
TABLE_NAME	VARCHAR(30)	NOT NULL	테이블 이름
TABLESPACE_NAME	VARCHAR(30)		테이블 스페이스 이름
NUM_ROWS	NUMBER		테이블에 저장된 행수

- DBA_접두어를 가진 데이터 사전

데이터베이스 관리 권한을 가진 사용자만 조회할 수 있기 때문에 SCOTT계정으로는 조회할 수 없음

 

SYSTEM 사용자로 접속하여 DBA_접두어 사용해보기

DBA_USERS 사용하여 사용자 정보 알아보기(SYSTEM계정으로 접속해야 함)

2. 더 빠른 검색을 위한 인덱스

- 인덱스란? 데이터 검색 성능의 향상을 위해 테이블 열에 사용하는 객체

- 인덱스 사용 여부에 따른 데이터 검색방식

○ Table Full Scan: 테이블 데이터를 처음부터 끝까지 검색하여 원하는 데이터를 찾는 방식

○ Index Scan: 인덱스를 통해 데이터를 찾는 방식

SCOTT계정이 소유한 인덱스 컬럼 정보

※ 인덱스는 사용자가 직접 특정 테이블의 열에 지정할 수도 있지만 열이 기본키 또는 고유키일 경우 자동으로 생성됨

 

- 인덱스 생성: 사용자가 직접 인덱스를 만들 때

CREATE INDEX 인덱스 이름 ON 테이블 이름(열 이름1 ASC or DESC, 열 이름2  ASC or DESC, ... );

지정한 각 열별로 인덱스 정렬 순서 정할 수 있음(ASC or DESC)

EMP 테이블의 SAL열에 인덱스 생성

※ 여러 방식으로 생성할 수 있는 인덱스 종류

방식	사용
단일 인덱스(single index)	CREATE INDEX IDX_NAME ON EMP(SAL);
복합 인덱스(concatenated index), 결합 인덱스(composite index) - 두 개 이상 열로 만들어지는 인덱스 - WHERE절의 두 열이 AND 연산으로 묶이는 경우	CREATE INDEX IDX_NAME ON EMP(SAL, ENAME,...);
고유 인덱스(unique index) - 열에 중복 데이터가 없을 때 사용 - UNIQUE 키워드를 지정하지 않으면 비고유 인덱스(non unique)가 기본값	CREATE UNIQUE INDEX IDX_NAME ON EMP(EMPNO);
함수 기반 인덱스(function based index) - 열에 산술식 같은 데이터 가공이 진행된 결과로 인덱스 생성	CREATE INDEX IDX_NAME ON EMP(SAL*12+COMM);
비트맵 인덱스(bitmap index) - 데이터 종류가 적고 같은 데이터가 많이 존재할 때 주로 사용	CREATE BITMAP INDEX IDX_NAME ON EMP(JOB);

- 인덱스 삭제

DROP INDEX 인덱스 이름;

인덱스 삭제하기

3. 테이블처럼 사용하는 뷰

뷰란? 하나 이상의 테이블을 조회하는 SELECT문을 저장한 객체

뷰	서브쿼리
SELECT * FROM VW_EMP20;	SELECT * FROM (SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP WHERE DEPTNO=20);
VW_EMP20 뷰를 생성해놓으면 간단하게 VW_EMP20뷰를 테이블처럼 조회할 수 있음

- 뷰의 사용 목적

1) 편리성: SELECT문의 복잡도를 완화하기 위해

2) 보안성: 테이블의 특정 열을 노출하고 싶지 않을 경우

- 뷰 생성

SCOTT계정에 뷰 생성 권한 부여하기

CREATE[OR REPLACE] [FORCE | NOFORCE] VIEW 뷰 이름(열 이름1, 열 이름2, ...) AS (저장할 SELECT문) [WITH CHECK OPTION [CONTRAINT 제약 조건]] [WHTH READ ONLY [CONSTRAINT 제약 조건]];

OR REPLACE: 같은 이름의 뷰가 이미 존재할 경우 현재 생성할 뷰로 대체하여 생성(선택) FORCE: 뷰가 저장할 SELECT문의 기반 테이블이 존재하지 않아도 강제로 생성(선택) NO FORCE: 뷰가 저장할 SELECT문의 기반 테이블이 존재할 경우에만 생성(기본값) 뷰 이름: 생성할 뷰 이름(필수) 열 이름: SELECT문에 명시된 이름 대신 사용할열 이름 지정(생략 가능)(선택) 저장할 SELECT문: 생성할 뷰에 저장할 SELECR문 지정(필수) WITH CHECK OPTION: 지정할 제약 조건을 만족하는 데이터에 한해 DML 작업이 가능하도록 뷰 생성(선택) WITH READ ONLY: 뷰의 열람, 즉 SELECT만 가능하도록 뷰 생성(선택)

뷰 생성 및 확인

생성한 뷰 조회하기

- 뷰 삭제하기(DROP)

뷰 삭제 및 확인

- 인라인 뷰를 사용한 TOP-N SQL문

인라인뷰: SQL문에서 일회성으로 만들어서 사용하는 뷰

ROWNUM을 추가로 조회하기

 

SAL 열 기준으로 정렬하기

인라인뷰(서브쿼리), 인라인뷰(WITH절) 조회

인라인 뷰로 TOP-N추출하기(급여가 높은 상위 3명 데이터 출력하기)

4. 규칙에 따라 순번을 생성하는 시퀀스

- 시퀀스란? 특정 규칙에 맞는 연속 숫자를 생성하는 객체

- 시퀀스 생성

CREATE SEQUENCE 시퀀스 이름 [INCREMENT BY n] [START WITH n] [MAXVALUE n | NOMAXVALUE] [MINVALUE n | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE | NOCACHE]

CREATE SEQUENCE: 생성할 시퀀스 이름 지정. 아래 절들을 지정하지 않았을 경우 1부터 시작하여 1만큼 계속 증가하는 시퀀스가 생성(필수) [INCREMENT BY n]: 시퀀스에서 생성할 번호의 증가 값(기본값은 1)(선택) [START WITH n]: 시퀀스에서 생성할 번호의 시작 값(기본값은 1)(선택) [MAXVALUE n | NOMAXVALUE]: 시퀀스에서 생성할 번호의 최댓값, 최댓값은 시작값(START WITH)이상, 최솟값(MINVALUE)을 초괏값으로 지정. NOMAXVALUE로 지정할 경우 오름하순이면 10^27, 내림차순일 경우 -1로 설정(선택) [MINVALUE n | NOMINVALUE]: 시퀀스에서 생성할 번호의 최솟값, 최솟값은 시작값(START WITH)이하, 최댓값(MAXVALUE)미만 값으로 지정. NOMINVALUE로 지정할 경우 오름차순이면 1, 내림차순일 경우 10^-26로 설정(선택) [CYCLE | NOCYCLE]: 시퀀스에서 생성한 번호가 최댓값(MAXVALUE)에 도달했을 경우 CYCLE이면 시작 값(START WITH)에서 다시 시작, NOCYCLE이면 번호 생성이 중단, 추가 번호 생성을 요청하면 오류 발생(선택) [CACHE | NOCACHE]: 시퀀스가 생성할 번호를 메모리에 미리 할당해 놓은 수를 지정, NOCACHE는 미리 생성하지 않도록 성정. 옵션을모두 생략하면 기본값은 20(선택)

DEPT_SEQUECE테이블 생성

 

시퀀스 생성 및 확인

- 시퀀스 사용

[시퀀스 이름.CURRVAL]: 시퀀스에서 마지막으로 생성한 번호를 반환, 시퀀스 생성 후 바로 사용하면 번호가 만들어진 적이 업기때문에 오류남

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

마지막으로 생성된시퀀스 확인하기

- 시퀀스 수정

CYCLE 옵션을 사용한 시퀀스의 최댓값 도달 후 수행결과 확인하기

- 시퀀스 삭제

시퀀스 삭제 및 확인

5. 공식 별칭을 지정하는 동의어

동의어란? 테이블, 뷰, 시퀀스 등 객체 이름 대신 사용할 수 있는 다른 이름을 부여하는 객체/주로 테이블 이름이너무 길어 불편할 때 짧고 간단하게 만들어주기 위해 사용

CREATE[PUBLIC] SYNONYM 동의어 이름 FOR[사용자 .][객체 이름];

PUBLIC: 동의어를 데이터베이스 내 모든사용자가 사용할 수 있도록 설정. 생략할 경우 동의어를 생성한 사용자만 사용가능(PUBLIC으로 생성되어도 본래 객체의 사용 권한이 있어야 사용 가능) 동의어 이름: 생성할 동의어 이름(필수) 사용자.: 생성할 동의어의 본래 객체 소유 사용자를 지정. 생략할 경우 현재 접속한 사용자로 지정(선택) 객체 이름: 동의어를 생성할 때 대상 객체 이름(필수)

※ 동의어는 데이터베이스에 저장되는 객체이며 동의어 생성 권한 별도로 부여해야 함

- 동의어 생성

동의어 E 생성 및 조회

- 동의어 삭제