210610_1(데이터베이스6 뷰VIEW, 시퀀스SEQUENCE, 인덱스 INDEX)

(6/9)뷰 view

권한 부여해야 사용가능

권한주기

conn system/oracle          --- 관리자로전환

grant create view to scott; --- 권한부여

 

뷰란 물리적인 테이블을 기반으로 생성한 가상의 논리적인 테이블이다. -- DQL, DML 가능

 

특징

• 복잡한 쿼리를 축약시킬수있다.
• 보안 측면에서 유리하다

기본형 : create view 뷰이름 as select ~~~ --만들기

create or replace view 뷰이름 as select ~~~ --없으면만들고 있으면 수정한다.

 

1. emp를 복사하여 emp_copy를 생성하시오

create table emp_copy as select * from emp;

 

2. emp_copy에서 부서번호가 30인 직원의 사번, 이름, 부서번호를 출력하라

select empno, ename, deptno from emp_copy where deptno=30;

 

3. 위의 내용을 실행하는 emp_view30을 생성하시오

create or replace view emp_view30 as select empno, ename, deptno from emp_copy where deptno=30;

--테이블 복사하는것과 똑같다

 

4. emp_view30을 실행하시오 --------쿼리단축의 의미

select * from emp_view30;

 

5. 뷰의 정보를 확인하시오 - user_views

desc user_views

select view_name, TEXT_LENGTH, TEXT, VIEW_TYPE from user_views;

.

 ----------------------------------------------------- -------- ------------------------------------
 VIEW_NAME             --뷰이름                                NOT NULL VARCHAR2(30)
 TEXT_LENGTH           --내용길이                                         NUMBER
 TEXT                       --내용                                    LONG
 TYPE_TEXT_LENGTH                                               NUMBER
 TYPE_TEXT                                                      VARCHAR2(4000)
 OID_TEXT_LENGTH                                                NUMBER
 OID_TEXT                                                       VARCHAR2(4000)
 VIEW_TYPE_OWNER                                                VARCHAR2(30)
 VIEW_TYPE                  --뷰형태                                    VARCHAR2(30)
 SUPERVIEW_NAME                                                 VARCHAR2(30)

 

 

실습

1. emp_copy에서 이름, 사번, 급여, 커미션을 선택하여 emp_account 뷰를 생성하고

create or replace view emp_account as select ename, empno, sal, comm from emp_copy; 

 

2. emp_copy에서 이름, 사번, 직책, 매니저을 선택하여 emp_insa 뷰를 생성하고 

create or_replace view emp_insa as select ename, empno, job, mgr from emp_copy; 

 

=====>보안

필요치 않는 정보에 접근을 방지하는 효과가 있다.

-- 나중에 emp_account는 회계과에서만 접속이 가능하도록 : grant~~

-- 나중에 emp_insa는 인사과에서만 접속이가능하도록  : grant~~ 보안을 강화한다

 

뷰실행

select * from emp_account;

select * from emp_insa;

 

3. 뷰 정보를 확인하시오

@views

 

VIEW_NAME                                                    TEXT_LENGTH

------------------------------------------------------------ -----------

TEXT

--------------------------------------------------------------------------------

VIEW_TYPE

------------------------------------------------------------

EMP_VIEW30                                                            57

select empno, ename, deptno from emp_copy where deptno=30

EMP_ACCOUNT                                                           44

select ename, empno, sal, comm from emp_copy

EMP_INSA                                                              43

select ename, empno, job, mgr from emp_copy

 

---

(6/10)뷰에 데이터 입력

SQL> select * from emp_view30;
     EMPNO ENAME                    DEPTNO
---------- -------------------- ----------
      7499 ALLEN                        30
      7521 WARD                         30
      7654 MARTIN                       30
      7698 BLAKE                        30
      7844 TURNER                       30
      7900 JAMES                        30

 

1. (1111,'HONGGIL',30) 을 입력하시오

INSERT INTO EMP_VIEW30 VALUES (1111,'HONGGIL',30);

2. 확인

select * from emp_view30;

3. 원본확인

select * from emp_copy

--결과 : 뷰에 입력했던내용이 원본에 들어간다.

 

---

단순뷰와 컬럼 별칭

단순뷰 : 하나의 물리적 테이블을 기반으로 생성한 뷰.

컬럼별칭 : 뷰를 생성할때 선택컬럼에 함수가 있으면 별칭을 반드시 부여한다.

               왜냐하면, 컬럼명이 없으면 뷰생성이 안된다.

 create view 이름 (별칭,,,,,,) as select

 

