VIEW

SELECT문의 실행 결과(RESULT SET)를 저장하는 객체
논리적 가상 테이블
-> 테이블 모양을 하고는 있지만
실제로 값을 저장하고 있지는 않음.

VIEW 사용 목적
1) 복잡한 SELECT문을 쉽게 재사용하기 위해서 사용.
2) 테이블의 진짜 모습을 감출 수 있어 보안상 유리함.

***** VIEW 사용 시 주의사항 *****

1) 가상의 테이블(실제 테이블 X) -> ALTER 구문 사용 불가
2) VIEW를 이용한 DML(INSERT/UPDATE/DELETE)가
가능한 경우도 있지만
많은 제약이 따르기 때문에 SELECT 용도로 사용 하는 것을 권장.

VIEW 생성 방법

CREATE [OR REPLACE][FORCE | NOFORCE] VIEW 뷰이름 [(alias[,alias]...)]
AS subquery
[WITH CHECK OPTION][WITH READ ONLY];

1) OR REPLACE 옵션 : 기존에 동일한 뷰 이름이 존재하는 경우 덮어쓰고,
존재하지 않으면 새로 생성.
2) FORCE / NOFORCE 옵션
FORCE : 서브쿼리에 사용된 테이블이 존재하지 않아도 뷰 생성
NOFORCE : 서브쿼리에 사용된 테이블이 존재해야만 뷰 생성(기본값)
3) WITH CHECK OPTION 옵션 : 옵션을 설정한 컬럼의 값을 수정 불가능하게 함.
4) WITH READ ONLY 옵션 : 뷰에 대해 조회만 가능(DML 수행 불가)

-- 사번, 이름, 부서명, 직급명 조회 결과를 저장하는 VIEW 생성
CREATE VIEW V_EMP
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE
JOIN JOB USING (JOB_CODE)
JOIN DEPARTMENT ON(DEPT_ID = DEPT_CODE);

1) SYS 관리자 계정 접속
ALTER SESSION SET "ORACLE_SCRIPT" = TRUE;
GRANT CREATE VIEW TO kh이니셜;
--> VIEW 생성 구문 다시 실행

-- 생성된 VIEW를 이용해서 조회
SELECT * FROM V_EMP;

---

-- OR REPLACE 확인 + 별칭
CREATE OR REPLACE VIEW V_EMP
AS SELECT EMP_ID 사번, EMP_NAME 이름, DEPT_TITLE 부서명, JOB_NAME 직급명
FROM EMPLOYEE
JOIN JOB USING (JOB_CODE)
JOIN DEPARTMENT ON(DEPT_ID = DEPT_CODE);

-- ORA-00955: 기존의 객체가 이름을 사용하고 있습니다.
-- OR REPLACE 추가 후 실행 -> 성공
--> 기존 V_EMP를 새로운 VIEW로 덮어쓰기

SELECT * FROM V_EMP;

---

-- VIEW를 이용한 DML 확인

-- 테이블 복사
CREATE TABLE DEPT_COPY2
AS SELECT * FROM DEPARTMENT;

-- 복사한 테이블을 이용해서 VIEW 생성
SELECT * FROM DEPT_COPY2;

CREATE OR REPLACE VIEW V_DCOPY2
AS SELECT DEPT_ID, LOCATION_ID FROM DEPT_COPY2;

-- 뷰 생성 확인
SELECT * FROM V_DCOPY2;

-- 뷰를 이용한 INSERT
INSERT INTO V_DCOPY2 VALUES ('D0', 'L3');

-- 삽입 확인
SELECT * FROM V_DCOPY2; --> VIEW에 'D0'가 삽입된 걸 확인함.
--> 가상의 테이블인 VIEW에 데이터 삽입이 된 것이 아닙니다!

-- 원본 테이블 확인
SELECT * FROM DEPT_COPY2; -- 'D0' NULL 'L3'
--> VIEW에 삽입한 내용이 원본 테이블에 존재함.
--> VIEW를 이용한 DML 구문이 원본에 영향을 미친다.

-- VIEW를 이용한 DML시 발생하는 문제점 == 제약조건 위배 현상
ROLLBACK;
SELECT FROM DEPT_COPY2;
SELECT FROM V_DCOPY2;

