[Oracle] 기타 스키마 객체 생성에 대해 알아보자 :)

-데이터베이스 객체

객체	설명
테이블	기본 저장 단위이며 행으로 구성되어 있습니다.
뷰	하나 이상의 테이블에 있는 데이터의 부분 집합을 논리적으로 나타냅니다.
시퀀스	숫자 값을 생성합니다.
인덱스	데이터 검색 query의 성능을 향상시킵니다.
동의어	객체에 다른 이름을 부여합니다.

 

데이터베이스에는 테이블 외에도 기타 여러 객체가 있습니다. 뷰를 사용하면 테이블의 데이터를 표시하거나 숨길 수 있습니다. 많은 응용 프로그램에서는 Primary Key 값으로 고유 번호를 사용해야 합니다. 응용 프로그램에 이러한 요구 사항을 처리하는 코드를 작성하거나 시퀀스를 사용하여 고유 번호를 생성할 수 있습니다. 데이터 검색 query의 성능을 향상시키려면 인덱스를 생성하는 것을 고려해 보십시오. 인덱스를 사용하여 열 또는 열 모음에서 고유성을 강화할 수 있습니다. 동의어를 사용하여 객체에 다른 이름을 지정할 수 있습니다.

 

-뷰

테이블의 뷰를 생성하여 데이터의 논리적 하위 집합 또는 조합을 나타낼 수 있습니다. 뷰는 테이블 또는 다른 뷰를 기반으로 하는 논리적 테이블입니다. 뷰는 자체적으로 데이터를 갖고 있지 않지만 테이블의 데이터를 보거나 변경할 수 있는 window와 같습니다 . 뷰의 기반이 되는 테이블을 기 본 테 이 블 이라고 합니다. 뷰는 데이터 딕셔너리에 SELECT 문으로 저장됩니다.

 

-뷰의 이점

• 뷰는 테이블의 열을 선택적으로 표시하므로 데이터 액세스를 제한합니다.

• 뷰를 사용하여 복잡한 query 결과를 검색하는 간단한 query를 만들 수 있습니다. 예를 들어, 조인 문을 작성하는 방법을 몰라도 뷰를 사용하여 여러 테이블에서 정보를 query할 수 있습니다.

• 뷰는 특정 유저와 응용 프로그램에 대해 데이터 독립성을 제공합니다. 하나의 뷰를 사용하여 여러 테이블의 데이터 를 검색할 수 있습니다.

• 뷰는 특정 기준에 따라 유저 그룹에게 데이터 액세스 권한을 부여합니다.

 

-단순 뷰와 복합 뷰

뷰에는 단순 뷰와 복합 뷰의 두 가지 유형이 있습니다. 기본적인 차이점은 DML 작업(INSERT, UPDATE 및 DELETE)과 관련이 있습니다.

 

• 단순 뷰의 특징은 다음과 같습니다.

- 하나의 테이블에서만 데이터를 가져옵니다.

- 함수나 데이터 그룹을 포함하지 않습니다.

- 뷰를 통해 DML 작업을 수행할 수 있습니다.

 

• 복합 뷰의 특징은 다음과 같습니다.

- 여러 테이블에서 데이터를 가져옵니다.

- 함수나 데이터 그룹을 포함합니다.

- 뷰를 통한 DML 작업을 허용하지 않는 경우도 있습니다.

 

- 뷰 생성

 

• CREATE VIEW 문에 subquery를 포함시킵니다.

 

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)]

AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY [CONSTRAINT constraint]];

 

OR REPLACE - 뷰가 이미 있는 경우 다시 생성합니다.

FORCE - 기본 테이블의 존재 여부에 관계없이 뷰를 생성합니다.

NOFORCE - 기본 테이블이 있는 경우에만 뷰를 생성합니다(기본값).

view - 뷰의 이름입니다.

alias - 뷰의 query에서 선택한 표현식의 이름을 지정합니다. (alias의 수와 뷰에서 선택한 표현식의 수가 일치해야 합 니다.)