1.emp_copy에서 부서번호가 10인 직원의 사번, 이름, 부서번호를 선택하여 emp_view10을 생성하고 컬럼은 한글로 사번, 이름, 부서번호 로 생성하시오

create or replace view emp_view10 (사번,이름,부서번호) as select empno, ename, deptno from emp_copy where deptno=10;

 

2.emp_copy에서 부서별 급여합계를 볼수있는 dept_view뷰를 생성하라

create view 이름 as select 함수() as '별칭'~

create or replace view dept_view as select deptno, sum(sal) sum_sal from emp_copy group by deptno;

 

3.dept_view에 (40,3000)을 입력해보자 결과는?

insert into dept_view values (40, 3000);

1행에 오류:
ORA-01733: 가상 열은 사용할 수 없습니다 <== 함수로 만들어진 뷰 컬럼은 입력이 불가하다.

---

복합 뷰

두개이상의 테이블, 즉 조인문 등을 기반으로 생성된 뷰

 

4. emp_copy와 dept_copy (복제필요함) 을 기반으로 사번, 이름, 부서명을 출력하는 뷰 (emp_view_join)를 생성하시오

create view emp_view_join as select empno, ename, dname from emp_copy, dept_copy

 where emp_copy.deptno = dept_copy.deptno;

 

5. emp_view_join 확인하시오

select * from emp_view_join;

 

뷰의 제거

drop view 뷰이름

 

뷰의 수정

 create or replace : 없으면 생성, 있으면 수정

1. emp_copy 로부터 부서별 합계 평균을 출력하는 dept_view 생성하시오

create or replace view dept_view as select deptno, avg(sal) avg_sal, sum(sal) sum_sal from emp_copy group by deptno;

 

2.조회

select * from dept_view;

 

    DEPTNO    AVG_SAL    SUM_SAL
---------- ---------- ----------
        30 1566.66667       9400
        20 2258.33333       6775
        10 2916.66667       8750

테이블이 없어도 뷰를 생성할수있을까.

1. employee 테이블로부터 정보를 출력하는 뷰 view_employee를 생성해보시오. 결과는?

create or replace view view_employss as select * from employee;

1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다

 

뷰의 테이블 없어도 강제 생성 : force

create or replace force view view_employee as select * from employee;

경고: 컴파일 오류와 함께 뷰가 생성되었습니다. -- 생성은 가능, 사용은 불가

뷰의 테이블 없이 강제 생성불가 : noforce

create or replace noforce view view_employee as select * from employee;

1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다 -- 디폴트와 똑같은 결과

 

1.emp_copy에서 부서번호가 20인 직원의 사번, 이름, 부서번호를 출력하는 뷰 emp_view20

create or replace view emp_view20 as select empno, ename, deptno from emp_copy where deptno=20;

 

2. emp_view20에서사번이 7369인 직원의 이름을 'HONG'으로 수정하시오.

update emp_view20 set ename='HONG' where empno=7369; -- update는 뷰에도 똑같이 적용된다

 

3.확인

select * from emp_view20; 

 

4. 사번이 7369인 직원의 부서를 40으로 변경하시오

update emp_view20 set deptno=40  where empno=7369;

 

5. 확인

select * from emp_view20;

 

6.emp_copy 확인

select * from emp_copy; -- 원본에는 수정된 것이 존재한다

 

조건 위배시 변경불가 view : with check option

create or replace view emp_view20 as select empno, ename, deptno

from emp_copy where deptno=20 with check option;

 

update emp_view20 set deptno=40  where empno=7902;

1행에 오류:
ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다 --바꿀수없다.

읽기만 가능한 view : with read only

DML불가. select만 가능.  뷰의 조건에 맞아도 변경불가

create or replace view emp_view20 as select empno, ename, deptno from emp_copy where deptno=20 with read only;

update emp_view20 set ename='LEE'  where deptno=20 ;

1행에 오류:
ORA-01733: 가상 열은 사용할 수 없습니다

인라인 뷰 : 페이징?

from 절에 있는 서브쿼리를 인라인 뷰라고 한다

 

1. emp_copy 에서 부서별 가장 최근에 입사한 직원보다 이전에 입사한 직원의 사번 , 이름, 입사일자, 부서번호, 입사일자, 최근입사일자를 출력하시오.

select empno, ename, s.deptno, hiredate, maxhire

from emp_copy s, (select deptno, max(hiredate) maxhire from emp_copy group by deptno) t

