[DB] VIEW , SEQUENCE, INDEX

▶ VIEW

 

- SELECT 쿼리의 실행 결과를 화면에 저장한 논리적 가상 테이블

- 실제 테이블과는 다르게 실질적 데이터를 저장하고 있진 않지만 사용자는 테이블을 사용하는 것과 동일하게 사용 가능

 

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

▷ VIEW 사용 목적 

 

 1) 복잡한 SELECT문을 쉽게 재사용하기 위해.

 

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

 

▷ VIEW 사용 시 주의 사항 

 

 1) 가상의 테이블(실체 X)이기 때문에 ALTER 구문 사용 불가.

 

 2) VIEW를 이용한 DML(INSERT,UPDATE,DELETE)이 가능한 경우도 있지만

 제약이 많이 따르기 때문에 조회(SELECT) 용도로 대부분 사용.

 

▷ VIEW 옵션

 

OR REPLACE 옵션	생성한 뷰가 존재하면 뷰를 갱신함
FORCE/NOFORCE 옵션	FORCE옵션은 기본 테이블이 존재하지 않더라도 뷰 생성 NOFORCE 옵션이 기본 값으로 지정되어 있음
WITH CHECK OPTION 옵션	옵션을 설정한 컬럼의 값을 수정 불가능하게 함(삭제는 가능)
WITH READ ONLY 옵션	뷰에 대해 조회만 가능하고 삽입, 수정, 삭제 등은 불가능하게 함

▷ VIEW 작성법

 CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름 [컬럼 별칭]
 AS 서브쿼리(SELECT문)
 [WITH CHECK OPTION]
 [WITH READ OLNY];

 

▷ INLINE VIEW

 

일반적으로 FROM절에 사용된 서브쿼리의 결과 화면에 별칭을 붙인 것을 말함
FROM절에 서브쿼리를 직접 사용해도 되고 따로 뷰를 생성 후 FROM절에 생성한 뷰를 사용해도 됨

 

---

 

▶ SEQUENCE

순차적으로 정수 값을 자동으로 생성하는 객체로 자동 번호 발생기 역할을 함

 

시퀀스명.NEXTVAL	다음 시퀀스 번호를 얻어옴. (INCREMENT BY 만큼 증가된 수) 단, 생성 후 처음 호출된 시퀀스인 경우 START WITH에 작성된 값이 반환됨.
시퀀스명.CURRVAL	현재 시퀀스 번호를 얻어옴. 단, 시퀀스가 생성 되자마자 호출할 경우 오류 발생. = 마지막으로 호출한 NEXTVAL 값을 반환

 

▷ SEQUENCE 작성법 (CREATE)

  CREATE SEQUENCE 시퀀스이름
  [STRAT WITH 숫자] -- 처음 발생시킬 시작값 지정, 생략하면 자동 1이 기본
  [INCREMENT BY 숫자] -- 다음 값에 대한 증가치, 생략하면 자동 1이 기본
  [MAXVALUE 숫자 | NOMAXVALUE] -- 발생시킬 최대값 지정 (10의 27승 -1)
  [MINVALUE 숫자 | NOMINVALUE] -- 최소값 지정 (-10의 26승)
  [CYCLE | NOCYCLE] -- 값 순환 여부 지정
  [CACHE 바이트크기 | NOCACHE] -- 캐쉬메모리 기본값은 20바이트, 최소값은 2바이트
	-- 시퀀스의 캐시 메모리는 할당된 크기만큼 미리 다음 값들을 생성해 저장해둠
	-- --> 시퀀스 호출 시 미리 저장되어진 값들을 가져와 반환하므로 
	--     매번 시퀀스를 생성해서 반환하는 것보다 DB속도가 향상됨.

/*시퀀스 삭제하기*/
DROP SEQUENCE SEQ_TEST_NO;
/* 시퀀스 생성하기 */
CREATE SEQUENCE SEQ_TEST_NO
START WITH 100 -- 시작 번호 100
INCREMENT BY 5 -- NEXTVAL 호출 시 5씩 증가
MAXVALUE 150	 -- 증가 가능한 최대값 150
NOMINVALUE     -- 최소값 없음
NOCYCLE        -- 반복 안함
NOCACHE;       -- 미리 만들어둔 시퀀스 번호 없음

-- 시퀀스 테스트할 테이블 생성
CREATE TABLE TB_TEST(
		TEST_NO NUMBER PRIMARY KEY,
		TEST_NAME VARCHAR2(30) NOT NULL
);

