[SQL] 집합 연산자 / 인덱스 / 데이터 사전 / 뷰

Gabriela·2023년 7월 10일
sql
0

[SQL] 데이터베이스

목록 보기
10/13
post-thumbnail

‣ 집합

  • 2개 이상의 테이블을 한 번에 조회하는 방식 중 하나이다.
  • 모든 SELECT 절의 칼럼 순서와 타입이 일치해야 한다.

집합 연산자

1) UNION : 합집합, 중복 값은 한 번만 조회
2) UNION ALL : 합집합, 중복 값도 그대로 조회
3) INTERSECT : 교집합, 중복값은 한 번만 조회
4) MINUS : 차집합, 첫 번째 SELECT 결과에서 두 번째 SELECT 결과를 뺀다.

형식

SELECT 칼럼1, 칼럼2, ...
  FROM 테이블1
 집합연산자
SELECT 칼럼1, 칼럼2, ...
  FROM 테이블2
[ORDER BY 정렬]  --정렬을 원한다면 마지막에 한번 가능하다.

‣ 집합 기본문법 예시

⇢ 합집합

  • UNION, UNION ALL
-- UNION 예시2)
SELECT 1, 2
  FROM DUAL
 UNION     -- 중복을 제거한 합집합
SELECT 1, 2
  FROM DUAL;
  
-- UNION ALL 예시)
SELECT 1, 2
  FROM DUAL
 UNION ALL  -- 중복을 그대로 조회하는 합집합
SELECT 1, 2
  FROM DUAL;

-- 사원 테이블과 부서 테이블에 존재하는 모든 부서번호를 조회하기
-- UNION
SELECT DEPARTMENT_ID
  FROM DEPARTMENTS
 UNION      -- 중복 데이터 제거
SELECT DEPARTMENT_ID
  FROM EMPLOYEES;

-- UNION ALL
SELECT DEPARTMENT_ID
  FROM DEPARTMENTS
 UNION ALL  -- 중복 데이터 유지
SELECT DEPARTMENT_ID
  FROM EMPLOYEES;

⇢ 교집합

  • INTERSECT
-- 사원 테이블과 부서 테이블에 모두 존재하는 부서번호 조회하기
-- (사원들이 근무 중인 부서번호만 조회하기)
-- (아래 쿼리를 조인으로 푼다면 내부조인)

SELECT DEPARTMENT_ID
  FROM DEPARTMENTS
INTERSECT
SELECT DEPARTMENT_ID
  FROM EMPLOYEES;

⇢ 차집합

  • MINUS
-- 부서 테이블에 존재하지만 사원 테이블에 존재하지 않는 부서번호 조회하기
-- (유령부서 조회하기)
SELECT DEPARTMENT_ID
  FROM DEPARTMENTS
 MINUS 
SELECT DEPARTMENT_ID
  FROM EMPLOYEES;

⇢ 재귀쿼리를 이용한 활용 예시

  • WITH문과 재귀쿼리 활용
-- 활용1. WITH문과 재귀 쿼리

WITH MY_SUBQUERY(N, TOTAL) AS (        -- N, TOTAL은 MY_SUBQUERY의 칼럼을 의미
        SELECT 1, 1                    -- N=1, TOTAL=1을 의미하는 초기화 서브쿼리
          FROM DUAL
         UNION ALL
        SELECT N + 1, TOTAL + (N + 1)  -- N = N + 1, TOTAL = TOTAL + (N + 1) 방식으로 반복해서 처리되는 부분
          FROM MY_SUBQUERY             -- 조건(WHERE) 없이 이대로만 끝맺으면 무한루프에 빠짐(infinite loop)
         WHERE N < 10
)
SELECT N, TOTAL FROM MY_SUBQUERY;
-- 활용2. WITH문과 재귀 쿼리를 활용한 사원의 레벨 표시하기
-- MANAGER가 몇 명인가에 따른 LVL 표시하기
-- MANAGER가 0명이다 : LVL = 1
-- MANAGER가 1명이다 : LVL = 2

WITH MY_SUBQUERY(LVL, EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID) AS (
    -- 초기값(LVL = 1)을 지정하는 서브쿼리
    SELECT 1, EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID
      FROM EMPLOYEES
     WHERE MANAGER_ID IS NULL
      UNION ALL
    -- 반복해서 호출되는 서브쿼리
    SELECT M.LVL + 1 AS LVL, E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.MANAGER_ID
      FROM EMPLOYEES E INNER JOIN MY_SUBQUERY M  -- LVL이 MY_SUBQUERY에 있는 칼럼임
        ON E.MANAGER_ID = M.EMPLOYEE_ID
)
SELECT LVL, EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID
  FROM MY_SUBQUERY;

‣ 인덱스

◊ ROWID

  • 오라클에서 제공하는 가상 칼럼(PSEUDO COLUMN)이다. (P는 묵음 : 수도)
  • 어떤 행이 어디에 저장되어 있는지 알고 있는 칼럼이다.
  • 실제로 저장된 물리적 저장위치를 확인할 수 있다.

ROWID 확인

SELECT ROWID, EMPLOYEE_ID, FIRST_NAME, LAST_NAME
  FROM EMPLOYEES;

ROWID를 이용한 조회

  • 오라클에서 가장 빠른 조회이나, 실제로 사용하기가 불가능하다.
  • 대신 인덱스를 사용한다.
-- 예시
SELECT *
  FROM EMPLOYEES
 WHERE ROWID = 'AAAR92AAHAAAADOAAE';

