VIEW & SEQUENCE & INDEX (Object of Oracle)

👀 VIEW

💁‍♀️ 뷰(VIEW)란,
SELECT 쿼리문을 저장한 객체로 실질적인 데이터를 저장하고 있지않은 논리적인 테이블이나 테이블을 사용하는 것과 동일하게 사용 가능

• 복잡한 SELCET 문을 다시 작성할 필요가 없음
• 민감한 데이터를 숨길 수 있음

---

🙋‍ 잠깐 ! 데이터 딕셔너리(Date Dictionary)도 알아봅시다 !
자원을 효율적으로 관리하기 위해 다양한 정보를 저장하는 시스템 테이블

• 사용자가 테이블을 생성하거나, 사용자를 변경하는 등의 작업을 할 때 데이터베이스 서버에 의해 자동으로 갱신되는 테이블로 사용자는 데이터 딕셔너리 내용을 직접 수정하거나 삭제할 수 없음
• 원본 테이블을 커스터마이징 해서 보여주는 원본 테이블의 가상 테이블(VIEW)
• 뷰에 대한 정보를 확인하는 데이터 딕셔너리
  USER_VIEWS

SELECT
        UV.*
    FROM USER_VIEWS UV;

---

👉 VIEW 생성 및 삭제

[1] system계정에서 EMPLOYEE계정으로 뷰를 만들 수 있는 권한을 부여

GRANT CREATE VIEW TO C##EMPLOYEE;

>>> 반드시 system계정으로 실행
>>> 'Grant을(를) 성공했습니다.' 출력

[2] V_RESULT_EMP 뷰 생성 (사번, 이름, 직급명, 부서명, 근무지역을 조회하고 그 결과를 뷰로 생성)

CREATE [OR REPLACE] VIEW 뷰이름 AS 서브쿼리;

CREATE OR REPLACE VIEW V_RESULT_EMP -- OR REPLACE : 있으면 이것으로 덮어쓰기
AS
SELECT
        EMP_ID
    ,   EMP_NAME
    ,   JOB_NAME
    ,   DEPT_TITLE
    ,   LOCAL_NAME
    FROM EMPLOYEE E
    LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
    LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
    LEFT JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE);

[3] 테이블처럼 사용 가능

SELECT
        V.*
    FROM V_RESULT_EMP V
    WHERE V.EMP_ID = '205'; 

[4] 뷰에 별칭을 부여해서 생성

CREATE OR REPLACE VIEW 뷰명 (뷰컬럼별칭) AS 서브쿼리

CREATE OR REPLACE VIEW V_EMP
(
    사번 -- EMP_ID에 별칭부여
,   이름
,   부서
)
AS
SELECT
        EMP_ID
    ,   EMP_NAME
    ,   DEPT_CODE
    FROM EMPLOYEE;

SELECT
        V.*
    FROM V_EMP V;

[5] 뷰 삭제

DROP VIEW 뷰명

DROP VIEW V_EMP; 

[6] 뷰의 서브쿼리 안에 연산의 결과를 포함

>>> 이 때는 '반드시 별칭을 부여해서 생성'해야함

CREATE OR REPLACE VIEW V_EMP_JOB
(
    사번 -- 뷰에 별칭 붙이기
,   이름
,   직급
,   성별
,   근무년수
)
AS
SELECT
        EMP_ID
    ,   EMP_NAME
    ,   JOB_NAME
    ,   DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', '여') 
    	>>> 함수식은 별칭 필수! 
    	>>> 없으면, '이 식은 열의 별명과 함께 지정해야 합니다' 오류
    ,   EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)
    FROM EMPLOYEE
    JOIN JOB USING(JOB_CODE);

SELECT
        V.*
    FROM V_EMP_JOB V; -- 별칭이 부여된 뷰 확인 가능

👉 베이스 테이블에서의 정보 변경

[1] 베이스 테이블에서의 EMP_NAME 변경

UPDATE
        EMPLOYEE
    SET EMP_NAME = '홍길동'
    WHERE EMP_ID = '200';

[2] 베이스 테이블 확인

SELECT
        E.*
    FROM EMPLOYEE E
    WHERE EMP_ID = '200'; -- EMP_NAME이 홍길동으로 변경됨