where s.deptno = t.deptno and s.hiredate <  t.maxhire ; -- where 조인절 and 조건

 

    EMPNO ENAME                    DEPTNO HIREDATE MAXHIRE
---------- -------------------- ---------- -------- --------
      7844 TURNER                       30      81/09/08    81/12/03
      7698 BLAKE                        30        81/05/01   81/12/03
      7654 MARTIN                       30      81/09/28    81/12/03
      7521 WARD                         30       81/02/22    81/12/03
      7499 ALLEN                        30        81/02/20    81/12/03
      7566 JONES                        20        81/04/02    81/12/03
      7839 KING                         10         81/11/17    82/01/23
      7782 CLARK                        10         81/06/09   82/01/23

 

 

2.emp에서 급여를 많이 받는 사람 4명의 이름, 사번, 급여를 출력하시오

select ename, empno, sal from (select * from emp order by sal desc) where rownum <5;

--정렬후 행번호를 붙여 상위 몇개를 출력한다

 

3. 가장 최근에 입사한 지원 5명의 사번 이름 입사일자를 출력하시오

select rownum, empno, ename, hiredate from(

select empno, ename, hiredate from emp order by hiredate desc) where rownum<=5;

--정렬후 행번호를 붙여 상위 몇개를 출력한다

 

---

시퀀스

시퀀스란 오라클에서 자동증가 번호를 생성하는 객체이다. -- 연속번호는 아님.

참조 -- mysql에서는 auto_increment 

시퀀스가 갖는 유일성+notnull 성격때문에 식별자컬럼에 많이 사용한다.

 

기본형

create sequence 시퀀스명 

         increment by n -- n씩 증가한다

         start with n --n부터

         maxvalue n | nomaxvalue --최대값 n | 제한없음

         minvalue n | nominvalue --최소값 n | 제한없음

         cycle | nocycle --순환 | 비순환

         cache n | nocache -- 한번에 생성하는 숫자n개. default 20개 | 한개씩생성함

시퀀스명.nextval : 증가된 번호

시퀀스명.currval : 현재 가장 큰번호

 

1. 10부터 10씩 증가하는 시퀀스 mycount를 생성하시오

create sequence mycount start with 10 increment by 10;

2. 증가3번

select mycount.nextval from dual;

select mycount.nextval from dual;

select mycount.nextval from dual;

 

3. 현재 큰 번호 3번호 반환

select mycount.currval from dual; -- 증가하지 않고 현재 가장큰값을 반환한다.

select mycount.currval from dual;

select mycount.currval from dual;

 

4. 증가 2번을 실행하고 분석하시오.

select mycount.nextval from dual;

select mycount.nextval from dual;

 

시퀀스수정

기본형 : alter sequence 시퀀스명 옵션 값

 

1. mycount를 1씩 증가하는 시퀀스로 변경하고, 증가된값 세번을 출력하시오

alter sequence mycount increment by 1;

select mycount.nextval from dual; -- 1씩 증가됨

 

시퀀스삭제

기본형 : drop sequence 시퀀스명;

drop sequence mycount;

 

 

연습

1. 100부터 10씩증가하는 시퀀스 dept_seq를 생성하고 

create sequence dept_seq start with 100 increment by 10;

 

2. dept를 구조만 복사하여 dept_copy를 생성하고

drop table dept_copy;

create table dept_copy as select * from dept where 1=0;

 

3. dept_copy의 deptno 컬럼에 시퀀스를 이용하여 인사과 서울, 경리과 서울, 총무과 인천, 기술팀 일산 을 순서로 입력하고 결과를 확인하라

 

insert into dept_copy (deptno,dname,loc) values (dept_seq.nextval,'인사과','서울');

insert into dept_copy (deptno,dname,loc) values (dept_seq.nextval,'경리과','서울');

insert into dept_copy (deptno,dname,loc) values (dept_seq.nextval,'총무과','인천');

insert into dept_copy (deptno,dname,loc) values (dept_seq.nextval,'기술팀','일산');

 

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
       100 인사과                       서울
       110 경리과                       서울
       120 총무과                       인천
       130 기술팀                       일산

---

인덱스 INDEX

1. index : 목차 -- 빠른검색을 하기위해 생성하는 객체

2. 오라클 : B * tree 인덱스 방식을 사용한다.

3. root --> branch --> leaf 식으로 찾아 간다

  leaf는 원래 데이터고, root 와 branch는 인덱스를 위해서 추가적으로 필요한 공간

4. 단점) 추가적인 저장공간이 더 필요하다