subquery - 완전한 SELECT 문입니다. (SELECT 리스트에 열의 alias를 사용할 수 있습니다.)

WITH CHECK OPTION - 뷰에서 액세스할 수 있는 행만 삽입하거나 갱신할 수 있도록 지정합니다.

constraint - CHECK OPTION 제약 조건에 할당되는 이름입니다.

WITH READ ONLY - 현재 뷰에서 DML 작업을 수행하지 못하도록 합니다.

 

• 부서 80의 사원에 대한 세부 정보를 포함하는 EMPVU80 뷰를 생성합니다.

CREATE VIEW empvu80

AS SELECT employee_id, last_name, salary

FROM employees

WHERE department_id = 80;

 

예제는 부서 80의 각 사원에 대한 사원 번호, 성 및 급여를 포함하는 뷰를 생성합니다. DESCRIBE 명령을 사용하여 뷰의 구조를 표시할 수 있습니다.

 

• subquery에서 열 alias를 사용하여 뷰를 생성합니다.

CREATE VIEW salvu50

AS SELECT employee_id ID_NUMBER, last_name NAME,

salary*12 ANN_SALARY

FROM employees

WHERE department_id = 50;

• 제공된 alias 이름으로 이 뷰에서 열을 선택합니다.

 

subquery에열alias를 포함하여 열 이름을 제어할 수 있습니다. 예제는 부서 50의 모든 사원에 대해 alias가 ID_NUMBER인 사원 번호 (EMPLOYEE_ID), alias가 NAME인 이름 (LAST_NAME) 및 alias가 ANN_SALARY인 연봉 (SALARY)을 포함하는 뷰를 생성합니다. 또는 CREATE 문과 SELECT subquery 사이에서 alias를 사용할 수 있습니다. 나열된 alias의 수와 subquery에서 선택한 표현식의 수가 일치해야 합니다.

 

테이블처럼 뷰에서도 데이터를 검색할 수 있습니다. 전체 뷰나 특정 행과 열의 내용을 표시할 수 있습니다.

 

- 뷰 수정

 

• CREATE OR REPLACE VIEW 절을 사용하여 EMPVU80 뷰를 수정합니다. 각 열 이름에alias를 추가합니다.

 

CREATE OR REPLACE VIEW empvu80

(id_number, name, sal, department_id)

AS SELECT employee_id, first_name || ' '

|| last_name, salary, department_id

FROM employees

WHERE department_id = 80;

 

OR REPLACE 옵션을 사용하면 이미 동일한 이름의 뷰가 있는 경우에도 뷰가 생성되므로 이전 버전의 뷰를 해당 소유자에 맞게 대체할 수 있습니다. 즉, 뷰를 삭제하고 다시 생성하고 객체 권한을 다시 부여하지 않고서도 뷰를 변경할 수 있습니다.

 

-복합 뷰 생성

그룹 함수를 포함하는 복합 뷰를 생성하여 두 테이블의 값을 표시합니다.

 

CREATE OR REPLACE VIEW dept_sum_vu

(name, minsal, maxsal, avgsal)

AS SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)

FROM employees e JOIN departments d

ON (e.department_id = d.department_id)

GROUP BY d.department_name;

 

예제에서는 각 부서별로 부서 이름, 최소 급여, 최대 급여 및 평균 급여를 포함하는 복합 뷰를 생성합니다. 뷰에 다른 이름이 지정되었습니다. 뷰의 모든 열이 함수나 표현식으로부터 파생되는 경우에는 다른 이름을 지정해야 합니다.

 

-뷰에 대한 DML 작업 수행 규칙

 

• 단순 뷰에서는 대개 DML 작업을 수행할 수 있습니다.

• 뷰에 다음 항목이 포함되어 있으면 행을 제거할 수 없습니다.

– 그룹 함수

– GROUP BY 절

– DISTINCT 키워드

– Pseudocolumn ROWNUM 키워드

 

-뷰에 대한 DML 작업 수행 규칙

 

뷰에 다음 항목이 포함되어 있으면 뷰의 데이터를 수정할 수 없습니다.