[3] 베이스 테이블의 정보가 변경되면 VIEW의 정보도 같이 변경 (VIEW는 베이스 테이블을 참조해오고 있기 때문에)

SELECT
        V.*
    FROM V_RESULT_EMP V
    WHERE EMP_ID = '200'; -- 홍길동으로 변경되어있음

👉 VIEW 테이블에서의 정보 변경

[1] 뷰를 통해 값 삽입

INSERT
    INTO V_JOB
(
    JOB_CODE, JOB_NAME
)
VALUES
(
    'J8', '인턴'
);

SELECT
        V.*
    FROM V_JOB V; -- 'J8', '인턴' 값 조회가능

SELECT
        J.*
    FROM JOB J; -- 베이스 테이블에서도 'J8', '인턴' 값 조회가능

[2] 뷰를 통해 JOB_NAME 수정

UPDATE
        V_JOB V
    SET JOB_NAME = '알바'
    WHERE JOB_CODE = 'J8';

SELECT
        V.*
    FROM V_JOB V; -- 'J8', '알바'로 변경된 값 조회가능

SELECT
        J.*
    FROM JOB J; -- 베이스 테이블에서도 'J8', '알바'로 변경된 값 조회가능

[3] 뷰를 통해 J8 알바행 삭제

DELETE
    FROM V_JOB
    WHERE JOB_CODE = 'J8'; 
    -- 삭제한 후 조회 시, 뷰와 베이스 테이블에서 모두 삭제된 것 확인 가능

👉 DML 명령어로 VIEW 조작이 불가능한 경우

뷰를 통해 베이스테이블 삽입/변경/삭제 가능한 것 확인했으나, 반면에 조작이 불가능한 경우가 많음

📍 VIEW 조작이 불가능한 경우

[1] 뷰 정의에 포함되지 않은 컬럼을 조작하는 경우
[2] 뷰에 포함되지 않은 컬럼 중에, 베이스가 되는 테이블 컬럼이 NOT NULL 제약조건이 지정 된 경우
[3] 산술 표현식으로 정의 된 경우
[4] JOIN을 이용해 여러 테이블을 연결한 경우
[5] DISTINCT를 포함한 경우
[6] 그룹함수나 GROUP BY절을 포함한 경우

👉 VIEW 옵션

[1] OR REPLACE

OR REPLACE : 기존에 동일한 뷰 이름이 존재하는 경우 덮어쓰고, 존재하지 않으면 새로 생성하는 옵션
FORCE(강제 실행) : 서브 쿼리에 사용 된 테이블이 존재하지 않아도 뷰 생성하는 옵션

CREATE OR REPLACE FORCE VIEW V_EMP
AS
SELECT TCODE
    ,  TNAME
    ,  TCONTENT
    FROM TT;   
    >>> FORCE로 인해 강제로 뷰 생성 및 '경고: 컴파일 오류와 함께 뷰가 생성되었습니다.' 출력

[2] NOFORCE

NOFORCE : 서브쿼리에 테이블이 존재해야만 뷰를 생성하는 옵션(기본값)

CREATE OR REPLACE NOFORCE VIEW V_EMP >>> 기본값이므로 NOFORCE를 명시하지않아도됨
AS
SELECT TCODE
    ,  TNAME
    ,  TCONTENT
    FROM TT;    
    >>> '테이블 또는 뷰가 존재하지 않습니다' 오류

[3] WITH CHECK OPTION

WITH CHECK OPTION : 조건절에 사용 된 컬럼의 값을 수정하지 못 하게 하는 옵션

CREATE OR REPLACE VIEW V_EMP2
AS
SELECT
        E.*
    FROM EMPLOYEE E
    WHERE MANAGER_ID = '200'
    >>> 이 부분은 중요한 부분이므로 뷰를 통해 수정하지 못 하게 함
    WITH CHECK OPTION;

✅ MANAGER_ID 수정 시도

UPDATE
        V_EMP2
    SET MANAGER_ID = '900'
    WHERE MANAGER_ID = '200'; -- '뷰의 WITH CHECK OPTION의 조건에 위배 됩니다' 오류

[4] WITH READ ONLY

