CHAPTER 13. 객체 종류-DO IT! 오라클로 배우는 데이터베이스 입문

CHAPTER13 객체 종류

오라클 데이터베이스는 데이터 보관 및 관리를 위한 여러 기능과 저장 공간을 객체를 통해 제공

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

데이터 사전이란?

오라클 데이터베이스 테이블은 사용자 테이블( user table)과 데이터 사전(data dictionary)으로 나뉨

사용자 테이블(normal table): 데이터베이스를 통해 관리할 데이터를 저장하는 테이블

데이터 사전(base table): 데이터베이스를 구성하고 운영하는 데 필요한 모든 정보를 저장하는 특수한 테이블로 데이터 베이스가 생성되는 시점에 자동으로 만들어짐

데이터 사전에는 데이터베이스 메모리,성능,사용자,권한,객체 등 오라클 데이터베이스 운영에 중요한 데이터가 보관되어 있음.

이 데이터에 문제가 발생한다면 오라클 데이터베이스사용이 불가능해짐.

따라서 오라클 데이터베이스는 사용자가 데이터 사전 정보에 직접 접근하거나 작업하는 것을 허용하지 않음.

그 대신 사전 뷰( data dictionary view)를 제공하여 select문으로 정보열람을 할 수 있게 해둠

뷰(view)는 어떤 목적을 위해 테이블 일부 또는 전체 데이터 열람을 주 목적으로 사용하는 객체

접두어+복수형 단어 설명

user_*	자기소유, 현재 데이터 베이스에 접속한 사용자가 소유한 객체 정보
all_*	자기소유+ 접근가능,현재 데이터베이스에 접속한 사용자가 소유한 객체 또는 다른 사용자가 소유한 객체 중 사용 허가를 받은 객체,즉 사용가능한 모든 객체 정보
dba_*	db내의 모든 것,데이터 베이스 관리를 위한 정보(데이터 베이스 관리권한을 가진 system,sys사용자만 열람가능)
V$_*	데이터베이스 성능 관련 정보(X$_XXXX테이블의 뷰)

해당 계정에서 사용가능한 데이터 사전 살펴보기

SELECT * FROM DICT;
SELECT * FROM DICTIONARY;

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

USER_접두어로 시작하는 이름의 데이터 사전에는 현재 오라클에 접속해 있는 사용자가 소유한 객체정보가 보관되어 있음.

접두어 다음에 객체가 오면 복수형 단어가 오게됨.

SELECT TABLE_NAME
FROM USER_TABLES;

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

ALL_접두어를 가진 데이터 사전은 오라클 데이터베이스에 접속해 있는 사용자가 소유한 객체 및 다른 사용자가 소유한 객체 중 사용이 허락되어있는 객체 정보를 가지고 있음.

SELECT OWNER,TABLE_NAME
FROM ALL_TABLES;

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

DBA_접두어를 가진 데이터 사전은 데이터베이스 관리 권한을 가진 사용자만 조회할 수 있는 테이블임.

-- SYSTEM,SYS계정에서 가능
SELECT * FROM DBA_TABLES;

오라클 데이터 베이스에서 어떤 사용자가 사용 권한이 없는 정보 열람을 시도할 경우에 보안을 위해오라클 데이터베이스는 해당 개체가 존재하지 않는다고 알려줌

DBA_접두어가 붙은 데이터 사전은 오라클 데이터베이스 운영과 관련된 여러 정보를 보관

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

인덱스란?

색인이라는 뜻의 인덱스(INDEX)는 오라클 데이터베이스에서 데이터 검색 성능의 향상을 위해 테이블 열에 사용하는 객체

테이블에 보관된 특정 열 데이터의 주소, 즉 위치 정보를 목록처럼 만들어 놓은 것.

인덱스 사용 여부에 따라 데이터 검색 방식을 TABLE FULL SCAN,INDEX SCAN으로 구분

TABLE FULL SCAN:테이블 데이터를 처음부터 끝까지 검색하여 원하는 데이터를 찾는 방식

INDEX SCAN: 인덱스를 통해 데이터를 찾는 방식

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

인덱스 생성

사용자가 직접 인덱스를 만들 때는 CREATE문을 사용

CREATE문에서 인덱스를 생성할 테이블 및 열을 지정하며 열은 하나 또는 여러 개 지정할 수 있음.

지정한 각 열 별로 정렬 순서 (오름차순 또는 내림차순)를 정할 수도 있음.

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

인덱스 삭제

인덱스 삭제는 DROP명령어 사용

DROP INDEX 인덱스 이름

인덱스는 데이터 접근 및 검색 속도 향상을 위해 사용하는 객체이지만 인덱스 생성이 항상 좋은 결과로 이어지지는 않음.

