하위^^

뷰 = 가상의 테이블

뷰는 가상의 테이블을 만들어 사용 할 때 쓴다.

만약 정규화를 하여 6개의 테이블로 분리가 되었는데 이를 조인해서 여러번 사용한다고 치자

그러면 사용할 때 마다 3~4줄이 되는 SELECT 문이 만들어진다.

너무 길어지기 때문에 간단하게 사용하기 위해서 나온게 뷰이다.

뷰에 SELECT같이 조회한 내용을 넣어 묶음으로 임시로 가상 테이블을 생성하여

간단하게 뷰만 호출하는 방식이다.

뷰는 본질적으로 보기만 하기 위해서 만들어진 기능이다.

하지만 수정이 가능하게 되었는데 아래 코드의 주석을 통해 알아보자.

또한 뷰가 어떻게 쓰이는지도 알아보자.

12_view

-- View

-- 여러 테이블의 값을 서브쿼리나 조인으로 가져오기 힘들다

-- 가상의 테이블에 여러 테이블의 값을 담도록 한다.

-- 이후 가상 테이블을 부르기만 하면 된다.

-- 0) 뷰의 제한 조건

-- 테이블 데이터에 NULL인 컬럼은 뷰에 포함 될 수 없다.

-- WITH READ ONLY 옵션을 사용 하면 뷰 데이터 갱신이 불가하다.

-- WITH CHECK OPTION을 사용하면 해당 데이터 수정 가능

-- 하지만 ROWID, ROWNUM, NEXTVAL, CURRVAL 등과 같은 가상컬럼에

-- 대한 참조를 포함하는 뷰에는 INSERT 할 수 없다.

-- 뷰는 원래 수정이 불가능 했지만 요즘에는 바뀌었다고 함

-- 하지만 퍼져있는 데이터가 묶여있기 때문에

-- 뷰가 현재 어디를 참조하는지 예측하기 어렵고 만약 수정한다면

-- 데이터가 어디가 변경됫는지 파악하기 힘들게 된다.

-- 그러므로 수정은 매우 비추

-- 1) 뷰의 생성 문법

-- CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW [view name] AS [sub-query]

-- [WHERE 조건 WITH CHECK OPTION] [WITH READ ONLY]

-- OR REPLACE : 뷰 생성시 기존 뷰가 있으면 갱신한다.

-- 이름이 같은게 있다면 데이터를 새로은 쿼리문으로 바꾸는것.

-- 뼈대를 남기고 다시 건물을 짓는것처럼 리모델링 하는 것

-- 자원 상 절약이 되므로 습관적으로 붙이는걸 추천

-- FORCE : 기존 테이블 없이 일단 만든다.

-- WITH CHECK OPTION : 조건에 따라 뷰를 수정 할 수 있는 옵션

-- WITH READ ONLY : 읽기 전용으로 지정

CREATE OR REPLACE VIEW name_qry AS

    SELECT e.ename, d.deptname FROM emp e, dept d WHERE e.deptno = d.deptno

        WITH READ ONLY;

-- 아래의 오류가 발생하면 권한이 없기 때문임.

-- ORA-01031: insufficient privileges

-- 01031. 00000 -  "insufficient privileges"

-- 우측 상단의 WEB_USER 접속을 SYSTEM으로 바꿔서 접속

GRANT CREATE VIEW TO web_user;

-- 이것을 실행 한 후 다시 접속을 WEB_USER로 바꿔주고 위의 CREATE OR REPLACE VIEW를 다시 실행한다.

SELECT * FROM name_qry;

-- 이것으로 조회 할 수 있다.

-- 뷰는 테이블의 구조같은 중요한 정보를 알 수 없기 때문에

-- 다른 사람에게 보여주기 위한 테이블이다.

-- 또한 이러한 특성으로 보안에도 효과적이다.

UPDATE name_qry SET ename = 'oh' WHERE deptname = 'delivery';

-- with read only 옵션 때문에 데이터 수정이 불가

-- SQL 오류: ORA-01779: cannot modify a column which maps to a non key-preserved table

-- 01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"

