[SQL] DB 스터디 - 10

객체(VIEW, SEQUENCE, SYNONYM, INDEX)

1. VIEW(뷰)

- CREATE VIEW view_name AS SELECT 명령문

- SQL의 SELECT명령문은 다른 SQL명령문의 Subquery로 사용 되기도 함

-> 일반 파일의 바로가기 기능과 같음

- 테이블은 데이터를 실제로 가지고 있는 객체 임

- VIEW는 SELECT명령문에 VIEW를 붙혀서 보는 것

- 특정 부분만 한정해서 결과를 볼 수 있도록 만드는 것을 VIEW라고 함

-> 테이블 복사와 비슷 함

-> 테이블 복사와 다른 점은 VIEW를 만들 때 사용한 SELECT명령문을 가지고 있음

-> 따라서 원본 데이터를 수정 시킬 수 있게 됨

-> VIEW를 업데이트 시키는 것을 다르게 보면 기존 테이블 업데이트에 Subquery를 넣어서 실행시키는 것

-> 즉, VIEW를 업데이트 시키면 원본 테이블에 업데이트를 시키는 것과 같은 의미

- VIEW는 VIEW를 만들 때 사용한 SELECT문을 텍스트 형태로 저장 하고 있는 것과 같은 것

-> 복잡한 쿼리를 단순화 시킴

-> 보안적인 측면에서 원본 테이블의 SELECT권한을 제한할 수 있음

- VIEW는 DML이 가능한 VIEW가 있고, DML이 불가능한 VIEW가 있음

-> JOIN문장으로 만든 VIEW는 될 수도 있고 안될 수도 있음

-> 함수식이 있는 경우(계산 값이 있는 경우)도 동일

- VIEW를 수정할 때 CREATE와 VIEW 사이에 OR REPLACE 를 집어넣어서 생성하면 됨

-> CREATE OR REPLACE VIEW name AS SELECT...

- VIEW에서 값을 수정하면 해당 VIEW에서 사라지는 행도 존재 함

-> WHERE절에서 조건에 맞지 않는 정보는 VIEW상에서 나오지 않음

- 원본 데이터를 수정하면 VIEW에서도 수정 된 값이 출력 됨

- 데이터 저장 X

예)

->

-- VIEW에서 empno가 7839인 사원의 급여(sal)를 10% 인상 시키는 것

UPDATE empv10

SET sal = sal * 1.1

WHERE empno = 7839;

-- VIEW에서 UPDATE가 이루어 지는 과정

UPDATE (

    SELECT empno, ename, sal, deptno

    FROM emp

    WHERE deptno = 10 )

SET sal = sal * 1.1

WHERE empno = 7839;

---

2. VIEW의 제약 조건

- WITH CHECK OPTION

-> WHERE절의 조건에 있는 부분의 컬럼의 행을 수정할 때 불가능 하게 만듬

- WITH READ ONLY

-> 읽기 기능만 허용

-> 모든 DML거부

예)

->

-- department_id가 20이 아닌 행을 INSERT하거나 

-- VIEW에 있는 임의의 행에서 부서 번호를 UPDATE하려는 시도를 거부

CREATE OR REPLACE VIEW empvu20

AS 

SELECT *

FROM employees

WHERE department_id = 20

WITH CHECK OPTION CONSTRAINT empvu20_ck ;

---

3. SEQUENCE(시퀀스)

- 숫자 값을 생성

- 순차 적으로 증가하는 값에 적용

- 중복되지 않는 번호를 자동으로 만드는 것

- CREATE  SEQUENCE empno_sq START WITH 8000;

- 시퀀스는 한 번 사용 되면 해당 번호는 다시 사용하지 않음

- 'seq_name.NEXTVAL' 을 INSERT문에 사용하여 시작된 8000번의 다음 번호를 리턴시켜서 INSERT 시킴

- 'seq_name.CURRVAL' 을 이용해 마지막으로 사용한 번호를 리턴 받음

- PRIMARY KEY로 사용할 수 있음

- 데이터 저장 X

예)

->

-- SEQUENCE의 생성 방법

CREATE SEQUENCE sequence