WITH READ ONLY : DML 수행이 불가능하게 하는 옵션

CREATE OR REPLACE VIEW V_DEPT
AS
SELECT
        D.*
    FROM DEPARTMENT D
    WITH READ ONLY; 
    >>> 읽기 전용이므로 삽입/수정/삭제 불가능

✅ V_DEPT 삭제 시도

DELETE
    FROM V_DEPT; -- '읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.' 오류

👀 SEQUENCE

💁‍♀️ 시퀀스(SEQUENCE)란,
자동 번호 발생기 역할을 하는 객체

• 순차적으로 정수 값을 자동으로 생성

👉 SEQUENCE 생성, 실행 및 조회

CREATE SEQUENCE 시퀀스명
[INCREMENT BY 숫자] -- 다음 값에 대한 증가치, 생략하면 자동 1이 기본
[START WITH 숫자] -- 처음 발생시킬 값 지정, 생략하면 자동 1이 기본
[MAXVALUE 숫자 or NOMAXVALUE] -- 발생시킬 최대 값 지정(10의 27승)
[MINVALUE 숫자 or NOMINVALUE] -- 발생시킬 최소 값 지정(-10의 26승)
[CYCLE or NOCYCLE] -- 값 순환 여부
[CACHE 바이트크기 or NOCACHE] --캐쉬 메모리 기본 값은 20바이트, 최소 2바이트

[1] SEQ_EMPID 시퀀스 생성

CREATE SEQUENCE SEQ_EMPID
START WITH 300
INCREMENT BY 5
MAXVALUE 310
NOCYCLE
NOCACHE;

[2] SEQ_EMPID 시퀀스 실행

시퀀스명.CURRVAL : 해당 시퀀스가 가지고 있는 CURRENT VALUE(현재 값)
시퀀스명.NEXTVAL : 해당 시퀀스가 가질 NEXT VALUE(다음 값) 리턴. NEXTVAL을 1회 수행해야 CURRVAL를 알 수 있음

SELECT SEQ_EMPID.CURRVAL FROM DUAL; 
>>> CURRVAL 바로 실행 시, 
>>> '시퀀스 SEQ_EMPID.CURRVAL은 이 세션에서는 정의 되어 있지 않습니다' 오류

SELECT SEQ_EMPID.NEXTVAL FROM DUAL; -- 300
SELECT SEQ_EMPID.CURRVAL FROM DUAL; -- 300
SELECT SEQ_EMPID.NEXTVAL FROM DUAL; -- 305
SELECT SEQ_EMPID.CURRVAL FROM DUAL; -- 305
SELECT SEQ_EMPID.NEXTVAL FROM DUAL; -- 310
SELECT SEQ_EMPID.CURRVAL FROM DUAL; -- 310

SELECT SEQ_EMPID.NEXTVAL FROM DUAL;
>>> MAXVALUE를 넘어가면 에러 발생
>>> '시퀀스 SEQ_EMPID.NEXTVAL exceeds MAXVALUE은 사례로 될 수 없습니다'

[3] 데이터 딕셔너리를 통해 SEQ_EMPID 시퀀스 조회

USER_SEQUENCES

SELECT * FROM USER_SEQUENCES;

👉 SEQUENCE 변경 및 삭제

[1] SEQ_EMPID 시퀀스 변경

ALTER SEQUENCE 시퀀스명 ...

ALTER SEQUENCE SEQ_EMPID
INCREMENT BY 10
MAXVALUE 400
NOCYCLE
NOCACHE;
>>> START WITH 값은 '변경 불가능'이므로, 
>>> 해당 값을 변경하려면 DROP으로 삭제 후 다시 생성

SELECT SEQ_EMPID.NEXTVAL FROM DUAL; -- 320
SELECT SEQ_EMPID.CURRVAL FROM DUAL; -- 320
SELECT SEQ_EMPID.NEXTVAL FROM DUAL; -- 330
SELECT SEQ_EMPID.CURRVAL FROM DUAL; -- 330
>>> 시퀀스가 변경되어 10씩 증가하고 최대값이 400까지 늘어나 추가가 지속

[2] SEQ_EMPID 시퀀스 삭제

DROP SEQUENCE 시퀀스명;

DROP SEQUENCE SEQ_EMPID;