-- 원본 테이블 DEPT_TITLE 컬럼에 NOT NULL 제약조건 추가
ALTER TABLE DEPT_COPY2 MODIFY DEPT_TITLE NOT NULL;

-- 현 상태에서 다시 VIEW를 이용한 INSERT 수행
INSERT INTO V_DCOPY2 VALUES('D0', 'L3');

--> VIEW를 이용한 INSERT 시
-- VIEW가 아닌 원본 테이블에 삽입이 진행된다.

-- VIEW에 포함되지 않은 컬럼(DEPT_TITLE)에는
-- INSERT 시 NULL이 삽입됨. ('D0', NULL, 'L3')

-- 만약 VIEW에 포함되지 않은 컬럼(DEPT_TITLE)에
-- NOT NULL 제약조건이 설정되어 있는 경우
-- 정상적인 INSERT가 진행되지 않는다!
--> ORA-01400: NULL을 ("KH_BDH"."DEPT_COPY2"."DEPT_TITLE") 안에 삽입할 수 없습니다

-- VIEW의 본래 목적인 보여지는 것(조회)이라는 용도에 맞게 사용하는게 좋다
--> WITH READ ONLY 옵션 사용

CREATE OR REPLACE VIEW V_DCOPY2
AS SELECT DEPT_ID, LOCATION_ID FROM DEPARTMENT
WITH READ ONLY; -- 읽기 전용 VIEW 생성 (SELECT만 가능)

INSERT INTO V_DCOPY2 VALUES('D0', 'L3');
-- ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

---

SEQUENCE(순서, 연속, 수열)

• 순차적 번호 자동 발생기 역할의 객체
  

-> SEQUENCE 객체를 생성해서 호출하게되면
지정된 범위 내에서 일정한 간격으로 증가하는 숫자가
순차적으로 출력됨.

 EX) 1부터 10까지 1씩 증가하고 반복하는 시퀀스 객체
 1 2 3 4 5 6 7 8 9 10 1 2 3 4 5 6 7 8 9 10
 
 ** SEQUENCE는 주로 PK역할의 컬럼에 삽입되는 값을 만드는 용도로 사용 **    
--> 인위적 주식별자

  

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

-- 시퀀스의 캐시 메모리는 할당된 크기만큼 미리 다음 값들을 생성해 저장해둠
-- --> 시퀀스 호출 시 미리 저장되어진 값들을 가져와 반환하므로
-- 매번 시퀀스를 생성해서 반환하는 것보다 DB속도가 향상됨.

시퀀스 사용 방법
1) 시퀀스명.NEXTVAL : 다음 시퀀스 번호를 얻어옴. (INCREMENT BY만큼 증가된 값)
단, 시퀀스 생성 후 첫 호출인 경우 START WITH의 값을 얻어옴.
2) 시퀀스명.CURRVAL : 현재 시퀀스 번호 얻어옴.
단, 시퀀스 생성 후 NEXTVAL 호출 없이 CURRVAL를 호출하면 오류 발생.

-- 옵션 없이 SEQUENCE 생성
-- 범위 : 1 ~ 10^38
-- 시작 : 1
-- 반복 X
-- 캐시메모리 20BYTE

CREATE SEQUENCE SEQ_TEST;

• • CURRVAL 주의사항 *
    --> CURRVAL는 마지막 NEXTVAL 호출 값을 다시 보여주는 기능
    --> NEXTVAL를 먼저 호출해야 CURRVAL 호출이 가능하다!

-- 생성되자마자 바로 현재 값 확인
SELECT SEQ_TEST.CURRVAL FROM DUAL;
--> ORA-08002: 시퀀스 SEQ_TEST.CURRVAL은 이 세션에서는 정의 되어 있지 않습니다
SELECT SEQ_TEST.NEXTVAL FROM DUAL; -- 1
SELECT SEQ_TEST.CURRVAL FROM DUAL; -- 1

SELECT SEQ_TEST.NEXTVAL FROM DUAL; -- 2
SELECT SEQ_TEST.NEXTVAL FROM DUAL; -- 3
SELECT SEQ_TEST.NEXTVAL FROM DUAL; -- 4
SELECT SEQ_TEST.NEXTVAL FROM DUAL; -- 5