-- 현재 시퀀스 번호 확인하기
SELECT SEQ_TEST_NO.CURRVAL FROM DUAL;
--: ORA-08002: 시퀀스 SEQ_TEST_NO.CURRVAL은 
-- 이 세션에서는 정의 되어 있지 않습니다

--> 발생 원인 : CURRVAL의 정확한 의미는
--  가장 최근 호출된 NEXTVAL의 값을 반환함을 뜻함
  --> NEXTVAL를 호출한 적이 없어서 오류 발생!!

--> 해결 방법 : NEXTVAL를 호출하면 해결
SELECT SEQ_TEST_NO.NEXTVAL FROM DUAL;
-- 시퀀스 생성 후 첫 NEXTVAL == START WITH 값인 100

SELECT SEQ_TEST_NO.CURRVAL FROM DUAL; --100

-- NEXTVAL를 호출할 때마다
-- INCREMENT BY에 작성된 수 만큼 증가
SELECT SEQ_TEST_NO.NEXTVAL FROM DUAL;

-- 처음 : 100
-- 1회 : 105
-- 2회 : 110
-- 3회 : 115
-- 4회 : 120

-- TB_TEST 테이블에 PK 값을 SEQ_TEST_NO 시퀀스로 생성하기
INSERT INTO TB_TEST
VALUES(SEQ_TEST_NO.NEXTVAL, '짱구' ); -- 125

INSERT INTO TB_TEST
VALUES(SEQ_TEST_NO.NEXTVAL, '철수' ); -- 130

INSERT INTO TB_TEST
VALUES(SEQ_TEST_NO.NEXTVAL, '유리' ); -- 135

SELECT * FROM TB_TEST;




--------------------------------
/*UPDATE에서 시퀀스 사용하기*/

-- '짱구'의 PK 컬럼 값을 
-- SEQ_TEST_NO 시퀀스의 다음 생성 값으로 변경하기
UPDATE TB_TEST
SET	
	TEST_NO = SEQ_TEST_NO.NEXTVAL
WHERE TEST_NAME = '짱구';
--> 짱구의 TEST_NO 값을 150까지 증가시키고 나서
--> 다시 한번 더 SEQ_TEST_NO.NEXTVAL 호출

-- ORA-08004: 시퀀스 SEQ_TEST_NO.NEXTVAL exceeds 
-- MAXVALUE은 사례로 될 수 없습니다
--> MAXVALUE 150보다 증가할 수 없다 !!


SELECT * FROM TB_TEST;

 

▷ SEQUENCE 작성법 (ALTER)

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

 

▷ VIEW, SEQUENCE 삭제

-- V_DCOPY2 VIEW 삭제
DROP VIEW V_DCOPY2;

-- SEQ_TEST_NO SEQUENCE 삭제
DROP SEQUENCE SEQ_TEST_NO;

 

---

▶ INDEX

SQL명령문의 처리 속도를 향상시키기 위해서 컬럼에 대해 생성하는 오라클 객체로
내부 구조는 B*트리 형식으로 구성되어 있음

 

장점

 - 이진 트리 형식으로 구성되어 자동 정렬 및 검색 속도 증가.

 

 - 조회 시 테이블의 전체 내용을 확인하며 조회하는 것이 아닌 인덱스가 지정된 컬럼만을 이용해서 조회하기 때문에

시스템의 부하가 낮아짐.

 

단점

 - 데이터 변경(INSERT,UPDATE,DELETE) 작업 시

이진 트리 구조에 변형이 일어남

 

→ DML 작업이 빈번한 경우 시스템 부하가 늘어 성능이 저하됨.

 

- 인덱스도 하나의 객체이다 보니 별도 저장공간이 필요(메모리 소비)

 

- 인덱스 생성 시간이 필요함.

 

▷ INDEX 작성법

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

 

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

/* 관리자 계정 접속 */
ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;

-- 할당된 저장 공간 용량 변경
ALTER USER KH_***
DEFAULT TABLESPACE USERS
QUOTA 200M ON USERS;


/* 다시 KH 계정 접속 */

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


SELECT COUNT(*) FROM TB_IDX_TEST;


/* 인덱스를 사용해서 검색하는 방법!!! 
 * 	-> WHERE절에 INDEX가 지정된 컬럼을 언급하기
 * */

/*인덱스 X*/
-- TEST_ID 가 'TEST500000'인 행 조회하기
SELECT * FROM TB_IDX_TEST
WHERE TEST_ID = 'TEST500000'; -- 0.017

/*인덱스 O*/
-- TEST_NO 가 500000인 행 조회
SELECT * FROM TB_IDX_TEST
WHERE TEST_NO = 500000; -- 0.001
--> 17배 차이 (보통 10~30배 차이)