뷰(View),인덱스(Index)

1. 뷰(View)

1-1 뷰란

• 정의: 뷰(View)는 실제 데이터를 저장하지 않는 가상 테이블입니다. 복잡한 쿼리를 저장하고, 이를 테이블처럼 사용하여 데이터를 조회할 수 있습니다.

• 목적:
  복잡한 쿼리를 간단하게 사용하도록 도와줍니다.
  민감한 데이터를 보호하기 위해 일부 데이터를 감추거나 제한된 형태로 제공할 수 있습니다.
  자주 사용하는 쿼리를 쉽게 재사용할 수 있도록 합니다.

1-2 뷰 생성 및 관리

• 사원 조회 예제
  아래는 EMPLOYEES 테이블에서 job_id가 'ST_CLERK'인 사원의 사번, 이름, 직업, 고용일, 부서아이디를 조회하는 뷰를 생성하는 예제입니다.
  sql코드 :
  -- job_id가 'ST_CLERK'인 사원들의
  -- 사번, 이름, 직업, 고용일, 부서아이디를 뷰로 생성
  -- create view 에서 컬럼생성할때 이름은 내가 정할수 있다
  -- 하지만 select문에서 컬럼은 emplooyee에 있는 그대로 적어줘야한다.
  CREATE VIEW v_emp
  (emp_id, first_name, job_id, hiredate, dept_id)
  AS
  SELECT employee_id, first_name, job_id, hire_date, department_id
  FROM EMPLOYEES e
  WHERE job_id = 'ST_CLERK';

-- 생성된 뷰에서 데이터 조회
SELECT FROM V_EMP ve;
뷰 생성: CREATE VIEW 구문을 사용하여 v_emp라는 뷰를 생성합니다.
조회: 뷰는 테이블처럼 사용 가능하므로 SELECT FROM V_EMP를 통해 데이터를 조회할 수 있습니다.

1-3. 뷰 삭제
뷰를 삭제하려면 DROP VIEW 구문을 사용합니다.
sql 코드 :
-- 뷰 삭제
DROP VIEW V_EMP;

1-4. 커미션 정보 포함한 뷰 생성
커미션 정보가 NULL인 경우 0으로 대체하여 조회하는 뷰를 생성합니다.

sql 코드 :
-- job_id가 'SH_CLERK'인 사원들의 뷰 생성
-- 커미션이 null일 경우 0으로 출력
CREATE VIEW v2_emp
(emp_id, first_name, job_id, hiredate, dept_id, commition_pct)
AS
SELECT employee_id, first_name, job_id, hire_date, department_id, NVL(commission_pct, 0)
FROM EMPLOYEES e
WHERE job_id = 'SH_CLERK';

-- 생성된 뷰에서 데이터 조회
SELECT * FROM V2_EMP ve;
NVL 함수: NULL 값을 0으로 대체하기 위해 사용됩니다.

1-5. 뷰를 통한 데이터 수정
뷰를 통해 데이터를 업데이트할 수 있습니다. 단, 일부 함수로 가공된 데이터는 수정할 수 없습니다.
sql 코드 :
-- 180번 사원의 이름을 Winston으로 수정
UPDATE V2_EMP SET FIRST_NAME = 'Winston'
WHERE EMP_ID = 180;
SELECT FROM V2_EMP ve;
-- 180번 사원의 commition_pct를 0.5로 수정 (불가)
UPDATE V2_EMP SET COMMITION_PCT = 0.5
WHERE EMP_ID = 180;
-- 실제 테이블에서 데이터 확인
SELECT FROM EMPLOYEES e
WHERE EMPLOYEE_ID = 180;
주의사항: NVL 함수 등으로 가공된 컬럼은 직접 수정이 불가합니다. 이는 뷰가 가상 테이블이기 때문입니다.

1-6 읽기 전용 뷰 생성
읽기 전용 뷰는 데이터 수정이 불가하도록 합니다.
sql 코드:
-- 연봉 정보를 포함한 읽기 전용 뷰 생성
CREATE VIEW V3_emp
(emp_id, last_name, annual_sal)
AS
SELECT employee_id, last_name, (salary + NVL(commission_pct, 0)) 12
FROM EMPLOYEES e
WITH READ ONLY;
-- 읽기 전용 뷰에서 데이터 조회
SELECT FROM V3_EMP ve;
-- 수정 시도 (실패)
UPDATE V3_EMP SET LAST_NAME = 'Kim'
WHERE EMP_ID = 100;
WITH READ ONLY: 뷰를 읽기 전용으로 설정하여 수정 불가하게 만듭니다.

1-7. 뷰 활용 사례

• 사례 1: 특정 조건에 맞는 사원 조회
  sql 코드 :
  -- 연봉이 3000 이상, 부서 50번, 직업 'ST_CLERK'인 사원 조회 뷰 생성
  CREATE VIEW V4_emp
  (emp_id, last_name, dept_id, hire_date)
  AS
  SELECT employee_id, last_name, department_id, hire_date
  FROM EMPLOYEES e
  WHERE salary >= 3000
  AND department_id = 50
  AND job_id = 'ST_CLERK';
  -- 뷰 데이터 조회
  SELECT * FROM V4_EMP ve;

