Oracle DB의 INDEX와 시퀀스

## index

-- 검색속도 향상
-- 기본키, 유일키 자동 인덱스 생성

## 인덱스 생성

CREATE TABLE EMP1 AS SELECT * FROM EMP;

CREATE INDEX EMP_ID_IDX ON EMP1(EMPLOYEE_ID);

SELECT * FROM EMP WHERE EMPLOYEE_ID=105;
==> 조건 컬럼에 인덱스가 없으므로 풀스캔 한다.

SELECT * FROM EMP1 WHERE EMPLOYEE_ID=105;
==> 조건 컬럼에 인덱스가 생성되어 있으므로 인덱스 검색하여 빠르게 접근
==>인덱스가 있는 두번째 쿼리가 응답 빠르다.

## 인덱스 수정 안함 --> 재건한다.

ALTER INDEX EMP_ID_IDX REBUID;

## 인덱스 삭제

DROP INDEX 인덱스명;

## 인덱스 종류 : 성격에 따른 종류

-- 고유 인덱스 : 오라클이 자동 생성-> 기본키, 유일키
-- 비고유 인덱스 : 사용자가 생성 -> 조건 검색에 자주 사용하는 컬럼으로 생성

## 인덱스 종류 : 형태에 따른 종류

-- 단일 인덱스 : 컬럼 하나
-- 결합 인덱스 : 컬럼 두개 이상
    ==> 검색조건이 두개 이상 일때
-- 함수 기반 인덱스

    검색 조건으로 함수 사용시

    SELECT * FROM EMP WHERE SALARY*12 => 40000;

    ==> SALARY 컬럼에 인덱스가 있어도 컬럼이 변형 되었으므로 인덱스 사용안함. 풀스캔
    ==> 해결책 : 함수 기반 인덱스를 생성

    CREATE INDEX EMP_SAL12_IDX ON EMP1(SALARY*12);
    CREATE INDEX EMP_LOW_EMAIL_IDX ON EMP1(LOWER(EMAIL));

--------------------------------------------------------------------------------------

## 인덱스를 만들면 효과적인 경우

-- 해당 컬럼이 유일키
-- 분포도가 5~10% 이내 일 경우 효과적
-- 테이블 클 경우

## 인덱스를 만들면 비효과적인 경우

-- 테이블이 작을 경우
-- 업데이트가 잦은 경우
-- 분포도가 큰 경우

## 시퀀스
-- SEQUENCE
-- PRIMARY KEY를 위한 일련번호 발생시키는 객체
-- 번호가 일관성을 보장하지 않는다!

# 시퀀스 생성
--1부터 1씩 무한대로 증가하는 시퀀스

CREATE SEQUENCE TEST_A_SEQ;

CREATE TABLE TEST(
A NUMBER(5) PRIMARY KEY,
B NUMBER(3)
);

# 시퀀스 사용
-- 시퀀스명.NEXTVAL ===> 주로 사용
-- 시퀀스명.CURRVAL

INSERT INTO TEST VALUES (TEST_A_SEQ.NEXTVAL,10);

# 시퀀스 옵션
CREATE SEQUENCE TEST_A_SEQ
START WITH 1
INCREMENT BY 1
MAXVALUE 99999;

# 시퀀스 수정
-- START WITH 값을 제외하고 다른 옵션은 수정가능

ALTER SEQUENCE TEST_A_SEQ
INCREMENT BY 100;

# 시퀀스 삭제

DROP SEQUENCE TEST_A_SEQ;

-----------------------------------------

# 시퀀스 응용

주문번호 :년월일_순차번호 => 20220107_00001

CREATE TABLE TEST (
A CHAR(13) PRIMARY KEY,
B NUMBER(5)
);

CREATE SEQUENCE TEST_A_SEQ;

INSERT INTO TEST VALUES(
TO_CHAR(SYSDATE,'YYYYMMDD') ||'_'|| 
LPAD(TEST_A_SEQ.NEXTVAL,4,'0'),
1000);

#시퀀스를 사용하지 않고 일련번호 만들기
-- 부서번호와 같은 일관성이 보장되어야 하는 경우
-- 힌트 : 현재 최고 부서번호 + 1
          : 줄번호 + 1

DROP TABLE TEST;