• 그룹 함수

• GROUP BY 절

• DISTINCT 키워드

• Pseudocolumn ROWNUM 키워드

• 표현식으로 정의되는 열

 

-WITH CHECK OPTION 절 사용

 

• WITH CHECK OPTION 절을 사용하여 뷰에 수행된 DML 작업이 뷰 영역에만 적용되도록 할 수 있습니다.

CREATE OR REPLACE VIEW empvu20

AS SELECT *

FROM employees

WHERE department_id = 20

WITH CHECK OPTION CONSTRAINT empvu20_ck ;

 

• department_id가 20이 아닌 행을INSERT하거나 뷰에 있는 임의의 행에서 부서 번호를 UPDATE하려는 시도는 WITH CHECK OPTION 제약 조건에 위반되므로 실패합니다.

 

-DML 작업 거부

• WITH READ ONLY 옵션을 뷰 정의에 추가하여 DML 작업이 발생하지 않도록 할 수 있습니다.

• 뷰에 있는 임의의 행에서 DML 작업을 수행하려고 시도하면 Oracle 서버 오류가 발생합니다.

 

CREATE OR REPLACE VIEW empvu10

(employee_number, employee_name, job_title)

AS SELECT employee_id, last_name, job_id

FROM employees

WHERE department_id = 10

WITH READ ONLY ;

 

읽기 전용 제약 조건이 있는 뷰에서 행을 제거하려고 시도하면 오류가 발생합니다.

 

-뷰 제거

뷰는 데이터베이스의 기본 테이블을 기반으로 하기 때문에 뷰를 제거해도 데이터는 손실되지 않습니다.

 

DROP VIEW view;

 

DROP VIEW 문을 사용하여 뷰를 제거할 수 있습니다. 이 명령문은 데이터베이스에서 뷰 정의를 제거합니다. 그러나 뷰를 삭제해도 뷰의 기반이 되는 테이블에는 영향을 미치지 않습니다. 또한 삭제된 뷰를 기반으로 하는 뷰나 기타 응용 프로그램은 사용할 수 없게 됩니다. 생성자나 DROP ANY VIEW 권한을 가진 유저만 뷰를 제거할 수 있습니다.

 

-시퀀스

 

시퀀스는 정수 값을 생성하는 데이터베이스 객체입니다. 시퀀스를 생성한 다음 이 시퀀스를 사용하여 번호를 생성합니다.

 

• 고유 번호를 자동으로 생성할 수 있습니다.

• 공유할 수 있는 객체입니다.

• Primary key 값을 생성하는 데 사용할 수 있습니다.

• 응용 프로그램 코드를 대체합니다.

• 시퀀스 값이 메모리에서 캐시된 경우 액세스 속도가 향상됩니다.

 

시퀀스는 유저가 생성한 데이터베이스 객체이며 정수를 생성하는 여러 유저가 공유할 수 있습니다. 시퀀스를 정의하여 고유 값을 생성하거나 동일한 번호를 재사용할 수 있습니다. 시퀀스의 일반적 용도는 각 행에 고유해야 하는 Primary key 값을 생성하는 경우입니다. 시퀀스는 내부 Oracle 루틴에 의해 생성되고 순차적으로 증가하거나 감소합니다. 이것은 시퀀스 생성 루틴을 작성하는 데 필요한 응용 프로그램 코드의 양을 줄일 수 있기 때문에 시간 절약형 객체라 할 수 있습니다. 시퀀스 번호는 테이블과 별도로 저장되고 생성됩니다. 따라서 여러 테이블에 동일한 시퀀스를 사용할 수 있습니다.

 

-CREATE SEQUENCE 문: 구문

 

자동으로 일련 번호를 생성하도록 시퀀스를 정의합니다.

 

CREATE SEQUENCE sequence

[INCREMENT BY n]

[START WITH n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}];

 

sequence - 시퀀스 생성기의 이름입니다.

INCREMENT - BY n 시퀀스 번호 사이의 간격을 지정하며, 여기서 n은 정수입니다.

