[DB] VIEW, SEQUENCE, INDEX

✍VIEW 

: SELECT문의 실행 결과(RESULT SET)을 저장하는 객체

논리적 가상 테이블 (실행결과는 진짜 테이블이 아닌 조회 결과이다.)
➡ 테이블의 모양을 하고 있지만 실제 값을 저장하고 있진 않는다.

---

1. VIEW 사용 목적

- 대체로 조회 용도로만 사용(노출은 되어도 악의적인 이용은 못하게 )

 

1) 복잡한 SELECT 문의 쉬운 재사용

: 이렇게 길게 쓸 필요 없이 VIEW 이름만 정해주면된다

VIEW	➡	SELECT * FROM VIEW 이름;
SELECT~ FROM ~ JOIN ~ JOIN ~ WHERE  ~ (SELECT) GROUP BY ~ HAVING ~ ORDER BY ~

 

2) 테이블의 진짜 모습을 감출 수 있어 보안상 유리

:  테이블의 진짜 이름을 감춰서 바이러스 프로그램으로부터 보호

ex) DDOS : 트래픽을 강제적으로 올려서 느리게 만든다. 
       SQL INJECTION : 통신 중 SQL 구문을 변경하는 것

---

2. VIEW 사용 시 주의 사항

 

• ALTER 구문 사용 불가 (가상 테이블이기때문에 못한다. )
• VIEW를 이용한 DML(INSERT, UPDATE, DELETE)가 가능하지만 제약이 많아 보통 조회용도로만 사용

---

3. 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) [(alias[,alias]...] : 이름 변경하는건데 subquery에서 주로 변경한다.

4) WITH CHECK OPTION 옵션 : 옵션을 설정한 컬럼의 값을 수정 불가능하게 함. (조회용으로 많이 사용해서 자주 사용하진 않는다.)
5) WITH READ ONLY 옵션 : 읽기 전용, 뷰에 대해 조회만 가능 (DML 수행 불가)

 

예제 ( 뷰 생성 권한 부여되었다는 전제)

CREATE VIEW V_EMPLOYEE AS
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN JOB USING (JOB_CODE);

-- 생성된 V_EMPLOYEE를 이용하여 조회
SELECT * FROM V_EMPLOYEE;

➡ VIEW 라는 의미로 앞에 V를 쓰면 구분하기 편하다. 필수 X

 

1) VIEW 문제점

예제

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

--DEPT_COPY2 테이블에서 DEPT_ID, LOCATION_ID만 조회하는 VIEW 생성
CREATE VIEW V_DCOPY2 AS
SELECT DEPT_ID, LOCATION_ID FROM DEPT_COPY2;

-- 생성 확인
SELECT * FROM V_DCOPY2;

-- V_DCOPY2 뷰를 이용해서INSERT 진행하기
INSERT INTO V_DCOPY2 VALUES('D0', 'L5');
-- 1 행 이(가) 삽입되었습니다. == VIEW를 이용해서 INSERT 가능

➡ VIEW를 이용해 INSERT를 진행하게 되면 INSERT되지만 VIEW에 삽입된것은 아니다!
 

VIEW는 원본 테이블의 조회 결과를 저장하고, 보여주는 역할인 가상 테이블로 실제 데이터를 저장하지 못한다. 
➡ 삽입된 데이터는 VIEW가 아닌 원본 테이블에 삽입이 된다. 

 

 

2) INSERT를 하지 못하도록 WITH READ ONLY 옵션 추가

- 읽기 전용으로만 하는 이유

: 위의 예제에서  데이터가 입력되지 않은 컬럼은 삽입은 확인 되지만 NULL 값으로 저장된다.

만약, 데이터가 입력되지 않은 컬럼에 NOT NULL 제약조건이 있었다면 오류 발생 ==> 데이터 무결성 침해

이러한 문제때문에 대체로 읽기전용으로만 사용한다.

 

예제(WITH READ ONLY)

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

INSERT INTO V_DCOPY2 VALUES('D0', 'L5');
-- ORA-42399: cannot perform a DML operation on a read-only view
-- INSERT (DML)불가

 

3) VIEW 삭제

DROP VIEW 뷰 이름;

---

✍ SEQUENCE

: 순차적 번호 자동 발생기 역할의 객체 
like.은행 번호표 

 

SEQUENCE 사용 이유 

: PRIMARY KEY 컬럼에 사용될 값으로써 주로 사용

(PRIMARY KEY = UNUQUE+ NOT NULL)

---

1. SEQUENCE  생성 방법

표현식

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) 생성하기

CREATE SEQUENCE SEQ_EMP_ID -- 사번 생성용 시퀀스
START WITH 223                      --넘버가 222까지 있어서 그 뒤로부터 시작
INCREMENT BY 2;                     --2씩 번호 증가

➡ 첫번째 문장 이후에는 전부 옵션이다.

➡ START WITH는 변경 불가 / 재설정 필요 시 기존 시퀀스 DROP 후 재생성

---

2. SEQUENCE 사용 방법

 

1) 시퀀스명.NEXTVAL 

: 다음 시퀀스 번호를 얻어온다.
 단, 시퀀스 생성 후 첫 호출인 경우 START WITH 값을 얻어온다 .