CREATE OR REPLACE VIEW chk_option AS 

    SELECT ename, job, deptno FROM emp

        WHERE deptno = 1 WITH CHECK OPTION;

SELECT * FROM chk_option;

UPDATE chk_option SET job = 'manager' WHERE ename = 'lee';

UPDATE chk_option SET deptno = 2 WHERE ename = 'lee';

-- ORA-01402: view WITH CHECK OPTION where-clause violation 발생

-- deptno = 1일 경우에만 바뀌게 되어있으므로 deptno = 2는 바뀌어선 안된다.

-- 2) 뷰 삭제

-- 어떤 뷰가 있는지 조회

SELECT * FROM USER_VIEWS;

-- 삭제할 뷰를 입력

DROP VIEW name_qry;

DROP VIEW chk_option;

-- 뷰는 수정하는것 보단 새로 만드는것을 지향하자

ROWID, ROWNUM, NEXTVAL, CURRVAL 등과 같은 가상컬럼은 아래 테이블을 조회했을때 DB에서 임시로 지정한 번호같은걸 말한다.

사진상의 회색 박스 좌측의 번호는 어디서 나온걸까??

이러한게 가상 컬럼이다.

어려운 개념으로 상세히는 배우지 않았으므로 이정도로만 설명

1.정규화, 비정규화

정규화는 DB 설계 자체에서 데이터의 중복을 최소화하여 이상현상을 최소화하는 것이 목표이다.

이상현상으로는 아래와 같이 3가지가 있다.

삭제 이상 - 원치 않는 데이터도 같이 삭제

삽입 이상 - 원치 않는 데이터가 같이 삽입

갱신 이상 - 데이터의 일부만 갱신

정규화를 위한 기본 폼(NF, Nomal Form), 규칙같은게 있는데 아래와 같다.

1NF - 모든 항목에 값이 있어야 하며(NOT NULL), 중복열이 없어야 한다.

2NF - 개체의 속성이 한 식별자에 종속 되어야 한다.(부분 함수적 종속 제거)

3NF - X로 인해 Y가 결정 되고, Y로 인해 Z가 결정되는 경우(이행 함수 종속성)

~

4NF...

5NF...

.....

4NF부턴 이론적으로만 존재

이 NF 규칙을 봤을땐 바로 이해가지 않을 것이다.

솔직히 말을 되게 어렵게 써서 어떻게 간단하게 쓸지도 모르겠다.

고로 예제를 통해서 이해해보자.

데이터 표의 밑줄은 항목을 키로 지정했음을 표시하는것

1NF

우측 그림은 비식별관계여야 함, 잘못 표시

2NF

복합키는 NAME 항목을 보면 중복값이 많기 때문에 어쩔 수 없이 묶어 복합키를 사용했다.

하지만 쪼갤 수 있으므로 분리해서 사용할 수 있을 땐 분리하자

쉽게 말하면 복합키를 구지 사용하지 말자.

3NF

만약 두 데이터가 아래와 같이 합쳐져 있었다면

연도  우승국  감독

1998    X        X

2002    X        X

2006    X        X

2010    X        X

2014    X        X

만약 우승국이 바뀐다면 감독도 바뀌어야 한다.

고로 위와같이 분리하였으니 우승국만 바꾸면 된다.

위와같은 데이터는 항목, 컬럼이 한두개 밖에 없으니 바꾸는 작업이 별로 없을것 같지만

실제로는 컬럼이 훨씬 많이 지정되므로 바꾸지 않으면 매우 번거로운 작업이 된다.

NF에서 요구하는건 대부분 테이블 규모를 키우지 말고 되도록 쪼개라는 말이 된다.

하지만 테이블을 지나치게 쪼개면, 정규화를 하면 쿼리가 복잡해지고, 복잡해지면 성능이 저하된다.

예를들어 테이블 참조를 여러개, 만약 6개를 한다면??, 속도가 매우 느려질것.

그래도 이해가 안간다면 정규화의 예시를 구글에서 찾아보자

역정규화는 말 그대로 정규화의 반대.

쪼갠 테이블을 합치는 것이다.

2. INDEX (색인)

색인은 검색을 빠르게 하기위한 수단