(이 절을 생략하면 시퀀스는 1씩 증가합니다.)

START WITH - n 생성할 첫번째 시퀀스 번호를 지정합니다.

(이 절을 생략하면 시퀀스는 1부터 시작합니다.)

MAXVALUE - n 시퀀스가 생성할 수 있는 최대값을 지정합니다.

NOMAXVALUE - 오름차순 시퀀스의 경우 최대값 10^27을, 내림차순 시퀀스의 경우 –1을 지정합니다(기본 옵션). MINVALUE - n 최소 시퀀스 값을 지정합니다.

NOMINVALUE - 오름차순 시퀀스의 경우 최소값 1을, 내림차순 시퀀스의 경우 –(10^26)을 지정합니다(기본 옵션).

 

-시퀀스 생성

• DEPARTMENTS 테이블의 Primary key에 사용할 DEPT_DEPTID_SEQ라는 시퀀스를 생성합니다.

• CYCLE 옵션을 사용하지 마십시오.

 

CREATE SEQUENCE dept_deptid_seq

INCREMENT BY 10

START WITH 120

MAXVALUE 9999

NOCACHE

NOCYCLE;

 

예제는 DEPARTMENTS 테이블의 DEPARTMENT_ID 열에 사용할 DEPT_DEPTID_SEQ라는 시퀀스를 생성합니다. 이 시퀀스는 120에서 시작하고 캐시에 저장할 수 없으며 순환하지 않습니다. 시퀀스를 사용하여 Primary key 값을 생성하는 경우 시퀀스 주기보다 더 빠르게 이전 행을 지우는 믿을 만한 방법이 없으면 CYCLE 옵션을 사용하지 마십시오.

 

-NEXTVAL 및 CURRVAL Pseudocolumn

 

• NEXTVAL은 사용 가능한 다음 시퀀스 값을 반환합니다 . 다른 유저인 경우도 포함하여 참조될 때마다 고유 값을 반 환합니다.

• CURRVAL은 현재 시퀀스 값을 구합니다.

• CURRVAL이 값을 포함하기 전에 해당 시퀀스에 대해 NEXTVAL이 실행되어야 합니다.

 

-시퀀스 사용

 

• 위치 ID 2500에 "Support"라는 새 부서를 삽입합니다.

INSERT INTO departments(department_id,

department_name, location_id)

VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500);

 

예제는 DEPARTMENTS 테이블에 새 부서를 삽입합니다. 다음과 같이 DEPT_DEPTID_SEQ 시퀀스를 사용하여 새 부서 번호를 생성합니다. 슬라이드의 두번째 예제에 나와 있는 대로 sequence_name.CURRVAL을 사용하여 시퀀스의 현재 값을 확인할 수 있습니다.

 

-시퀀스 값 캐시

 

• 시퀀스 값을 메모리에 캐시하면 해당 값에 빠르게 액세스할 수 있습니다 .

• 다음과 같은 경우 시퀀스 값에 간격이 발생할 수 있습니다.

– 롤백이 발생하는 경우

– 시스템 작동이 중단되는 경우

– 시퀀스가 다른 테이블에서 사용되는 경우

 

-시퀀스 수정

 

증분값, 최대값, 최소값, 순환 옵션 또는 캐시 옵션을 변경합니다.

 

ALTER SEQUENCE dept_deptid_seq

INCREMENT BY 20

MAXVALUE 999999

NOCACHE

NOCYCLE;

 

시퀀스의 MAXVALUE 한계에 도달하면 시퀀스에서 추가 값이 할당되지 않고 시퀀스가 MAXVALUE 한계를 초과했음을 나타내는 오류 메시지가 수신됩니다. ALTER SEQUENCE 문을 사용하여 시퀀스를 수정하면 계속 사용할 수 있습니다.

 

-시퀀스 수정 지침

• 시퀀스의 소유자이거나 시퀀스에 대해 ALTER 권한을 가져야 합니다.

• 다음 시퀀스 번호에만 적용됩니다.