SELECT SEQ_EMP_ID.NEXTVAL FROM DUAL;
-- 실행할 때 마다 2씩 증가
-- 계속 실행해보면 처음보다 2씩 증가한 값이 나온다 223- 225 - 227

SEQ_EMP_ID ->생성 예제에서 만든 시퀀스명

2)  시퀀스명.CURRVAL

 : 현재 시퀀스 번호를 얻어온다.

SELECT SEQ_EMP_ID.CURRVAL FROM DUAL;

 

실제 사용방법 예시 

응용) 학번, 사번 

1. 테이블 복사
CREATE TABLE EMPLOYEE_COPY4 AS
SELECT EMP_ID, EMP_NAME FROM EMPLOYEE;

2. 복사한 테이블 확인
SELECT * FROM EMPLOYEE_COPY4;

3. 복사한 테이블에 삽입 
INSERT INTO EMPLOYEE_COPY4 VALUES (SEQ_EMP_ID.NEXTVAL , '홍길동');
INSERT INTO EMPLOYEE_COPY4 VALUES (SEQ_EMP_ID.NEXTVAL , '고길동');
INSERT INTO EMPLOYEE_COPY4 VALUES (SEQ_EMP_ID.NEXTVAL , '장장장');

-- 매번 EMP_ID 수를 바꿀 수 없으니 시퀀스를 이용

🔎 시퀀스 특징

: 시퀀스는 오류 또는 롤백 등과 관계 없이 NEXTVAL 구문이 수행되면 시퀀스 숫자는 무조건 증가한다. 

- ROLLBACK 후 INSERT 내용은 사라졌지만 시퀀스 번호는 계속 증가한 상태
- 어차피 중복되지만 않으면 되니깐 굳이 연이어질 필요는 없다.

---

3. SEQUENCE 변경

표현식

ALTER SEQUENCE 시퀀스이름
[INCREMENT BY 숫자]
[MAXVALUE 숫자 | NOMAXVALUE]
[MINVALUE 숫자 | NOMINVALUE] 
[CYCLE | NOCYCLE]
[CACHE 바이트크기 | NOCACHE];

예제

ALTER SEQUENCE SEQ_EMP_ID
INCREMENT BY 1;
-- Sequence SEQ_EMP_ID이(가) 변경되었습니다.

SELECT SEQ_EMP_ID.NEXTVAL FROM DUAL;

 

1) SEQUENCE 삭제

DROP SEQUENCE 시퀀스 이름;

---

✍ INDEX

:  SQL 명령문 중 SELECT의 처리 속도를 향상시키기 위해서 컬럼에 대해 생성하는 객체 
참고) INDEX 내부 구조는 B*(바이너리 스타) 트리형식으로 구성되어있다.  => 검색에 특화되어있다.

색인, 순서라고 하는데 책 속의 낱말이나 구절, 또 이에 관련한 지시자를 찾아보기 쉽도록 일정한 순서로 나열한 목록을 가리킨다

장점	- 이진트리 형식으로 구성되어 자동 정렬 및, 검색 속도가 빨라짐 - 시스템에 걸리는 부하를 줄여 시스템 전체 성능 향상
단점	- 인덱스를 추가 하기위한 별도의 저장공간이 필요 - 인덱스를 생성하는데 시간이 걸림 - 주로 SELECT 용도이기 때문에 데이터 변경작업(DML(INSERT/UPDATE/DELETE))이    빈번한 경우에는 오히려 성능이 저하됨

---

1. INDEX  생성 방법

[표현식] 

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

 

 

---

2. INDEX  사용 방법

: 인덱스를 추가했다고 해서 자동적으로 사용되는 것이 아니다

 

1) 인덱스가 자동으로 생성되는 경우 --> PK 또는 UNIQUE 제약 조건이 설정되는 경우

: 원하는걸 더 빨리 찾을 수 있다 .

CREATE TABLE TB_IDX_TEST(
    TEST_NO NUMBER PRIMARY KEY, 
    TEST_ID VARCHAR2(20) NOT NULL
);

➡ PK 또는 UNIQUE 제약 조건이 설정되는 경우: 인덱스가 자동으로 생성

 

[예제] 

-- 인덱스 속도 확인용 테스트 테이블 생성
CREATE TABLE TB_IDX_TEST(
    TEST_NO NUMBER PRIMARY KEY,
    TEST_ID VARCHAR2(20) NOT NULL
);
➡ PK 또는 UNIQUE 제약 조건이 설정되는 경우: 인덱스가 자동으로 생성

-- 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;
/

-- INSERT 100만개 확인
SELECT COUNT(*) FROM TB_IDX_TEST;

-- INDEX가 적용된 TEST_NO 컬럼에서 5000 조회
SELECT * FROM TB_IDX_TEST
WHERE TEST_NO = 5000; 

➡ SELECT * FROM TB_IDX_TEST WHERE TEST_ID = 'TEST5000'; 
한번 할때는 0.024  근데 두번쨰부터는 메모리에 남아있어서 0.011초 나옴

➡ 자주 SELECT 조건으로 사용되는 컬럼이 있을 경우
INDEX를 생성해서 해당 컬럼을 지정해 주는 것이 좋다. 

 

참고)

JOIN시 PK, FK의 관계를 확인하기 위해서는 테이블의 MODEL에서 FK와 PK를 확인하면 된다.