• 사례 2: 사원의 이름을 'TJ Olson'으로 표시
  sql 코드
  -- 50번 부서 사원들의 이름을 '이름 성' 형식으로 표시하는 뷰 생성
  CREATE VIEW V5_emp
  (emp_id, name, dept_id, hire_date)
  AS
  SELECT employee_id, first_name || ' ' || last_name AS name, department_id, hire_date
  FROM EMPLOYEES e
  WHERE department_id = 50;
  -- 뷰 데이터 조회
  SELECT * FROM V5_EMP;
  DROP VIEW V5_EMP;
  || 연산자: 두 문자열을 연결하는 데 사용됩니다.

• 사례 3: 부서 이름을 포함한 사원 조회
  sql 코드
  -- 사원 정보에 부서명을 포함하는 뷰 생성
  CREATE VIEW V6_emp
  (emp_id, first_name, dept_id, dept_name)
  AS
  SELECT employee_id, first_name, e.department_id, d.department_name
  FROM EMPLOYEES e
  INNER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;
  -- 뷰 데이터 조회
  SELECT * FROM V6_EMP ve;
  JOIN 사용: 여러 테이블에서 데이터를 조합하여 뷰를 생성할 수 있습니다.

2. 인덱스(Index)
   2-1. 인덱스란?
   정의: 인덱스는 테이블의 데이터를 빠르게 검색할 수 있도록 돕는 구조입니다. 특정 컬럼에 대한 검색 속도를 향상시키기 위해 사용됩니다.
   목적:
   데이터 조회 속도 개선
   대량의 데이터에서 효율적인 검색 수행

2-2. 인덱스 생성 및 관리

• 테이블 복사 및 인덱스 활용
  테스트를 위한 EMPLOYEES 테이블 복사본을 생성하고, 인덱스를 활용합니다.
  sql 코드 :
  -- EMPLOYEES 테이블 복사
  CREATE TABLE EMPLOYEE3 AS SELECT FROM EMPLOYEES e;
  -- EMPLOYEE3 테이블 데이터 조회
  SELECT FROM EMPLOYEE3 e;
  -- 특정 레코드의 rowid 조회 (rowid는 레코드의 고유 주소)
  SELECT rowid, EMPLOYEE_ID
  FROM EMPLOYEE3 e
  WHERE EMPLOYEE_ID = 200;

• 중복 데이터 삽입 및 조회
  sql 코드
  -- 중복 데이터 삽입
  INSERT INTO EMPLOYEE3 e (employee_id, FIRST_NAME, LAST_NAME)
  VALUES (100, 'Kim', 'Chen');
  -- 중복 데이터 조회
  SELECT * FROM EMPLOYEE3 e WHERE EMPLOYEE_ID = 100;

• 고유 인덱스 생성
  sql 코드 :
  -- 고유 인덱스 생성 (유니크 인덱스는 중복 불가)
  CREATE UNIQUE INDEX idx_emp_id
  ON EMPLOYEE3(employee_id);

-- 중복 데이터 삭제 후 고유 인덱스 생성
DELETE FROM EMPLOYEE3 e
WHERE first_name = 'Kim'
AND last_name = 'Chen';
SELECT * FROM EMPLOYEE3 e;
CREATE UNIQUE INDEX idx_emp_id
ON EMPLOYEE3(employee_id);
고유 인덱스: 중복이 허용되지 않는 인덱스입니다. 각 값이 테이블 내에서 고유해야 합니다.

• 비고유 인덱스 생성 = Nonindex
  sql 코드 :
  -- 비고유 인덱스 생성 (중복 허용)
  CREATE INDEX ind_emp_first
  ON EMPLOYEE3(first_name);
  -- 인덱스를 활용한 데이터 조회
  SELECT * FROM EMPLOYEE3 e
  WHERE FIRST_NAME = 'TJ';
  -- 중복 데이터 삽입
  INSERT INTO EMPLOYEE3 e (employee_id, first_name, last_name)
  VALUES (500, 'TJ', 'name');
  비고유 인덱스: 중복이 허용되며, 검색 성능을 향상시키기 위해 주로 사용됩니다.

2-3. 인덱스 활용 사례
인덱스는 주로 조회 성능을 향상시키기 위해 사용되며, 대량의 데이터를 다루는 경우에 유용합니다.
하지만, 인덱스는 삽입, 삭제, 수정 작업에서 성능 저하를 초래할 수 있으므로, 적절한 컬럼에 필요할 때만 생성하는 것이 좋습니다.

• 정리
  이 가이드에서는 SQL의 뷰와 인덱스를 활용하여 데이터베이스에서 복잡한 쿼리를 간단히 하고, 검색 성능을 향상시키는 방법을 설명했습니다. 뷰는 데이터 접근을 단순화하고, 보안을 강화하며, 인덱스는 조회 속도를 개선하지만 DML 작업에 주의가 필요합니다. 이러한 도구들을 적절히 사용하면 데이터베이스 작업의 효율성을 크게 높일 수 있습니다.