• 다른 번호로 시퀀스를 다시 시작하려면 시퀀스를 삭제하고 다시 생성해야 합니다.

• 일부 유효성 검사가 수행됩니다.

• 시퀀스를 제거하려면 DROP 문을 사용합니다.

DROP SEQUENCE dept_deptid_seq;

 

• 시퀀스를 수정하려면 시퀀스의 소유자이거나 시퀀스에 대해 ALTER 권한을 가져야 합니다. 시퀀스를 제거하려면 시 퀀스의 소유자이거나 DROP ANY SEQUENCE 권한을 가져야 합니다.

• ALTER SEQUENCE 문은 후속 시퀀스 번호에만 적용됩니다.

• START WITH 옵션은 ALTER SEQUENCE를 사용하여 변경할 수 없습니다. 다른 번호로 시퀀스를 다시 시작하려면 시퀀스를 삭제하고 다시 생성해야 합니다.

• 일부 유효성 검사가 수행됩니다. 예를 들어, 새 MAXVALUE를 현재 시퀀스 번호보다 작게 지정할 수 없습니다.

 

-인덱스

 

데이터베이스 객체인 인덱스를 생성하면 일부 query의 성능을 향상시킬 수 있습니다. 인덱스는 유저가 Primary key 또는 Unique 제약 조건을 생성할 때 서버에 의해 자동으로 생성됩니다.

 

• 스키마 객체입니다.

• Oracle 서버에서 포인터를 사용하여 행 검색 속도를 높이는 데 사용할 수 있습니다.

• 신속한 경로 액세스 방식을 사용하여 데이터를 빠르게 찾아 디스크 I/O(입/출력)를 줄일 수 있습니다.

• 인덱스의 대상인 테이블에 독립적입니다. • Oracle 서버에서 자동으로 사용되고 유지 관리됩니다

 

Oracle 서버 인덱스는 포인터를 사용하여 행 검색 속도를 높일 수 있는 스키마 객체입니다. 명시적으로 또는 자동으로 인덱스를 생성할 수 있습니다. 열에 인덱스가 없으면 전체 테이블 스캔이 수행됩니다.

 

인덱스를 사용하면 테이블의 행에 직접 빠르게 액세스할 수 있습니다. 인덱스는 인덱스화된 경로를 사용하여 데이터를 신속하게 찾음으로써 디스크 I/O를 줄이는 데 그 목적이 있습니다. 인덱스는 Oracle 서버에서 자동으로 사용되고 유지 관리됩니다. 인덱스가 생성된 후에는 유저가 직접 조작할 필요가 없습니다

 

인덱스는 논리적, 물리적으로 인덱스의 대상인 테이블에 독립적입니다. 즉, 인덱스는 언제든지 생성하고 삭제할 수 있으며 기본 테이블이나 다른 인덱스에 영향을 미치지 않습니다.

 

-인덱스가 생성되는 방식

 

• 자동으로: 테이블 정의에서 PRIMARY KEY 또는 UNIQUE 제약 조건을 정의하면 고유 인덱스가 자동으로 생성됩니 다.

• 수동으로: 행에 액세스하는 속도를 높이기 위해 유저가 열의 비고유 인덱스를 생성할 수 있습니다.

 

두 가지 유형의 인덱스를 생성할 수 있습니다.

• 고유 인덱스: 테이블의 열이 PRIMARY KEY 또는 UNIQUE 제약 조건을 갖도록 정의하면 Oracle 서버가 자동으로 고유 인덱스를 생성합니다. 인덱스의 이름은 제약 조건에 지정된 이름입니다.

• 비고유 인덱스: 유저가 생성할 수 있는 인덱스입니다. 예를 들어, query의 조인을 위해 FOREIGN KEY 열 인덱스를 생성하여 검색 속도를 높일 수 있습니다.

 

-인덱스 생성

 

• 하나 이상의 열에 인덱스를 생성합니다.

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

 

• EMPLOYEES 테이블의 LAST_NAME 열에 대한 query 액세스 속도를 향상시킵니다.

CREATE INDEX emp_last_name_idx

