뷰(VIEW)
- 테이블 또는 다른 뷰를 기반으로 하는 논리 테이블 (가상의 테이블. 저장공간을 차지하지 않음)
- 자체적으로 데이터를 포함하지는 않는다
- 뷰를 통해 테이블의 데이터를 보거나 변경이 가능
- 테이블과 같이 조회가 가능하며,
입력, 수정, 삭제는 조건이 맞으면 가능하다.
사용목적
- 데이터 액세스를 제한하기 위해
- 복잡한 질의를 쉽게 작성하기 위해
- 데이터 독립성을 제공하기 위해
- 동일한 데이터로부터 다양한 결과를 얻기 위해
CREATE VIEW 구문
CREATE [ OR REPLACE ][ FORCE | NOFORCE ]
VIEW view [ ( 컬럼alias [ , 컬럼alias ] … ) ]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint ] --단순한 뷰는 DML통해 입력 수정 삭제 가능
| WITH READ ONLY [CONSTRAINT constraint ] --단순한 뷰여도 읽기만 가능
] ;
CREATE OR REPLACE VIEW v_emp30
AS SELECT empno, ename, deptno, sal --AS 서브쿼리
FROM emp
WHERE deptno = 30;
-->> 테이블 복사와 유사하나 객체 유형이 VIEW임.
-->> DISTINCT, JOIN, GROUP BY, 산술연산, 제약조건 등이 없는 단순한 뷰
SELECT * FROM v_emp30;
-->> 실제로 수행되는 것은 뷰 내의 서브쿼리에 있는 테이블.
--DISTINCT, JOIN, GROUP BY, 그룹함수 사용할 경우 : 뷰 삭제 불가능
-- 산술연산 사용할 경우 : 수정 불가능
-- NOT NULL 제약조건을 가진 컬럼을 뷰에서 선택하지 않을 경우 : 데이터 입력 불가능
insert into V_EMP30(EMPNO, ENAME, DEPTNO, SAL)
values(SER_NO.nextval, '장미향',40, 1000);
-->> 단순한 뷰 이므로 데이터 입력 가능
select * from V_EMP30;
--뷰는 실제로 데이터를 가지지 않음. 뷰를 통해 데이터를 INSERT할 경우 테이블로 데이터 저장.
-->> 따라서 장미향 데이터 조회되지 않음
--V_EMP30 뷰는 30번 부서의 사원 데이터만 가져온 뷰임. 여기에 40번 부서인 장미향 사원을 삽입하는것은 논리상 맞지 않다.
-->> 제약조건을 통해 입력 불가능하도록 변경 필요함.
SELECT empno, ename, deptno, sal
FROM emp;
--EMP 테이블에 장미향 데이터 삽입되어있음.
CREATE OR REPLACE VIEW v_emp30
--OR REPLACE라는 옵션이 있으므로 뷰를 DROP하지 않아도 됨. (아니면 DROP VIEW, CREATE VIEW로 따로 기술해야 함)
AS SELECT empno, ename, deptno, sal
FROM emp
WHERE deptno = 30
WITH CHECK OPTION; --OPTION 따로 지정하지 않았으므로 WHERE절의 조건이 OPTION이 된다.
INSERT INTO v_emp30(empno, ename, deptno, sal)
VALUES(ser_no.NEXTVAL, '백장미',40, 1000);
--40번 부서이므로 WITH CHECK OPTION 조건에 위배
INSERT INTO v_emp30(empno, ename, deptno, sal)
VALUES(ser_no.NEXTVAL, '백장미',30, 1000);
SELECT * FROM v_emp30;
--30번 부서의 백장미 정상 삽입됨
CREATE OR REPLACE VIEW v_emp30
AS SELECT empno, ename, deptno, sal
FROM emp
WHERE deptno = 30
WITH READ ONLY; --입력 수정 삭제 안되고 오로지 조회만 가능. 읽기 전용의 뷰
INSERT INTO v_emp30(empno, ename, deptno, sal)
VALUES(ser_no.NEXTVAL, '진달래',30, 1000);
-- 읽기 전용 뷰에서는 DML작업 수행할 수 없다는 오류 발생
SELECT * FROM v_emp30;
-- 진달래 삽입되지 않고 조회만 됨
시퀀스 (SEQUENCE)
- 고유번호를 자동으로 생성
- 공유 가능한 객체
- 일반적으로 기본 키 값을 생성하는데 사용
- 응용 프로그램 코드를 대체
- 시퀀스 값을 메모리에 캐시 하면 액세스 효율이 높아진다.
CREATE SEQUENCE 구문
CREATE SEQUENCE sequence
[START WITH n] --생략하면 디폴트 1
[INCREMENT BY n] --생략하면 디폴트는 1
[ { MAXVALUE n | NOMAXVALUE} ][ { MINVALUE n | NOMINVALUE } ]
[ {CYCLE | NOCYCLE } ] --MAXVALUE에 도달했을 때 처음부터 다시 만듦. PK를 사용하려면 NOCYCLE 기술
[ { CACHE n | NOCACHE } ] --CACHE를 미리 생성
;
CREATE SEQUENCE t_emp_id_seq
START WITH 101 --101번부터 시작
INCREMENT BY 1 --1씩 증가
;NEXTVAL은 사용 가능한 다음 시퀀스값을 반환
• 참조될 때마다(서로 다른 사용자일지라도) 고유한 값을 반환.
• CURRVAL은 현재 시퀀스 값을 반환.
• CURRVAL이 값을 포함하려면 먼저 해당 시퀀스에 대해 NEXTVAL이 수행되어야 한다.
INDEX
- 스키마 객체이다.
- Oracle Server에서 포인터를 사용하여 행의 검색속도를 높이기 위해 사용.
- 데이터 위치를 빠르게 찾는 신속한 경로 액세스 방법을 사용하여 디스크 I/O를 줄여준다.
- 인덱스는 테이블과 독립되어 존재.
- Oracle Server에 의해 사용되며, 자동으로 유지 관리된다.
인덱스가 생성되는 방식
- 자동으로 : 테이블 정의에서 PRIMARY KEY 또는 UNIQUE 제약조건을 선언하면 UNIQUE INDEX 가 자동으로 생성.
- 수동으로 : CREATE INDEX 명령을 통해 사용자가 직접 인덱스를 생성.
인덱스 생성 지침
-
인덱스를 생성하는 경우
: 열에 광범위한 값이 포함된 경우
: 열에 많은 널 값이 포함된 경우 (인덱스는 NULL이 아닌 값에 대해서만 생성되므로 데이터 조회 효율이 향상된다.)
: 하나 이상의 열이 where 절이 조인 조건에서 함께 자주 사용되는 경우
: 테이블이 크고 대부분의 query 가 테이블에서 2 ~ 4% 미만의 행을 검색할 것으로 예상되는
경우 -
인덱스를 생성하지 않는 경우
: 열이 query 에서 조건으로 사용되지 않는 경우 (인덱스가 저장공간만 차지하고 사용되지 않음)
: 테이블이 작거나 대부분의 query 가 테이블에서 2 ~ 4% 이상의 행을 검색할 것으로 예상되
는 경우
: 테이블이 자주 갱신되는 경우
: 인덱스화된 열이 표현식의 일부로 참조되는 경우 (컬럼이 변경되면 인덱스가 사용되지 않음.
--> 컬럼의 분포도가 높은경우, 인덱스를 안씌우기 위해 RTRIM() 등으로 일부러 컬럼을 변경시키기도 함.)
CREATE INDEX index
ON table (column[, column] . . .) ;
두 개 이상의 컬럼을 합쳐서 컬럼을 생성하는 것은 결합 인덱스라고 한다.
