since 2019.

1 minute read

Updated:

복잡한 쿼리를 단순하게 하기(VIEW)

  • 테이블의 데이터 중에서 일부의 데이터만 바라볼 수 있게 해준다.
  • 보안상의 이유로 민감한 데이터를 감추고 일부 데이터만 보여주고 싶을 때 사용한다. 테이블처럼 데이터를 가지고 있지 않고, 바라만 보는 용도다.

예제) 직업이 SALESMAN인 사원들의 사원번호, 이름, 월급, 직업, 부서번호만 보이도록 VIEW를 만드세요

create view emp_view
as
select empno, ename, sal, job, deptno
from emp
where job = 'SALESMAN';

SELECT * FROM emp_view;
  • 복잡한 쿼리문을 간단하게 검색하고 싶을 때도 view를 쓴다.

예제) 사원테이블에서 부서번호와 부서번호별 평균월급만 바라볼 수 있는 VIEW를 만드세요

create view emp_view2
as
select deptno, round(avg(sal)) as avgsal 
from emp
group by deptno;
  • group함수로 view를 만들 떄는 컬럼별칭을 써야 만들어진다.

데이터 검색 속도를 높이기(INDEX)

  • index란 데이터 검색 속도를 높이는 데이터 객체이다. 참고로 객체에는 table, view, index가 있다.

  • 데이터가 대용량인 환경에서도 빠르게 데이터를 검색하려면 index가 필요하다. 목차와 같은 역할이다.

  • sql문이 너무 느릴 때 full scan인지를 확인하려면 실행계획을 확인해보면 된다.
explain plan for
select ename, sal
from emp
where sal = 3000;

select * from table(dbms_xplan.display)

예제) 월급이 3000인 사원의 이름과 월급을 인덱스를 통해서 빠르게 검색 하세요.

--인덱스 생성
create index emp_sal
on emp(sal);

--실행계획 확인
explain plan for
select ename, sal
from emp
where sal = 3000;

select * from table(dbms_xplan.display);
  • rowid는 행의 물리적인 주소로 테이블과 인덱스에 모두 존재한다. 파일 번호 + 블럭 번호 + row 번호로 이루어져 있다.
--인덱스를 확인하는 쿼리문
select sal, rowid
from emp
where sal >=0;

절대로 중복되지 않는 번호 만들기(SEQUENCE)

  • 번호를 중복없이 순서대로 생성하는 번호표 기계와 같다.
  • ex) 사원번호, 주식거래번호

예제) 사원번호에 번호를 입력할 때 데이터가 중복되지 않고 순서대로 입력되게 하시오.

--시퀀스 생성
create sequence seq3
start with 1 --몇 번부터
maxvalue 100--최대 몇번까지
increment by 1 --증가치
nocycle;

--테이블 생성
create table emp500
(empno number(10),
ename varchar2(10));

--데이터 삽입
insert into emp500
values (seq3.nextval, 'king');

-----------
--참고 : 번호 생성
select seq2.nextval
from dual;

예제2) dept 테이블에 부서번호를 50번부터 입력하고 10씩 증가되는 시퀀스를 생성하시오 (시퀀스 이름은 dept_seq1)

create sequence dept_seq1
start with 50
increment by 10;

insert into dept(deptno, dname, loc)
values (dept_seq1.nextval, 'transfer', 'seoul');

You may also enjoy