SELECT SEQ_TEST.CURRVAL FROM DUAL; -- 5

---

-- 실제 사용 예시

CREATE TABLE EMP_TEMP
AS SELECT EMP_ID, EMP_NAME FROM EMPLOYEE;

SELECT * FROM EMP_TEMP;

-- 223번부터 10씩 증가하는 시퀀스 생성
CREATE SEQUENCE SEQ_TEMP
START WITH 223 -- 223 시작
INCREMENT BY 10 -- 10씩 증가
NOCYCLE -- 반복 X (기본값)
NOCACHE; -- 캐시 X (CACTH 20 기본값)

-- EMP_TEMP 테이블에 사원 정보 삽입
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL, '홍길동'); -- 223
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL, '고길동'); -- 233
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL, '김길동'); -- 243

SELECT * FROM EMP_TEMP;

---

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

-- SEQ_TEMP 를 1씩 증가하는 형태로 변경
ALTER SEQUENCE SEQ_TEMP
INCREMENT BY 1;

INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL, '이길동'); -- 244
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL, '박길동'); -- 245
INSERT INTO EMP_TEMP VALUES(SEQ_TEMP.NEXTVAL, '최길동'); -- 246

SELECT * FROM EMP_TEMP;

-- 테이블(EMP_TEMP), 뷰(V_DCOPY2), 시퀀스(SEQ_TEMP) 삭제
DROP TABLE EMP_TEMP;
DROP VIEW V_DCOPY2;
DROP SEQUENCE SEQ_TEMP;

---

INDEX(색인)

SQL 명령문 중 SELECT의 처리 속도를 향상 시키기 위해
컬럼에 대해서 생성하는 객체

• 인덱스 내부 구조는 B* 트리 형식으로 되어있음.

인덱스 장점

• 이진 트리 형식으로 구성되어 있어 자동 정렬 및 검색 속도가 빠름.
• 조회 시 전체 테이블 내용을 조회하는 것이 아닌
  인덱스가 지정된 컬럼만을 이용해서 조회하기 때문에
  시스템 부하가 낮아져 전체적인 성능이 향상된다.

인덱스 단점

• 데이터 변경(INSERT, UPDATE, DELETE) 작업 빈번한 경우 오히려 성능이 저하되는 문제가 발생.
• 인덱스도 하나의 객체이다보니 이를 저장하기 위한 별도 공간.
• 인덱스 생성 시간이 필요

CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명 (컬럼명, 컬럼명, ... | 함수명, 함수계산식);

인덱스가 자동으로 생성되는 경우

--> PK 또는 UNIQUE 제약조건이 설정되는 경우

-- 인덱스를 이용한 검색 방법
--> WHERE절에 인덱스가 지정된 컬럼을 언급하면 된다.

SELECT * FROM EMPLOYEE
WHERE EMP_ID = 215; -- 인덱스 사용 O

SELECT * FROM EMPLOYEE
WHERE EMP_NAME = '대북혼'; -- 인덱스 사용 X

-- 인덱스 확인용 테이블 생성
CREATE TABLE TB_IDX_TEST(
TEST_NO NUMBER PRIMARY KEY, -- 자동으로 인덱스가 생성됨.
TEST_ID VARCHAR2(20) NOT NULL
);

-- TB_IDX_TEST 테이블에 샘플데이터 100만개 삽입 (PL/SQL 사용)
BEGIN
FOR I IN 1..1000000
LOOP
INSERT INTO TB_IDX_TEST VALUES( I , 'TEST' || I );
END LOOP;

COMMIT;

END;

-- 샘플데이터 100만개 확인

SELECT COUNT(*) 샘플데이터 FROM TB_IDX_TEST;

-- 'TEST500000' 찾기
SELECT * FROM TB_IDX_TEST
WHERE TEST_ID = 'TEST500000'; -- INDEX 사용 X
--> 20ms

SELECT * FROM TB_IDX_TEST
WHERE TEST_NO = 500000; -- INDEX 사용 O
--> 0 ~ 2ms