◊ 인덱스

  • 빠른 조회를 지원하는 데이터베이스 객체이다.
  • 어떤 데이터가 어떤 ROWID를 가지고 있는지 알고 있다.
  • 기본키(PK)와 중복이 없는 칼럼(UNIQUE)은 자동으로 인덱스가 만들어진다.
  • 인덱스가 많으면 데이터의 삽입/수정/삭제 시 인덱스도 함께 갱신해야 하기 때문에 전체적인 성능이 떨어질 수 있다.

⇢ 인덱스 활용 예시

• 인덱스 만들기 CREATE INDEX

-- 부서 테이블의 부서명(DEPARTMENT_NAME) 칼럼에 인덱스 설정하기
CREATE INDEX DEPT_NAME_IX -- DEPT_NAME_IX -> 인덱스 이름 설정
    ON DEPARTMENTS(DEPARTMENT_NAME);

• 인덱스 DEPT_NAME_IX 삭제하기

DROP INDEX DEPT_NAME_IX;

• 인덱스를 타는 조회

 -- 인덱스를 타는 조회
 SELECT *
   FROM EMPLOYEES
 WHERE EMPLOYEE_ID = 150;  
 	-- PK값을 타고 있기에 빠른 조회가 가능하다

• 인덱스를 타지 않는 조회

-- 인덱스를 안 타는 조회 
--(인덱스 칼럼을 그대로 사용하지 않고 함수를 적용해서 사용하면 인덱스를 타지 않는다.)
SELECT *
  FROM EMPLOYEES
 WHERE TO_CHAR(EMPLOYEE_ID) = '150';  
 	-- 가능하면 함수를 뒤(오른쪽)로 빼는 것이 좋다. 
	-- (=등호 기준으로 가능한 왼쪽은 변경하지 않는게 좋다. -> 성능문제)

◊ 데이터 사전

  • 특정 데이터의 정보를 담고 있는 객체이다.
  • 시스템 카탈로그, 메타데이터라고도 한다.
  • 계정 유형별로 관리한다.
  • 데이터 사전의 Prefix 값
    • DBA_ :가장 많은 정보를 갖고있음
    • USER_ : 유저가 갖고 있는 것
    • ALL_ : 유저가 접근할 수 있는 것
  • 인덱스 정보가 저장되어 있는 데이터 사전
    (이미 만들어진 테이블)
  • DBA_INDEXES, USER_INDEXES, ALL_INDEXES
     -- 현재 사용중인 계정에 저장된 인덱스 보기
 SELECT * FROM USER_INDEXES;
  • 인덱스가 설정된 칼럼 정보가 저장되어 있는 데이터 사전
    (어느 테이블, 어느 칼럼에 인덱스가 주어져 있는지 명확하게 알 수 있다.)
  • DBA_IND_COLUMNS, USER_IND_COLUMNS, ALL_IND_COLUMNS
    SELECT * FROM USER_IND_COLUMNS;
  • 요약

    👉인덱스 장점 : 검색 속도가 빨라질 수 있음
    👉인덱스 단점 : 변경 작업(INSERT, UPDATE, DELETE)이 자주 발생되면 오히려 성능 저하가 될 수 있음

‣ 뷰 VIEW

  • 하나 이상의 기본 테이블이나 다른 뷰(View)를 이용해서 만든 가상 테이블이다.
  • 데이터가 아닌 쿼리문만을 저장하고 있다.
  • 생성된 뷰는 SELECT문을 활용해서 테이블처럼 사용할 수 있다.
  • WITH문 보다는 쓰임새가 넓다 (저장해두고 사용 가능하기 때문)

장점

  • 데이터를 보호할 수 있음(보안상 이점)
  • 사용자가 보고 싶은 데이터만 제공하는 편의성 제공
  • 복잡한 쿼리를 단순하게 호출 : 자주 사용하고 복잡한 쿼리문을 뷰로 만들어 두면 쉽게 호출해서 사용할 수 있다.
  • 뷰 사용으로 인한 DB 성능상 이점은 없음

⇢ 뷰 생성

-- 뷰 만들기
CREATE VIEW V_EMP AS (   -- (V_EMP : 생성할 뷰 이름)
    SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.JOB_ID, D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.LOCATION_ID
      FROM DEPARTMENTS D INNER JOIN EMPLOYEES E
        ON D.DEPARTMENT_ID = E.DEPARTMENT_ID

⇢ 뷰 조회

-- 뷰 조회하기
SELECT *
  FROM V_EMP;

⇢ 뷰 삭제

-- 뷰 삭제
DROP VIEW V_EMP;

⇢ 뷰를 이용한 활용 예시

-- 1. 사원번호, 사원명, 부서번호, 부서명, 지역번호, 도로명 주소를 조회하시오.
SELECT V.EMPLOYEE_ID, V.FIRST_NAME, V.LAST_NAME, V.DEPARTMENT_ID, V.DEPARTMENT_NAME, L.LOCATION_ID, L.STREET_ADDRESS
  FROM LOCATIONS L INNER JOIN V_EMP V  -- 사실은 3개 테이블 조인 
    ON L.LOCATION_ID = V.LOCATION_ID;
profile
Gabriela
개발이 세상에서 제일 재밌어요
이전 포스트

[SQL] 조인 / 서브쿼리 / WITH문

다음 포스트

[PL/SQL] 기초 데이터 준비 / 변수선언 / 기본 구문

0개의 댓글