VIEW (논리적 가상 테이블)
SELECT문의 실행결과를 저장하는 객체
장점
1. 복잡한 SELECT문을 쉽게 재사용 가능
2. 테이블의 진짜 모습을 감출 수 있어 보안상 유리
**주의사항
1. 가상의 테이블이기 때문에 ALTER 사용 불가능
2. VIEW를 이용한 DML은 제약이 많이 따르기 때문에 SELECT(조회)하는 용도로 보통 사용
CREATE [OR REPLACE][FORCE | NOFORCE] VIEW 뷰이름 [(alias[,alias]...]
AS subquery
[WITH CHECK OPTION][WITH READ ONLY];
- OR REPLACE 옵션: 기존에 동일한 뷰 이름이 존재하는 경우 덮어쓰고, 존재하지 않으면 새로 생성
- FORCE / NOFORCE 옵션
- FORCE: 서브쿼리에 사용된 테이블이 존재하지 않아도 뷰 생성
- NOFORCE: 서브쿼리에 사용된 테이블이 존재해야만 뷰 생성(기본값)
- WITH CHECK OPTION 옶션: 옵션을 설정한 컬럼의 값을 수정 불가능하게 함
- WITH READ ONLY 옵션: 뷰에 대해 조회만 가능(DML 수행 불가)
주의
1. VIEW 테이블 생성 권한 부여 잊지 않기
2. 별칭이 적용되면 기존 컬럼명 대신 별칭 사용
3. VIEW에 INSERT하면 원본 테이블에도 영향을 미치기 때문에 DML 되도록 사용하지 않는다.
SEQUENCE 생성
순차적 번호 자동 발생기 역할의 객체
PRIMARY KEY 컬럼에 사용될 값을 생성하는 용도로 사용
1) 시퀀스명.NEXTVAL : 다음 시퀀스 번호를 얻어옴 (INCREMENT BY 만큼 증가된 값)
단, 시퀀스 생성 후 첫 호출인 경우 START WITH 값을 얻어옴
2) 시퀀스명.CURRVAL : 현재 시퀀스 번호 얻어옴
단, 시퀀스 생성 후 NEXTVAL 호출 없이 CURRVAL 호출하면 오류 발생
SEQUENCE는 ROLLBACK을 수행해도 값이 원래대로 돌아가지 않는다 (계속 증가된 상태 유지)
SEQUENCE 변경 시 증가값은 변경 가능하지만, 시작번호는 변경 불가능하다
-> 삭제 후 재생성
INDEX (색인)
SQL 명령문 중 SELECT 처리 속도를 향상시키기 위해 컬럼에 대해서 생성하는 객체
장점
1. 이진 트리 형식으로 구성되어 있어 자동 정렬 및 검색 속도가 빠름
2. 조회 시 전체 테이블 내용을 조회하는 것이 아닌 인덱스가 지정된 컬럼만을 이용해서 조회하기 때문에 시스템 부하가 낮아져 전체적인 성능이 향상된다.
단점
데이터 변경(UPDATE, INSERT, DELETE) 작업이 빈번한 경우 오히려 성능이 떨어진다.
*인덱스 자동 생성 경우:PK 또는 UNIQUE 제약조건이 설정된 경우
10_VIEW, SEQUENCE, INDEX
/* 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 수행 불가)
*/
-- EMPLOYEE 테이블에서 모든 사원의 사번, 이름, 부서명, 직급명 조회
SELECT EMP_NO, EMP_NAME, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN JOB USING (JOB_CODE);
--> 자주 사용하는데 매번 쓰기 힘들다 -> VIEW를 생성
-- 사번, 이름, 부서명, 직급명 VIEW 생성
CREATE VIEW V_EMP
AS SELECT EMP_NO, EMP_NAME, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN JOB USING (JOB_CODE);
-- ORA-01031: 권한이 불충분합니다
-- View V_EMP이(가) 생성되었습니다.
--> 각자 이니셜 계정에 VIEW 생성 권한을 부여
-- (실행 시 관리자 계정으로 할 것)
GRANT CREATE VIEW TO khj; --Grant을(를) 성공했습니다.
--> 다시 이니셜 계정으로 접속 -> 뷰 생성 구문 다시 실행
-- 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
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN JOB USING (JOB_CODE);
-- ORA-00955: 기존의 객체가 이름을 사용하고 있습니다.
--> OR REPLACE 옵션 사용
-- 별칭이 적용된 VIEW에서 직급이 '대리'인 직원만 조회
--> VIEW에서 조회된 컬럼의 이름을 조건절에 사용해야 한다.
SELECT * FROM V_EMP
WHERE 직급명 = '대리';
------------------------------------------------------------------------------------
-- * VIEW를 이용한 DML 확인 *
-- 테이블 복사
CREATE TABLE DEPT_COPY2
AS SELECT * FROM DEPARTMENT;
SELECT * FROM DEPT_COPY2;
-- 복사한 테이블을 이용해서 VIEW를 생성
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'); --1 행 이(가) 삽입되었습니다.
-- 삽입 확인
SELECT * FROM V_DCOPY2; --> VIEW에 'D0'가 삽입된 걸 확인
--> 가상의 테이블이 VIEW에 데이터 삽입이 가능한 걸까? NO
-- 원본 테이블 확인
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; --ADD 쓰지 않게 유의
-- 현 상태에서 다시 VIEW를 이용한 INSERT 수행
INSERT INTO V_DCOPY2 VALUES('D0','L3');
-- ORA-01400: NULL을 ("KHJ"."DEPT_COPY2"."DEPT_TITLE") 안에 삽입할 수 없습니다
-- VIEW를 이용한 INSERT 시 원본 테이블에 삽입이 된다.
-- 원본 테이블 삽입 시 VIEW INSERT 구문이 미포함된 컬럼에는 NULL이 저장된다.
-- 그런데 DEPT_TITLE 컬럼에 NOT NULL 제약조건이 설정되어 있음
--> 오류 발생 -> VIEW를 이용한 INSERT 실패
-- 결론: VIEW에서 DML구문 사용하지 마세요
-------------------------------------------------------------------------------------
-- * WITH READ ONLY 옵션 *
CREATE OR REPLACE VIEW V_DCOPY2
AS SELECT DEPT_ID, LOCATION_ID FROM DEPT_COPY2
WITH READ ONLY; -- 읽기 전용의 VIEW 생성(DML X)
INSERT INTO V_DCOPY2 VALUES('D0','L1');
-- ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.
---------------------------------------------------------------------------------------
/* SEQUENCE(순서, 연속)
- 순차적 번호 자동 발생기 역할의 객체
EX) 1 2 3 4 5 6 7 8 9...
*** SEQUENCE를 사용하는 방법 : PRIMARY KEY 컬럼에 사용될 값을 생성하는 용도로 사용 ***
[작성법]
CREATE SEQUENCE 시퀀스이름
[START 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 호출하면 오류 발생
*/
-- 테이블 복사
CREATE TABLE EMPLOYEE_COPY4
AS SELECT EMP_ID, EMP_NAME FROM EMPLOYEE;
-- EMPLOYEE_COPY4의 EMP_ID 컬럼에 PK 제약조건 추가
ALTER TABLE EMPLOYEE_COPY4
ADD PRIMARY KEY(EMP_ID);
SELECT * FROM EMPLOYEE_COPY4;
-- 223부터 시작하여 5씩 증가하는 시퀀스 생성
CREATE SEQUENCE SEQ_EMP_ID
START WITH 223
INCREMENT BY 5;
-- Sequence SEQ_EMP_ID이(가) 생성되었습니다.
-- NEXTVAL 호출 없이 CURRVAL 호출하면 발생하는 오류 확인
SELECT SEQ_EMP_ID.CURRVAL FROM DUAL;
-- ORA-08002: 시퀀스 SEQ_EMP_ID.CURRVAL은 이 세션에서는 정의 되어 있지 않습니다
-- NEXTVAL 호출1
SELECT SEQ_EMP_ID.NEXTVAL FROM DUAL; -- 최초 호출 == START WITH 값 조회 == 223
-- NEXTVAL 호출2
SELECT SEQ_EMP_ID.NEXTVAL FROM DUAL; --228 : INCREMENT BY 만큼 증가한 값 조회
-- CURRVAL 호출
SELECT SEQ_EMP_ID.CURRVAL FROM DUAL; -- NEXTVAL 호출 후 CURRVAL 호출 시 오류 없음
-- 실제 사용
SELECT * FROM EMPLOYEE_COPY4;
INSERT INTO EMPLOYEE_COPY4 VALUES(SEQ_EMP_ID.NEXTVAL, '홍길동'); -- 228 + 5
INSERT INTO EMPLOYEE_COPY4 VALUES(SEQ_EMP_ID.NEXTVAL, '김길동'); -- 233 + 5
INSERT INTO EMPLOYEE_COPY4 VALUES(SEQ_EMP_ID.NEXTVAL, '박길동'); -- 238 + 5
SELECT * FROM EMPLOYEE_COPY4;
SELECT SEQ_EMP_ID.CURRVAL FROM DUAL; -- 243 (현재 시퀀스)
-- 시퀀스는 ROLLBACK을 수행한다고 해서 값이 원래대로 돌아가지 않는다.
--> 계속 증가된 상태 유지
ROLLBACK;
SELECT * FROM EMPLOYEE_COPY4;
SELECT SEQ_EMP_ID.CURRVAL FROM DUAL; -- 243
--------------------------------------------------------------------------------
-- SEQUENCE 변경 (ALTER)
/*
[작성법]
ALTER SEQUENCE 시퀀스이름
[INCREMENT BY 숫자] -- 다음 값에 대한 증가치, 생략하면 자동 1이 기본
[MAXVALUE 숫자 | NOMAXVALUE] -- 발생시킬 최대값 지정 (10의 27승 -1)
[MINVALUE 숫자 | NOMINVALUE] -- 최소값 지정 (-10의 26승)
[CYCLE | NOCYCLE] -- 값 순환 여부 지정
[CACHE 바이트크기 | NOCACHE] -- 캐쉬메모리 기본값은 20바이트, 최소값은 2바이트
--> 시퀀스 변경 시 시작번호는 변경할 수 없다.
* 시퀀스를 잘못 다루어 번호가 공백이 있어서 다시 처음부터 시작하고 싶은 경우
--> 시퀀스 삭제 후 재생성
*/
-- SEQ_EMP_ID의 증가값을 5 -> 1 변경
ALTER SEQUENCE SEQ_EMP_ID
INCREMENT BY 1; --Sequence SEQ_EMP_ID이(가) 변경되었습니다.
SELECT SEQ_EMP_ID.NEXTVAL FROM DUAL;
-- SEQUENCE 삭제
DROP SEQUENCE SEQ_EMP_ID;
-- VIEW 삭제
DROP VIEW V_DCOPY2;
--------------------------------------------------------------------------------
/*INDEX(색인)
- SQL 명령문 중 SELECT 처리 속도를 향상시키기 위해
컬럼에 대해서 생성하는 객체
- 인덱스 내부 구조는 B* 트리 형식으로 되어있음
* 인덱스 장점
- 이진 트리 형식으로 구성되어 있어 자동 정렬 및 검색 속도 빠름
- 조회 시 전체 테이블 내용을 조회하는 것이 아닌
인덱스가 지정된 컬럼만을 이용해서 조회하기 때문에
시스템 부하가 낮아져 전체적인 성능이 향상된다.
* 인덱스 단점
- 데이터 변경(UPDATE, INSERT, DELETE) 작업이 빈번한 경우 오히려 성능이 저하되는 문제 발생
-> 인덱스도 하나의 객체이다 보니 이를 저장하기 위한 별도 공간이 필요하고,
인덱스 생성시간도 필요하다.
--인덱스 생성 방법--
[작성법]
CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명 (컬럼명, 컬럼명, ... | 함수명, 함수계산식);
-- 인덱스가 자동으로 생성되는 경우
--> PK 또는 UNIQUE 제약조건이 설정되는 경우
*/
SELECT * FROM EMPLOYEE_COPY4;
-- EMPlOYEE_COPY4 테이블의 EMP_NAME 컬럼에 INDEX 생성
CREATE INDEX ECOPY4_NAME_IDX
ON EMPLOYEE_COPY4(EMP_NAME);
-- Index ECOPY4_NAME_IDX이(가) 생성되었습니다.
SELECT * FROM EMPLOYEE_COPY4;
--> 인덱스를 사용하지 않은 검색
-- *** 인덱스를 이용한 조회(검색)방법 ***
--> WHERE절에 인덱스가 추가된 컬럼이 언급되면 자동으로 INDEX가 활용됨
SELECT * FROM EMPLOYEE_COPY4
WHERE EMP_NAME != '0';
--> 데이터가 너무 적어서 차이가 거의 없다.
-- 인덱스 확인용 테이블 생성
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;
/
SELECT COUNT(*) FROM TB_IDX_TEST;
-- 인덱스 사용 X
SELECT * FROM TB_IDX_TEST
WHERE TEST_ID = 'TEST500000'; -- 0.016초
SELECT * FROM TB_IDX_TEST
WHERE TEST_NO = 500000; --0.002초
-- 테스트용 테이블 삭제
DROP TABLE TB_IDX_TEST;
-- 인덱스 삭제
DROP INDEX ECOPY4_NAME_IDX;
개발 일지