#6_140621_DB OBJECT

# DB OBJECT

l db object 5가지

1.     table

2.     view

3.     sequence

4.     synonym

5.     index

l view를 사용하는 이유 2가지

- 데이터의 보안을 위해서(특정 컬럼을 감추기 위해서)

- 복잡한 쿼리를 간단하게 하기 위해서

l view 생성

CREATE VIEW emp550

AS

SELECT empno,ename,sal,deptno

FROM EMP;

1. view

문제258. emp550 view의 king의 월급을 0로 변경하면 실제 테이블인 emp 테이블도 수정될까?

CREATE VIEW emp550

AS

SELECT empno,ename,sal,deptno

FROM EMP;

UPDATE emp550

SET sal=0

WHERE ename='KING';

è  가능하다

문제261. DALLAS에서 근무하는 사원들의 이름과 월급과 부서위치를 출력하는 VIEW를 생성하시오(VIEW 이름 : emp623)

CREATE VIEW emp623

as

SELECT e.ename, e.sal, d.loc

FROM EMP e, DEPT d

WHERE e.deptno=d.deptno AND d.loc='DALLAS';

SELECT * FROM emp623;

문제262. emp623 view의 이름이 scott 인 사원의 부서위치를 BOSTON 으로 변경하시오

UPDATE emp623

SET loc='BOSTON'

WHERE ename='SCOTT';

l 복합뷰이기 때문에 오류발생

문제263. 직업, 직업별 토탈월급을 출력하는데 view를 생성하시오(view 이름 : emp624)

CREATE VIEW emp624

AS

SELECT job, SUM(sal) 토탈월급

FROM EMP

GROUP BY job;

l 그룹함수가 포함되어 있으면 복합뷰이다.

문제264. 위에서 생성 emp624뷰를 수정하는데 직업이 SALESMAN의 토탈을 4500으로 수정하시오

UPDATE emp624

SET 토탈월급=4500

WHERE job='SALESMAN';

l 수정 불가능

2. CREATE OR REPLACE

문제265. 위에서 생성한 emp624 view를 수정하는데 부서번호, 부서번호별 인원수를 출력하는 쿼리로 수정하시오

CREATE OR REPLACE VIEW emp624

AS

SELECT deptno, COUNT(*) 인원수

FROM EMP

group BY deptno;

l CREATE OR REPLACE : 없으면 만들고 있으면 수정해라

3. WITH CHECK OPTION,  WITH READ ONLY

문제266. 부서번호가 20번인 사원들의 사원번호, 이름과, 월급, 부서번호를 출력하는 뷰를 생성하는데 부서번호 만큼은 다른 부서번호로 수정할 수 없도록 하시오

CREATE VIEW emp394

AS

SELECT empno, ename, sal, deptno

FROM EMP

WHERE deptno=20

WITH CHECK OPTION;

UPDATE emp394

SET deptno=30

WHERE ename='SCOTT';

l with check option을 사용하면 where 절에 기술한 조건을 다른 조건으로 수정할 수 없다.

l with read only는 모든 컬럼이 다 수정안됨

4. 시퀀스(SEQUENCE)

l 시퀀스(sequence) : 번호를 생성하는 db object

l 시퀀스 생성

CREATE SEQUENCE seq1

start WITH 1

INCREMENT BY 1

MAXVALUE 100;

SELECT SEQ1.NEXTVAL FROM dual;

start WITH : 시작번호

INCREMENT BY : 증가번호

MAXVALUE : 최대번호

문제267. 아래의 테이블을 생성하고 생성한 테이블의 empno에 숫자 데이터를 1~200번 까지 입력하는데1분 내로 하시오

CREATE TABLE emp552

(empno NUMBER(10));

CREATE SEQUENCE seq2

START WITH 1

INCREMENT BY 1

MAXVALUE 1000;

INSERT INTO emp552 VALUES(seq2.nextval);

BEGIN

FOR i IN 1..200 LOOP

INSERT INTO emp552 VALUES(seq2.nextval);

END LOOP;

END;

l PLSQL : 수작업 없이 한번에 가능

문제268. 위에서 생성한 seq2 시퀀스의 maxvalue를 3000으로 수정하시오

ALTER SEQUENCE seq2

MAXVALUE 3000;

SELECT * FROM user_sequences;

l start with 는 변경 불가능

5. 동의어(SYNONYM)

일단 아래와 같이를 생성한다.

l 유저생성

CREATE USER smith

IDENTIFIED BY tiger;  (암호:tiger)

GRANT CONNECT, resource TO smith;

GRANT SELECT ON EMP TO smith  (select 권한을 부여하겠다 emp 테이블을 smith에게)

위와같이 생성후 smith에게 connect와 resource 권한과
select 권한을 넘긴다

그러면 아래와 같이 scott.emp 길게 써줘야 접근이 가능하다

l CREATE PUBLIC SYNONYM EMP FOR SCOTT.EMP;

- 누구든지 접근가능하도록 public 으로 생성하고 scott.emp를 emp라고 부르겠다

- dba들이 처음에 하는 작업

- DROP PUBLIC SYNONYM EMP;

그러나 CREATE PUBLIC 문을 이용하면 간단하게 scott.emp를 emp로 단순하게 접근 가능하다

6. 인덱스

문제292. 사원테이블에 sal에 인덱스를 생성하시오

CREATE INDEX emp_sal

ON EMP(sal);

문제294. 월급이 3000인 사원의 이름과 월급을 출력하고 실행계획을 확인하시오

SELECT ename, sal

FROM EMP

WHERE sal=3000;

문제295. 인덱스를 삭제하고 다시 실행계획을 보시오

DROP INDEX emp_sal;

문제296. 연봉(sal*12)이 36000인 사원의 이름과 연봉을 출력하시오

CREATE INDEX emp_sal

ON EMP(sal);

SELECT ename, sal*12 연봉

FROM EMP

WHERE sal*12=36000;

l where 절에서 인덱스 컬럼을 가공하면 index access가 안되고 full table scan 하게 된다.

l 튜닝후

SELECT ename, sal*12 연봉

FROM EMP

WHERE sal=36000/12;

문제297. 사원 이름에 index를 생성하고 이름이 SCOTT인 사원의 이름과 월급과 직업을 조회한후 실행계획을 확인하시오

CREATE INDEX emp_ename

ON EMP(ename);

SELECT ename, sal, job

FROM EMP

WHERE ename='SCOTT';

l 튜닝전

SELECT ename, sal, job

FROM EMP

WHERE Upper(ename)='SCOTT';

l 튜닝후

CREATE INDEX emp_ename_f

ON EMP(UPPER(ename));

SELECT ename, sal, job

FROM EMP

WHERE Upper(ename)='SCOTT';