정확한 데이터 분석에 기반을 두지 않은 인덱스의 무분별한 생성은 오히려 성능을 떨어뜨리는 원인이 되기도 함

인덱스는 데이터의 종류,분포도,조회하는 SQL의 구성, 데이터 조작 관련 SQL문의 작업 빈도, 검색 결과가 전체 데이터에서 차지하는 비중 등 많은 요소를 고려하여 생성

13-3 테이블처럼 사용하는 뷰

뷰란?

흔히 가상테이블로 부르는 뷰는 하나이상의 테이블을 조회하는 SELECT문을 저장한 객체

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

뷰를 SELECT문의 FROM절에 사용하면 특정 테이블을 조회하는 것과 같은 효과를 얻을 수 있음.

SELECT *
FROM 뷰_이름;

뷰의 사용 목적(편리성)

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

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

여러 SQL문에서 자주 활용하는 SELECT문을 뷰로 저장해 놓은 후 다른 SQL문에서 활용하면 전체SQL문의 복잡도를 완화하고 본래 목적의 메인 쿼리에 집중할 수 있어 편리함.

뷰의 사용 목적(보안성)

아무에게나 노출하기에는 예민한 데이터가 존재할 때 해당 사용자에게 특정 테이블의 전체 조회 권한을 부여하는 것은 데이터 보안에 위협이 될 수 있으므로 주의해야 함.

테이블의 일부 데이터 또는 조인이나 여러 함수 등으로 가공을 거친 데이터만 SELECT하는 뷰 열람 권한을 제공하는 것이 불필요한 데이터 노출을 막을 수 있기 때문에 더 안전한 방법

뷰 생성

일반 계정은 뷰 생성 권한이 없으므로 SYSTEM계정으로 접속한 후 뷰 생성 권한을 줌.

GRANT CREATE VIEW TO 계정이름;

뷰 생성 기본 형식

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름 (열 이름 1,열 이름 ,….)
AS (저장할 SELECT 문)

[WITH CHECK OPTION [CONSTRAINT 제약조건]]
[WITH READ ONLY [CONSTRAINT 제약조건]];

OR REPLACE:같은 이름의 뷰가 이미 존재할 경우 현재 생성할 뷰로 대체하여 생성(선택)

FORCE:뷰가 저장할 SELECT문의 기반 테이블이 존재하지 않아도 강제로 생성(선택)

NOFORCE:뷰가 저장할 SELECT문의 기반 테이블이 존재할 경우에만 생성(기본값)(선택)

WITH CHECK OPTION: 지정한 제약 조건을 만족하는 데이터에 한해 DML작업이 가능하도록 뷰 생성(선택)

WITH READ ONLY:뷰의 열람, 즉 SELECT만 가능하도록 뷰 생성(선택)

CREATE VIEW 뷰이름
AS (SELECT 칼럼명1,칼럼명2,...
		FROM 테이블 명
		WHERE 조건식);

생성한 뷰 확인하기

SELECT * 
FROM USER_VIEWS;

하나의 테이블 뿐만아니라 여러 테이블을 조인하거나 서브쿼리를 사용한 좀 더 복합적인 SELECT문도 뷰에 저장할 수 있음.

뷰 삭제

DROP VIEW 뷰이름;

뷰는 실제 데이터가 아닌 SELECT문만 저장하므로 뷰를 삭제해도 테이블이나 데이터가 삭제되는 것은 아님

데이터를 따로 저장하는 것이 허용되는 구체화 뷰도 존재

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

CREATE문을 통해 객체로 만들어지는 뷰 외에 SQL문에서 일회성으로 만들어서 사용하는 뷰를 인라인 뷰(INLINE VIEW)라고 함

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

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

SELECT ROWNUM, E.*
FROM EMP E;

ROWNUM열은 EMP테이블에 존재하지는 않지만 ROWNUM열의 데이터가 숫자로 출력되고 있음을 확인할 수 있음.

ROWNUM은 의사열(PSEUDO COLUMN)이라고 하는 특수열

의사 열은 데이터가 저장되는실제 테이블에 존재하지는 않지만 특정 목적을 위해 테이블에 저장되어 있는 열처럼 사용 가능한 열을 뜻함.

ROWNUM외에 인덱스와 밀접하게 연관된 ROWID도 대표적인 의사 열

ROWNUM 열 데이터 번호는 테이블에 저장된 행이 조회된 순서대로 매겨진 일련 번호

이 특성을 인라인 뷰에서 적용하면 정렬된 SELECT문의 결과 순번을 매겨서 출력 가능

13-4 규칙에 따라 순번을 생성하는 시쿼스

시퀀스란?