[INCREMENT BY n] 

-- 값을 n씩 증가(-값도 사용 가능)

[START WITH n]

-- 시작 되는 값

[{MAXVALUE n | NOMAXVALUE}]

-- 최대 값 설정

[{MINVALUE n | NOMINVALUE}]

-- 최소 값 설정

[{CYCLE | NOCYCLE}]

-- MAXVALUE에 도달이 되는 순간 MINVALUE로 다시 시작 여부

[{CACHE n | NOCACHE}];

-- 캐시에 올려 둘 번호 수의 크기

-- dept_deptid_sql 시퀀스를 생성

CREATE SEQUENCE dept_deptid_seq

INCREMENT BY 10

-- 올라가는 값을 10씩 증가

START WITH 120

-- 시작 번호 120

MAXVALUE 9999

-- 최대 값은 9999

NOCACHE

-- 캐시에는 20번씩

NOCYCLE;

-- 최대값에 도달하면 더이상 증가 X

-- 시퀀스의 수정 방법

ALTER SEQUENCE dept_deptid_seq

INCREMENT BY 20

-- 올라가는 값을 20으로 변경

MAXVALUE 999999

-- 최대 값을 999999로 변경 

NOCACHE

-- 캐시는 20번씩

NOCYCLE;

-- 순환 X

-- START WITH는 수정 불가능

---

4. SEQUENCE 값 캐시

- 정상적인 방법이 아닌 비 정상적인 방법으로 DB가 종료되면 번호가 누락될 수 있음

-> 누락되는 번호를 거부 한다면 시퀀스를 사용할 수 없음

- 시퀀스의 번호는 사용하면 그것으로 끝

- ROLLBACK이 되는 경우 번호가 누락 될 수 있음

- 데이터 저장 X

---

5. SYNONYM(동의어)

- CREATE SYNONYM name FOR user.table

-> user.table을 name으로 별칭을 만듬

- SELECT * FROM name = SELECT * FROM user.table 와 같은 의미

-> name = user.table와 같음

- 객체를 가리키는 이름을 생성한다는 의미

- 다른 사용자가 소유한 테이블을 참조할 때 사용 함

- 데이터 저장 X

---

6. INDEX(인덱스)

- 테이블 상에서 하나의 행을 찾을 때 가장 빠른 방법은 실제 주소값으로 조건을 주면 가장 빠름

- SELECT절에 ROWID를 함깨 출력 시키면 해당 행의 물리 주소를 알 수 있음

- CREATE INDEX index_name ON table(column);

-> index_name이라는 INDEX를 생성 

-> table의 column의 주소를 인덱싱 시켜서 저장

-> 정렬 상태를 유지하는 객체

- 컬럼의 행에 ROWID를 가지고 있음

- 실행 계획은 INDEX를 먼저 샐행 시킴

- 검색 속도 증가

- WHERE절에서 컬럼이 가공되면 INDEX를 포기하고 테이블을 FULL스캔 하게 됨

- WHERE절에서 조건을 사용하는 종류에 따라서 INDEX의 사용 유무가 다름

- 데이터 저장 O

예)

->

-- T1 테이블 생성

CREATE TABLE T1

AS SELECT * FROM DBA_OBJECTS;

-- T1 테이블의 데이터를 2배로 증가

INSERT INTO T1 

SELECT * FROM T1 ; 

-- 테이블 내에서 데이터 검색

SELECT * 

FROM T1 A 

WHERE 8 < (SELECT COUNT(*)

           FROM T1 B 

           WHERE B.DATA_OBJECT_ID = A.DATA_OBJECT_ID) ; 

-- 7.416초 소요

-- T1의 INDEX 생성

CREATE INDEX T1_IX ON T1(DATA_OBJECT_ID) ; 

-- 재검색

SELECT * 

FROM T1 A 

WHERE 8 < (SELECT COUNT(*)

           FROM T1 B 

           WHERE B.DATA_OBJECT_ID = A.DATA_OBJECT_ID) ; 

-- 0.018초 소요

---

7. INDEX가 생성되는 방식

- 자동

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

- 수동

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