![[Oracle] View, Index, Sequence](https://img1.daumcdn.net/thumb/R750x0/?scode=mtistory2&fname=https%3A%2F%2Ft1.daumcdn.net%2Fcfile%2Ftistory%2F99350F3F5C7BD5AB0E)
8. View, Index, Sequence
8.1 뷰(view)
8.1.1 정의
테이블에 대한 가상의 테이블로써 테이블처럼 직접 데이터를 소유하지 않고 검색시에 이용할 수 있도록 정보를 담고 있는 객체. 테이블 정보의 부분집합
8.1.2 사용 목적
8.1.2.1 테이블에 대한 보안 기능을 설정해야 하는 경우
8.1.2.2 복잡하며 자주 사용하는 질의 SQL 문을 보다 쉽고 간단하게 사용해야 하는 경우
8.1.3 뷰 생성 권한 부여
cmd> sqlplus / as sysdba;
SQL > grant create view to 사용자;
8.1.4 뷰 생성
(형식)
create or replace view [force | noforce] 뷰이름 (컬럼리스트)
as [쿼리]
- replace : 이미 존재하는 뷰의 내용을 수정함
create or replace view test_view as
select empno, ename, job, sal, deptno
from emp;
select * from test_view;
8.1.5 뷰 수정하기
CREATE or REPLACE
8.1.6 뷰 삭제하기
DROP VIEW 뷰이름
drop view test_emp;
8.1.7 뷰 실습소스
--뷰 생성(두번째거부터는 수정)
CREATE OR REPLACE VIEW test_view
as
select empno, ename, e.deptno, dname
from emp e, dept d -- 조인대상 테이블
where e.deptno=d.deptno; -- 조인 조건
-- 생성된 뷰는 테이블처럼 사용 가능
select * from test_view;
select * from (select empno, ename, e.deptno, dname
from emp e, dept d
where e.deptno=d.deptno);
-- 테이블, 뷰 목록 확인
SELECT * FROM tab;
-- 뷰의 세부 정보 확인(데이터 사전)
SELECT * FROM user_views;
-- 뷰 생성
create or replace view emp_v
as
select empno, ename, hiredate, sal from emp
with read only; -- 읽기 전용 뷰
-- 실행
SELECT * FROM Emp_V;
-- 뷰에 레코드 입력(가능하나 권장하지 않음)
-- 뷰의 주용도는 select
-- 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.
-- "cannot perform a DML operation on a read-only view"
INSERT INTO Emp_V VALUES (9867, 'park', '2017-02-01', 500);
SELECT * FROM Emp_V;
8.2 색인(index)
8.2.1 정의
테이블의 데이터를 보다 빠르게 검색할 수 있도록 지원해주는 객체
8.2.2 구성요소
rowid, 색인 컬럼값
8.2.3 생성
CREATE INDEX 인덱스명 on 테이블명 (컬럼명...);
- Primary Key, Unique 제약조건을 만들면 해당 인덱스가 자동으로 생성됨
CREATE INDEX c_emp_name_idx ON c_emp(name);
8.2.4 삭제
DROP INDEX 인덱스명;
DROP INDEX c_emp_name_idx;
8.2.5 인덱스 실습
-- parsing(명령어 분석) -> 실행계획 수립(옵티마이저) -> 실행
-- sql developer : F10(실행계획 보기)
-- toad : Ctrl + E(실행계획 보기)
-- full scan 모든 레코드를 검사
-- 인덱스를 사용한 검사(by index rowid)
-- index unique scan : 유일한 값
-- index range scan : 유일하지 않은 값
select empno, ename from emp where empno=7900;
select empno, ename from emp
where ename='송기성';
-- 인덱스 추가
create index emp_ename_idx on emp(ename);
-- 인덱스 제거
drop index emp_ename_idx;
-- 인덱스 테스트를 위한 테이블 생성
create table emp3 (
no number,
name varchar2(10),
sal number
);
....
end;
/
-- Ctrl+E 실행계획 확인
-- 인덱스를 사용하지 않을 경우 : table access full, cost:894
select * from emp3 where name='shin691' and sal > 200;
-- 인덱스 추가
-- create index 인덱스이름 on 테이블(컬럼)
create index emp_name_idx on emp3(name, sal);
-- index range scan, cost:11
select * from emp3 where name='shin691' and sal > 200;
-- 인덱스 정보 확인
-- unique index : primary key, unique 제약조건 컬럼에 적용
-- nonunique index
select * from user_indexes where table_name='EMP3';
-- 인덱스 제거
drop index emp_name_idx;
-- primary key는 인덱스가 자동으로 생성됨
create table emp4 (
no number primary key,
name varchar2(10),
sal number
);
8.3 시퀀스(sequence)
8.3.1 정의
연속적인 숫자값을 자동으로 증감시키는 숫자를 발생시키는 객체
8.3.2 생성
create sequence 시퀀스이름
[increment by 숫자]
[start with 숫자]
[maxvalue 숫자]
[minvalue 숫자]
[cycle | nocycle] -- 일련번호 순환여부
[cache | nocache] -- 빠른 처리를 위해 시퀀스의 값을 메모리에 저장
create sequence c_emp_seq
increment by 1
start with 103
maxvalue 1000000000
nocache
nocycle;
8.3.3 시퀀스 호출 함수(주의: 시퀀스 생성 후 nextval을 호출해야 시퀀스에 초기 값이 설정됨)
가) nextval : 다음값을 반환함
select c_emp_seq.nextval from dual;
나) currval : 현재값을 반환함
select c_emp_seq.currval from dual;
8.3.4 사용 예
INSERT INTO c_emp values(c_emp_seq.nextval, 'aaa', 1000, '3429-001', 10);
-- 서브쿼리를 이용하여 id 필드에 값을 설정
-- nvl(A, A가 null일때의 값)
SELECT NVL(max(id)+1, 1) from c_emp;
INSERT INTO c_emp values
((select nvl(max(id)+1, 1) from c_emp), 'test', 3000, '3429-0000', 10);
8.3.5 시퀀스 실습 소스
-- 시퀀스 생성
-- 200부터 시작, 1씩 증가, 최대값 100000, 캐쉬 사용 안함, 순환 안함
create sequence c_emp_seq
start with 200
increment by 1
-- maxvalue 100000
nomaxvalue
nocache
nocycle;
-- 시퀀스.nextval : 다음 번호 발급
-- 시퀀스.currval : 현재 번호 확인
-- dual : 가상 테이블
select c_emp_seq.nextval from dual;
select c_emp_seq.currval from dual;
select * from c_emp;
delete from c_emp;
-- 시퀀스를 이용하여 사번 자동 부여
insert into c_emp values
(c_emp_seq.nextval, 'kim', 3000, '010-2222-3333', 10);
-- 서브쿼리를 이용한 번호 발급
drop table test;
create table test (