시퀀스는 오라클 데이터베이스에서 특정 규칙에 맞는 연속 숫자를 생성하는 객체

시퀀스는 단순히 번호 생성을 위한 객체이지만 지속적이고 효율적인 번호 생성이 가능하므로 여러모로 자주 사용하는 객체

시퀀스 생성

시퀀스 역시 CREATE문으로 생성하여 다음과 같이 다양한 옵션을 지정할 수 있음.

CREATE SEQUENCE 시퀀스 이름 		-- 아래 절을 지정하지 않을 경우 1부터 시작하여 1만큼 계속 증가

[INCREMENT BY N] 	-- 시퀀스에서 생성할 번호의 증가 값(기본값은 1)(선택)

[START WITH N]   -- 시퀀스에서 생성할 번호의 시작 값(기본값은 1)(선택)

[MAXVALUE N |NOMAXVALUE]  
-- 시퀀스에서 생성할 번호의 최댓값 지정,최댓값은 시작값 이상,최솟값을 초괏값으로 지정
-- NOMAXVALUE로 지정했을경우 오름차순이면 10^37,내림차순일경우 -1로 설정(선택)

[MINVALUE N | NOMINVALUE]
-- 시퀀스에서 생성할 번호의 최솟값 지정,최솟값은 시작값 이하,최댓값 미만 값으로 지정
-- NOMINVALUE로 지정했을경우 오름차순이면 1,내림차순일경우 10^-26로 설정(선택)

[CYCLE | NOCYCLE]
-- 시퀀스에서 생성할 번호가 최댓값에 도달했을 때,CYCLE이면 시작값에서 다시시작
-- NOCYCLE이면 번호 생성이 중단되고 추가 번호 생성을 요청하면 오류 발생(선택)

[CACHE N | NOCACHE]
-- 시퀀스가 생성할 번호를 메모리에 미리 할당해 놓은 수를 지정
-- NOCACHE는 미리 생성하지 않도록 설정
-- 옵션을 모두 생략하면 기본값은 20(선택)

시퀀스 사용

생성된 시퀀스를 사용할 때는 [시퀀스 이름.CURVAL]과 [시퀀스 이름.NEXTVAL]을 사용할 수 있음.

CURVAL은 시퀀스에서 마지막으로 생성한 번호를 반환하며 NEXTVAL는 다음 번호를 생성

CURRVAL은 시퀀스를 생성하고 바로 사용하면 번호가 만들어진 적이 없으므로 오류가 남

시퀀스 수정

ALTER명령어로 시퀀스를 수정하고 DROP명령어로 시퀀스를 삭제

시퀀스 수정은 오른쪽과 같이 옵션을 재 설정하는 데 사용

START WITH값은 변경 불가

ALTER SEQUENCE 시퀀스 이름
[INCREMENT BY N]
[MAXVALUE N |NO MAXVALUE]
[MINVALUE N| NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE N|NOCACHE]

시퀀스 삭제

DROP SEQUENCE를 사용하면 시퀀스를 삭제할 수 있음

시퀀스를 삭제해도 시퀀스를 사용(SEQ_DEPT_SEQUENCE.NEXTVAL)하여 추가된 데이터는 삭제되지 않음.

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

동의어란?

동의어(SYNONYM)는 테이블, 뷰, 시퀀스 등 객체 이름 대신 사용할 수 있는 다른 이름을 부여하는 객체

주로 테이블 이름이 너무 길어 사용이 불편할 때 좀 더 간단하고 짧은 이름을 하나 더 만들어 주기 위해 사용

-- 기본형식
CREATE [PUBLIC] SYNONYM 동의어_이름
FOR [사용자.][객체이름];

PUBLIC:동의어를 데이터베이스 내 모든 사용자가 사용할 수 있도록 설정, 생략할 경우 동의어를 생성한 사용자 만 사용 가능(PUBLIC 으로 생성되어도 본래 객체의 사용 권한이 있어야 사용 가능)

동의어는 SELECT문의 SELECT절,FROM절에서 사용한 열 또는 테이블 별칭과 유사하지만, 오라클 데이터베이스에 저장되는 객체이기 때문에 일회성이 아니라는 점에서 차이가 있음.

동의어 생성 역시 권한을 따로 부여해야 하기 때문에 SYSTEM에 접속해서 생성 권한을 부여

PUBLIC SYNONYM권한도 따로 부여해 주어야 함.

동의어 생성

CREATE SYNONYM E

FOR EMP;

동의어 삭제

DROP SYNONYM을 사용하여 동의어를 삭제

DROP SYNONYM E;

동의어를 삭제해도 테이블 이름과 데이터에는 아무 영향을 주지 않음