ON employees(last_name);

 

인덱스가 기반으로 하는 열의 값이 고유해야 함을 나타내려면 UNIQUE를 지정합니다. 각 행을 별도로 인덱스화하지 않고 각 구분 키에 대한 비트맵을 사용하여 인덱스가 생성되도록 하려면 BITMAP을 지정합니다. 비트맵 인덱스에서는 키 값과 연관된 rowid를 비트맵으로 저장합니다.

 

-인덱스 생성 지침

 

테이블에 인덱스가 많다고 해서 query 속도가 빨라지는 것은 아닙니다. 각 DML 작업이 인덱스가 있는 테이블에 커밋되어 있다는 것은 인덱스가 갱신되어야 함을 의미합니다. 테이블과 연관된 인덱스가 많을수록 DML 작업 후에 모든 인덱스를 갱신해야 하므로 Oracle 서버의 부담이 늘어납니다.

 

따라서 다음과 같은 경우에만 인덱스를 생성해야 합니다.

• 열에 광범위한 값이 포함된 경우 • 열에 많은 널 값이 포함된 경우

• 하나 이상의 열이 WHERE 절이나 조인 조건에서 함께 자주 사용되는 경우

• 테이블이 크고 대부분의 query가 2%~4% 미만의 행을 검색할 것으로 예상되는 경우

 

고유성을 강화하려면 테이블 정의에서 고유 제약 조건을 정의해야 합니다. 그러면 고유 인덱스가 자동으로 생성됩니다.

 

-인덱스 제거

 

• DROP INDEX 명령을 사용하여 데이터 딕셔너리에서 인덱스를 제거할 수 있습니다.

DROP INDEX index;

• 데이터 딕셔너리에서 emp_last_name_idx 인덱스를 제거합니다.

DROP INDEX emp_last_name_idx;

• 인덱스를 삭제하려면 인덱스의 소유자이거나 DROP ANY INDEX 권한이 있어야 합니다.

 

인덱스는 수정할 수 없습니다. 인덱스를 변경하려면 인덱스를 삭제한 다음 다시 생성해야 합니다. DROP INDEX 문을 실행하여 데이터 딕셔너리에서 인덱스 정의를 제거합니다. 인덱스를 삭제하려면 인덱스의 소유자이거나 DROP ANY INDEX 권한이 있어야 합니다.

 

-동의어

동의어는 다른 이름으로 테이블을 호출할 수 있는 데이터베이스 객체입니다. 동의어를 생성하여 테이블에 대체 이름을 부여할 수 있습니다.

 

 

-객체의 동의어 생성

 

동의어(객체의 또 다른 이름)를 생성하면 객체에 쉽게 액세스할 수 있습니다 . 동의어를 사용하여 다음과 같은 작업을 할 수 있습니다.

 

• 다른 유저가 소유한 테이블을 쉽게 참조할 수 있습니다.

• 긴 객체 이름을 짧게 만듭니다.

CREATE [PUBLIC] SYNONYM synonym

FOR object;

PUBLIC - 모든 유저가 액세스할 수 있는 동의어를 생성합니다.

synonym - 생성할 동의어의 이름입니다.

object - 동의어를 생성할 객체를 식별합니다.

 

-동의어 생성 및 제거

 

• DEPT_SUM_VU 뷰의 짧은 이름을 생성합니다.

CREATE SYNONYM d_sum

FOR dept_sum_vu;

 

예제는 빠른 참조를 위해 DEPT_SUM_VU 뷰의 동의어를 생성합니다. 데이터베이스 관리자는 모든 유저가 액세스할 수 있는 공용(public) 동의어를 생성할 수 있습니다. 다음 예제는 Alice의 DEPARTMENTS 테이블에 대해 DEPT라는 공용(public) 동의어를 생성합니다.

 

• 동의어를 삭제합니다.

DROP SYNONYM d_sum;

 

동의어를 제거하려면 DROP SYNONYM 문을 사용합니다. 데이터베이스 관리자만 공용(public) 동의어를 삭제할 수 있습니다.