👉 SEQUENCE의 사용

SEQUENCE는 '테이블의 식별자(PK)' 로 가장 많이 사용 (넘버링)

[1] SEQ_EID 시퀀스 생성

CREATE SEQUENCE SEQ_EID
START WITH 300
INCREMENT BY 1
MAXVALUE 10000
NOCYCLE
NOCACHE;

[2] EMPLOYEE 테이블의 EMP_ID 컬럼에 시퀀스 삽입

INSERT
    INTO EMPLOYEE

-- (모든 컬럼명) 
-- => 아래 VALUES에서 모든 컬럼에 대한 값을 순서대로 입력하였으므로 명시 불필요

VALUES
(
    SEQ_EID.NEXTVAL, '홍길동', '660101-1111111', 'hong_gd@greedy.com', '01012345678',
    'D2', 'J7', 'S1', 5000000, 0.1, 200, SYSDATE, NULL, DEFAULT
);
>>> 300부터 시작하고 1씩 증가하는 SEQ_EID.NEXTVAL를 EMP_ID 컬럼에 값으로 넣어 
>>> 추가할 때마다 300, 301, 302,...로 추가되게끔 시퀀스 삽입

[3] EMPLOYEE 테이블 조회하여 시퀀스 삽입된 것 확인

SELECT
        E.*
    FROM EMPLOYEE E;

---

👀 INDEX

💁‍♀️ 인덱스(INDEX)란,
SQL 명령문의 검색처리 속도를 향상시키기 위해 컬러에 대해 생성하는 객체

• 하드 디스크의 어떤 위치에 대한 정보를 가진 주소록으로 인덱스는 DATA와 ROWID로 구성
• 인덱스의 내부 구조는 이진 트리 형식으로 구성 (노드들이 정렬된 형태)
• 인덱스를 생성하기 위해서는 시간이 필요하며 인덱스를 위한 추가 저장 공간이 필요하기 때문에 항상 장점만 있는 것은 아님
• 인덱스가 생성 된 컬럼에서 DML 작업이 빈번한 경우 처리 속도가 느려짐

---

💁‍ 인덱스의 장점 & 단점

• 장점
  • 검색 속도 향상
  • 시스템 부하를 줄여(FULL SCAN을 하지 않으므로) 시스템 성능 향상
• 단점
  • 추가 저장 공간, 생성 시간 필요
  • DML이 빈번하면 REBUILD(트리 구조를 다시 만듬) 작업을 주기적으로 해야하며 REBUILD 하지 않으면 오히려 성능 저하

👉 일반적으로 데이터 전체 로우의 15% 이하의 데이터를 조회할 때 인덱스를 생성해서 사용 (적은 데이터를 FULL SCAN하면 비효율적이므로)

---

🙋‍ 잠깐 ! ROWID의 구조는 어떻게 되나요?
오브젝트 번호, 상대파일 번호, 블록 번호, 데이터 번호 (물리적인 ID값)

SELECT
        ROWID
    ,   EMP_ID
    ,   EMP_NAME
    FROM EMPLOYEE;

---

👉 INDEX의 종류

📍 INDEX의 종류

[1] 고유 인덱스(UNIQUE INDEX)
[2] 비고유 인덱스(NONUNIQUE INDEX)
[3] 단일 인덱스(SINGLE INDEX)
[4] 결합 인덱스(COMPOSITE INDEX)
[5] 함수 기반 인덱스(FUNTION BASED INDEX)

🙋‍ 잠깐 ! 인덱스 힌트 먼저 알고갑시다 !
일반적으로는 옵티마이저가 적절한 인덱스를 타거나 풀 스캐닝을 해서 비용이 적게 드는 효율적인 방식을 선택. 하지만 우리가 원하는 테이블에 있는 인덱스를 사용할 수 있도록 하는 구문(힌트)를 통해서 선택할 수도 있음

• SELECT절 첫 줄에 힌트 주석 /* + 내용 */을 작성하여 적절한 인덱스를 부여할 수 있음

---

[1] UNIQUE INDEX (고유 인덱스)

