SQL | CTE문, view, temporary table의 차이

👩 복잡한 쿼리를 다루다보면 서브쿼리와 CTE(common table expression)을 사용하게 된다. 그리고 대용량 데이터를 다루게 될수록 여러 개의 CTE를 만들어내게 되는데, 한 가지 어려움을 마주쳤다.

바로 하나의 실행 단위 안에서 이루어져야만 작동한다는 것.
즉, CTE는 쿼리 실행 시 일시적으로 생성되는 테이블이기 때문에 CTE와 (CTE를 포함한) 실행하고자 하는 쿼리를 동시에 드래그해서 실행하지 않는 이상 Error가 계속 떴다.

만약 쿼리가 길어지게 되면 일일이 찾아서 실행하기 어렵다.

그래서 이 때 View나 Temporary table을 사용해서 자주 사용하는 쿼리를 반복해서 불러올 때 편한 방법을 찾게 되었는데, 사실 이 세 가지 방법의 차이가 무엇인지 한 번에 알기 어려웠다.

그래서 찾아본 세 가지의 차이!

• 뷰 (view)
  - 반복적인 쿼리를 재사용할 수 있고, 데이터 베이스에 저장해 필요할 때마다 호출한다.
  - 쿼리의 정의만 저장되고, 뷰를 조회할 때마다 원본 테이블을 기반으로 데이터를 동적으로 실행하기 때문에 뷰를 조회할 때마다 쿼리분을 그대로 조회하는 것과 동일하다.
  - 복잡한 쿼리를 쉽게 재사용할 수 있다는 것과, 영구성, 유지보수가 용이하다는 점이 장점이다. 또한 테이블 접근 권한을 직접주지 않고 뷰를 통한 제한된 접근만 가능하도록 해 보안성이 장점이다.
  - 단점으로는 성능 개선을 위한 방법으로는 적절치 않다.
  
  

• 임시테이블 (temporary table)
  - 일시적으로 데이터를 저장하고 여러 쿼리를 재사용하 수 있다. 세이나 트랜잭션이 끝나면 자동으로 삭제된다.
  -일반 테이블처럼 데이터를 삽입, 업데이트, 삭제할 수 있어 유연하다.
  - select에 대해서 크게 성능이 개선되지 않는데, 그 이유는 테이블 생성, 삭제, insert, join 등 불필요한 단계가 존재하기 때문이다.
  - 중첩된 서브쿼리(CTE)는 여러 번 사용할 경우 추정이 틀릴 가능성이 높아진다. 이 때 서브쿼리 결과를 임시테이블에 저장하면 쿼리 엔진이 임시테이블의 데이터를 사용할 수 있으므로 잘못된 추정을 줄일 수 있다.
  
  

• 서브쿼리 (subquery)
  - 서브쿼리를 사용하여 필요한 데이터를 한 번에 가져오는 방법

언제 뭘 사용할까?

• CTE를 사용하면 더 좋을 때

  • CTE 결과가 나머지 쿼리의 동작에 실제 영향을 미치지 않을 때

  • CTE 결과에서 어떤 데이터를 실제로 사용할지 불분명할 때

  • CTE에서 몇 개 행이 나올지 잘 추정할 수 있고, 그 행들의 내용이 무엇인지 알 수 있을 때

    👩 데이터 확인하거나 일시적으로 사용/시도해볼 때?

• 임시 테이블을 사용하는 게 더 좋을 때

  • 결과를 여러 번 참조할 때

  • 저장 프로시저(데이터베이스에 저장된 SQL 코드 블록) 간에 데이터를 전달해야할 때

  • 예측 할 수 없는 요소들을 격리해 나머지 부분에 미치는 영향을 크게 줄이기 위해 쿼리를 단계별로 분할해야할 때

    👩 사용할 일이 많거나 쿼리를 단계별로 분리할 때!

출처

• VIEW를 사용하는 게 더 좋을 때
  • 데이터 일관성을 유지할 때
  • 데이터 보안 및 접근 제안이 필요할 때(민감한 데이터일 때)
  • 읽기 전용 데이터를 제공할 때

The first thing that is important to keep in mind, that Subqueries, Views and CTEs are all conceptually the same for the query engine. (쿼리엔진에서 개념적으로 모두 동일하다.) SQL is declarative language where – unless there are some precedence constraints imposed by the operator used – everything is evaluated “all-at-once” (우선순위 제약이 없는 한 모든 것이 한 번에 평가되는 언어이다.)

It means that there is no guarantee that a subquery (or a CTE or a view) will be executed before the query that uses it.
(CTE나 VIEW 같은 하위 쿼리가 이 쿼리를 사용하는 쿼리보다 먼저 실행된다는 것을 보장할 수 없다)

출처

*sql세션이란 데이터베이스와의 상호 연결이 되어있는 기간을 말한다.

view 생성과 삭제

• 뷰 생성
  형식 : CREATE VIEW 테이블 명 AS 쿼리;

CREATE VIEW vt AS 
SELECT user_id, user_session, event_time, product_id
FROM 2019dec
WHERE event_type = 'view';

• 단일뷰 삭제
  형식 : DROP VIEW IF EXISTS 테이블 명;

DROP VIEW IF EXISTS vt;

• 여러 뷰 삭제
  단일 뷰 형식에서 쉼표로 구분해서 명령

DROP VIEW IF EXISTS vt, ct, pt, rt, v_to_c, c_to_r, c_to_p;

임시테이블 생성과 삭제

• 임시테이블 생성
  형식 : CREATE TEMPORARY TABLE 테이블 명 AS 쿼리;

CREATE TEMPORARY TABLE c_to_r AS
SELECT ct.*
    , rt.event_time AS remove_event_time
    , TIMESTAMPDIFF(SECOND, ct.event_time, rt.event_time) AS time_gap
FROM ct
LEFT JOIN rt ON ct.user_id = rt.user_id
        AND ct.user_session = rt.user_session
        AND ct.product_id = rt.product_id
        AND ct.event_time <= rt.event_time;

• 임시테이블 삭제
  형식 : DROP TEMPORARY TABLE IF EXISTS temp_table_name;