5. 오래사용(데이터의 입력수정삭제등등이 많아지면)하면 인덱스의 성능이 떨어진다.

    따라서 주기적으로 rebuild를 해줘야한다.

6. 우리가 주로 설정하는 기본키는 자동인덱스 처리된다. 그래서 where절에 기본키가 자주 온다

7. DML 이 많은 테이블은 오히려 성능이 떨어진다.

 

 

인덱스의 채택방법

권장	비권장
행의 갯수가 많을 때	행의 갯수가 적을때
where 절에 자주사용되는 컬럼	where 절에 사용되지 않는 컬럼
검색결과가 2~4% 일때	10~15% 또는 그 이상일때
join절에 사용되는 컬럼	insert/update/delete가 많이사용될때
null을 많이포함할때

 

참고)

https://beelee.tistory.com/37

 

인덱스 기본형 

create index 인덱스명 on 테이블명(타겟컬럼,,,,);

 

연습

1. emp를 복제하여 e2를 생성합니다

create table e2 as select * from emp;

 

2. e2의 데이터를 e2에 입력합니다. 반복적으로해서 80만 줄 이상에 맞춥니다.

insert into e2 select * from e2;

 

3. 실행시간을 측정하기 위해서 set timing on 으로 설정합니다.

set timing on;

 

4. smith의 데이터를 검색합니다. 중복 제거

select  distinct ename from e2 where  ename='SMITH';

 시간은?

경   과: 00:00:00.06

 

5. e2의 ename에 대하여 index e2_idx를 생성합니다

create index e2_idx on e2(ename);

인덱스가 생성되었습니다.

경   과: 00:00:04.03

 

6.smith의 데이터를 검색합니다. 중첩제거

select  distinct ename from e2 where  ename='SMITH';

시간은?

경   과: 00:00:00.06

 

7.smith의 데이터를 검색합니다. 중첩제거

select  distinct ename from e2 where  ename='SMITH';

시간은?

경   과: 00:00:00.03

 

인덱스 정보

desc user_indexes; --일반정보

desc user_ind_columns; --인덱스가 설정된 컬럼의 정보

select index_name, index_type, table_name, uniqueness from user_indexes;

save index;

@index

인덱스 삭제

drop index 인덱스명 ;

인덱스 재설정

alter index 인덱스명 rebuild; -- 인덱스를 재설정(새로고침)한다..

인덱스 종류

1. 고유 인덱스 : unique index / 중복 불허

  create  unique index 이름 on 테이블 (타겟컬럼,,);

 

2. 비고유 인덱스 : nonunique index / 중복허용 / default

  create  index 이름 on 테이블 (타겟컬럼,,);

 

3. 단일 인덱스  : single index / 하나의 컬럼에 설정

  create  index 이름 on 테이블 (싱글컬럼);

 

4. 결합 인덱스 : composite index / 복수 컬럼

  create  index 이름 on 테이블 (컬럼,컬럼);

 

5. 함수기반 인덱스 : function based index / 컬럼에 연산식(함수)가 있는 경우

  create  index 이름 on 테이블 (컬럼연산식); // 급여 * 12  --연봉

 

연습

1. dept를 복제하여 d2를 생성한다.

create table d2 as select * from dept;

2. d2에 (50,'인사과','서울') 입력

2. d2에 (60,'총무과','대전') 입력

2. d2에 (70,'교육팀','대전') 입력

insert into d2 values (50,'인사과','서울');

 

3.deptno에 인덱스를 설정한다면 무엇이 좋을까요? 구현해보세요

  create  unique index no_ind on d2 (deptno);

2. loc에 인덱스를 설정한다면 무엇이 좋을까요?

  create  index loc_ind on d2 (loc);

5. deptno와 dname에 같이 인덱스를 설정하면 무엇이 좋을까요?

  create  index  no_name_ind on d2 (deptno,dname);

6. emp를 복제하여 e3를 생성합니다

create table e3 as select * from emp;

7. 연봉을 자주 검색합니다. 인덱스로 설정해보세요

  create  index sal_ind on e3 (sal*12);

주의) sal*4, sal+200, sal 식의 검색은 효과가 없다. sal*12 처럼 만든모양 그대로 검색해야된다

 

인덱스 컬럼 정보

desc user_ind_columns;

 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 INDEX_NAME                                         VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 COLUMN_NAME                                        VARCHAR2(4000)

 

desc user_ind_columns;

  select  INDEX_NAME , TABLE_NAME , COLUMN_NAME from user_ind_columns where table_name=&tablename;

save cols

@cols

/

 

---