CREATE TABLE TEST (
A CHAR(13) PRIMARY KEY,
B NUMBER(5)
);

INSERT INTO TEST VALUES(
(SELECT NVL(MAX(A),0) FROM TEST)+1, 1000);

INSERT INTO TEST VALUES(
(SELECT COUNT(*) FROM TEST)+1,1000);

----------------------------------------------------------------

# 시노임:SYNONYM
-- 객체에 대한 동의어
-- 긴 객체명을 간단히 사용하고자 할 때
-- 다른 유저의 객체를 간단히 참조할 때

# 시노임 생성

CREATE SYNONYM LOC FOR LOCATIONS;

CREATE [PUBLIC/PRIVATE] SYNONYM LOC FOR LOCATIONS;

--PUBLIC : 모든 사용자가 접근가능
--PRIVATE : 특정 사용자(오너) 접근가능 ==> 생략시 기본값

# 시노임 확인

SELECT * FROM USER_SYNONYMS;

-------------------------------------------------------------------------------

# VIEW 

-- 뷰는 테이블과 유사하고 테이블처럼 사용가능
-- 뷰는 물리 테이블이 아닌 가상 테이블이다
-- 쿼리를 저장한 객체

# 뷰 생성

CREATE VIEW EMP60_VU
AS 
SELECT * FROM EMP WHERE DEPARTMENT_ID=60;

# 뷰 사용
-- 테이블 처럼 사용
-- FROM 절
-- DML 작업이 가능 (예외도 있음)

SELECT * FROM EMP EMP60_VU;

# 뷰 확인
SELECT * FROM USER_VIEWS;

# 뷰의 사용 목적
--보안 관리
  보안등급에 맞춰서 컬럼 및 범위를 정하고 PRIVILEGE 부여 
  연산결과만 제공하고 알고리즘은 숨기기 위해서 사용
  검색용으로만 사용하기 위해서 : DML 작업을 원천적으로 봉쇄
  테이블명이나 컬럼명을 숨기기 위해서 사용

-- 사용 편의
   검색조건의 단순화를 위해 사용
   JOIN 문장의 단순화를 위해 사용

CREATE VIEW DEPT_CNT_VU
AS
SELECT DEPARTMENT_NAME, COUNT(*) AS 인원수
FROM DEPT,EMP
WHERE DEPT.DEPARTMENT_ID=EMP.DEPARTMENT_ID
GROUP BY DEPARTMENT_NAME
HAVING COUNT(*)>=0
ORDER BY 1;

SELECT * FROM DEPT_CNT_VU

# 읽기 전용 뷰 생성
CREATE VIEW EMP60_VU
AS
SELECT * FROM EMP WHERE DEPARTMENT_ID=60
WITH READ ONLY;

# 뷰 수정
-- 뷰는 수정하지 않음
-- ALTER 사용하지 않음

CREATE OR REPLACE VIEW EMP60_VU
AS
SELECT * FROM EMP WHERE DEPARTMENT_ID=60;

# 뷰 생성 권한 : CREATE VIEW

=========================================

# 뷰의 종류
단순 뷰 복합 뷰
-----------------------------------------------------------------------
테이블 수 ONE ONE OR MORE
함수 사용 NO YES
그룹 사용 NO YES
-----------------------------------------------------------------------
DML 연산 YES NO

단순뷰

CREATE VIEW T_VU
AS
SELECT LAST_NAME, SALARY
FROM EMP;

복합뷰

CREATE VIEW DEPT_CNT_VU
AS
SELECT DEPARTMENT_NAME, COUNT(*) AS 인원수
FROM DEPT,EMP
WHERE DEPT.DEPARTMENT_ID=EMP.DEPARTMENT_ID
GROUP BY DEPARTMENT_NAME
HAVING COUNT(*)>=0
ORDER BY 1;

#단순뷰를 통한 DML 작업

CREATE TABLE TEST(ID, NAME, SALARY)
AS
SELECT EMPLOYEE_ID,LAST_NAME,SALARY
FROM EMP
WHERE DEPARTMENT_ID=60;
===>원본 테이블

CREATE VIEW TEST_VU
AS
SELECT ID, NAME
FROM TEST;

INSERT INTO TEST_VU VALUES(200,'AAA');