기본키(Primary Key), 유니크 키(Unique Key)가 지정되면 자동으로 Index가 생성된다.

색인의 예로는 대표적으로 사전이나 책이 있다.(가나다라, ABCD~ 기준으로 나열된다.)

장점은 검색 속도가 빨라지고 시스템의 부하가 줄어든다.

단점으로는 색인 생성에 시간, 공간이 요구되고, 데이터가 삽입/갱신/삭제가 빈번히 일어나면 시스템에 부하가 걸린다.

색인은 키값을 지정할 때 자동으로 생성 되게 끔 되있는데

임의로 지정할 수도 있다. 아래의 예시를 확인해보자.

색인을 생성하는 것을 Indexing이라고 한다.

-- 1) 비트맵 인덱스

-- bit, 각 컬럼에 대해 적은 갯수의 값이 있을 경우 사용()

-- (예 : 남|여, 1|0, True|False)

-- 데이터가 어디 있는지 BIT로 표시, 속도가 빠르지만 컬럼이 추가 될 때 마다 인덱스를 다시 만든다.

-- 수정/변경/추가가 적은 데이터에 적합

-- Standard Edition 버전(유료버전)에서만 사용 가능

-- CREATE BITMAP INDEX [index name] ON table(column)

CREATE BITMAP INDEX emp_idx_deptno ON emp(deptno);

-- 위 구문은 유료 버전이 아니라면 오류가 발생한다.

-- 2) UNIQUE INDEX

-- 기본키와 유니크키 설정 시 생성되는 인덱스

-- 중복되지 않은 데이터에게만 걸 수 있는 인덱스

-- CREATE UNIQUE INDEX [index name] ON table(column)

CREATE UNIQUE INDEX emp_idx_ename ON emp(ename);

-- 위와 같이 중복된 데이터가 있을 경우 에러 발생

-- ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

-- 01452. 00000 -  "cannot CREATE UNIQUE INDEX; duplicate keys found"

-- 기본키와 비슷해서 사용할 일이 별로 없음

-- 3) NON-UNIQUE INDEX

-- 컬럼에 중복이 있어도 인덱스 생성이 가능

-- 가장 일반적으로 사용하는 INDEX 구문

-- CREATE INDEX [index name] ON table(column)

CREATE INDEX emp_idx_ename ON emp(ename);

-- 4)결합 인덱스

-- 16개 까지 컬럼을 조합하여 인덱스 생성

-- INDEX앞에 UNIQUE를 붙일 수 있다. (중복되는 컬럼 비허용)

-- CREATE INDEX [index name] ON table(column, column, ...)

CREATE INDEX emp_combi_idx ON emp(ename, job);

-- 5)인덱스 검색

-- 어떤 인덱스가 있나 검색

SELECT * FROM USER_INDEXES;

SELECT index_name, table_name, uniqueness FROM USER_INDEXES;

-- 6)인덱스 삭제

DROP INDEX EMP_IDX_ENAME;

수학시간에 배운 집합 개념을 DB에 적용한것

합집합 교집합 차집합 개념 자체를 따로 설명할 필요는 없을것 같아서 생략

-- [Query1] [UNION | UNION ALL | INTERSECT | MINUS] [Query2]

-- 1. 합집합(UNION)
-- 두개의 테이블의 데이터를 모두 보여준다.(중복 제거)
-- 성능상 계산이 많아 별로 좋지 않다.
-- 동일한 컬럼이 최소 한개 이상 있어야 한다.
SELECT Deptno FROM emp UNION SELECT deptno FROM dept;
-- ALL을 사용하여 중복 포함
SELECT Deptno FROM emp UNION ALL SELECT deptno FROM dept;



-- 교집합(INTERSECT)
SELECT Deptno FROM emp INTERSECT SELECT deptno FROM dept;







-- 차집합(MINUS), 6 - 1 = 5 처럼 왼쪽이 기준
-- emp에서 dept를 빼고 남은 값을 출력
SELECT Deptno FROM emp MINUS SELECT deptno FROM dept;
-- dept에서 emp를 빼고 남은 값을 출력
SELECT Deptno FROM dept MINUS SELECT deptno FROM emp;