• 고유 인덱스로 생성 된 컬럼에는 중복 값이 포함될 수 없으며 PK, UNIQUE 제약 조건을 생성하면 자동으로 해당 컬럼에 UNIQUE INDEX가 생성.
• 해당 컬럼으로 ACCESS 하는 경우 성능 향상 효과
• 단일 인덱스라고도 볼 수 있음

✅ 인덱스 영역에서 오름차순으로 스캔 (INDEX)

SELECT /*+ INDEX(E 엔터티1_PK)*/ >>> 인덱스를 이용해서 조회해달라는 힌트
        E.*
    FROM EMPLOYEE E;
    >>> 조회했을 때, 내부적으로 가장 빠른 조회법을 찾음 
    >>> (인덱스로 찾거나 풀 스캔으로 찾거나)

✅ 인덱스 영역에서 내림차순으로 스캔 (INDEX_DESC)

SELECT /*+ INDEX_DESC(E 엔터티1_PK)*/ >>> 인덱스를 이용해서 역방향으로 조회해달라는 힌트 
        E.*
    FROM EMPLOYEE E;

✅ 인덱스 생성 (중복 값이 있는 컬럼은 UNIQUE 인덱스 생성 가능)

CREATE UNIQUE INDEX 인덱스명 ON 테이블명(컬럼명);

CREATE UNIQUE INDEX IDX_EMP_NO
ON EMPLOYEE(EMP_NO);

✅ 중복 값이 있는 컬럼은 UNIQUE 인덱스를 생성 X

CREATE UNIQUE INDEX IDX_DEPT_CODE
ON EMPLOYEE(DEPT_CODE);

>>> DEPT_CODE의 각 컬럼에 값이 여러개 있음. 
>>> '중복 키가 있습니다. 유일한 인덱스를 작성할 수 없습니다' 오류

[2] NONUNIQUE INDEX (비고유 인덱스)

• WHERE절에서 빈번하게 사용되는 일반 컬럼을 대상으로 생성
• 단일 인덱스라고도 볼 수 있음

CREATE INDEX IDX_DEPT_CODE	>>> UNIQUE 키워드만 빼면 NONUNIQUE INDEX 생성 가능
ON EMPLOYEE(DEPT_CODE);

>>> 중복값이 있는 DEPT_CODE 컬럼은 NONUNIQUE 인덱스 생성

[3] COMPOSIRE INDEX (결합 인덱스)

결합 인덱스는 중복 값이 낮은 값이 먼저 오는 것이 검색 속도를 향상 시킴

✅ IDX_DEPT 인덱스 생성

CREATE INDEX IDX_DEPT
ON DEPARTMENT(DEPT_ID, DEPT_TITLE); >>> 중복 값이 더 낮은 DEPT_ID 컬럼 먼저 명시

✅ IDX_DEPT 인덱스 사용

SELECT /*+ INDEX_DESC(D IDX_DEPT)*/
        D.DEPT_ID
    FROM DEPARTMENT D
    WHERE D.DEPT_TITLE > '0'	>>> 인덱스를 사용하여 값들이 정렬되었으면 했을 때 사용하는 조건 
    							>>> (누군가를 거를 때 쓰는 조건 X)
    AND D.DEPT_ID > '0';

[4] FUNTION BASED INDEX (함수 기반 인덱스)

• SELECT절이나 WHERE절에서 산술 계산식이나 함수가 사용된 경우, 계산에 포함된 컬럼은 인덱스의 적용을 받지 않음
• 계산식으로 검색하는 경우가 많다면, 수식이나 함수식으로 이루어진 컬럼을 인덱스로 만들수도 있다.

✅ IDX_EMP_SALCALC 인덱스 생성

CREATE INDEX IDX_EMP_SALCALC
ON EMPLOYEE((SALARY + (SALARY * NVL(BONUS,0))) * 12);

✅ IDX_EMP_SALCALC 인덱스 사용

SELECT /*+ INDEX_DESC(E IDX_EMP_SALCALC)*/
        E.EMP_ID
    ,   E.EMP_NAME
    ,   (E.SALARY + (E.SALARY * NVL(E.BONUS,0))) * 12 연봉
    FROM EMPLOYEE E
    WHERE (E.SALARY + (E.SALARY * NVL(E.BONUS,0))) * 12 > 1000000;

---