WITH 로 시작하는 절을 CTE(Common Table Expression)라 한다. 뒤따르는 본 쿼리에서 테이블처럼 참조할 이름 하나를 미리 정의하는 것이다.
WITH target_filtered AS (
SELECT col1, col2, col3 FROM target_a@target
)
SELECT t.aaa, t.bbb, f.col2
FROM main_table t
JOIN target_filtered f ON t.col1 = f.col1;
CTE 를 정의만 하고 본 쿼리에서 참조하지 않으면 결과에 아무 영향이 없다. 옵티마이저가 통째로 제거하는 경우가 대부분이다.
-- target_filtered 를 쓰지 않으므로 아래 SELECT 결과만 나온다
WITH target_filtered AS (
SELECT col1 FROM target_a@target
)
SELECT aaa, bbb FROM main_table;
물려받은 쿼리를 읽을 때는 CTE 이름이 본문에서 실제로 쓰이는지 먼저 확인한다. 쓰이지 않는 CTE 는 과거에 조건이 있었다가 지워진 흔적인 경우가 많다.
WITH a AS (
SELECT ...
), b AS (
SELECT ... FROM a WHERE ...
)
SELECT * FROM b;
뒤에 오는 CTE 는 앞에 정의된 CTE 를 참조할 수 있다. 반대는 안 된다.
CTE 가 실제 임시 테이블로 구체화되는지, 본 쿼리에 인라인으로 펼쳐지는지는 엔진과 버전에 따라 다르다.
| 엔진 | 기본 동작 |
|---|---|
| Oracle | 참조 횟수와 비용에 따라 옵티마이저가 정한다. /*+ MATERIALIZE */ · /*+ INLINE */ 힌트로 강제할 수 있다 |
| PostgreSQL | 12 부터 한 번만 참조되고 부작용이 없으면 인라인으로 펼친다. 강제하려면 WITH x AS MATERIALIZED (...) |
| MySQL 8.0 | 옵티마이저가 정한다 |
여러 번 참조하는 무거운 CTE 를 인라인으로 펼치면 같은 서브쿼리를 여러 번 실행한다. 느려지면 이 지점을 의심한다.
계층 구조를 펼칠 때 쓴다.
WITH RECURSIVE org AS (
SELECT id, parent_id, name, 1 AS depth
FROM department WHERE parent_id IS NULL
UNION ALL
SELECT d.id, d.parent_id, d.name, o.depth + 1
FROM department d JOIN org o ON d.parent_id = o.id
)
SELECT * FROM org ORDER BY depth;
Oracle 은 RECURSIVE 키워드를 쓰지 않으며, 같은 일을 CONNECT BY 로도 한다. 종료 조건이 없으면 무한 반복하므로 깊이 제한을 함께 둔다.
target_a@target 은 Oracle 의 데이터베이스 링크 표기다. @ 뒤가 링크 이름이고, 그 링크가 가리키는 원격 데이터베이스의 테이블을 읽는다는 뜻이다.
SELECT db_link, host, username FROM all_db_links;
SELECT * FROM dual@target; -- 링크가 살아 있는지 확인
원격 조인은 성능이 걸린다. 조건이 원격으로 내려가지(pushdown) 않으면 전체를 끌어온 뒤 로컬에서 거른다. 실행 계획에서 REMOTE 오퍼레이션이 어떤 SQL 을 보내는지 확인한다.
EXPLAIN PLAN FOR SELECT ... ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));
원격 데이터를 여러 번 참조한다면 /*+ MATERIALIZE */ 로 한 번만 가져오게 하거나, 먼저 임시 테이블에 담는 편이 낫다. DB Link 를 거친 트랜잭션은 분산 트랜잭션이 되어 커밋 처리도 달라진다는 점을 염두에 둔다.
다른 엔진에는 같은 표기가 없다. PostgreSQL 은 postgres_fdw, MySQL 은 FEDERATED 엔진, Trino 는 카탈로그 이름으로 같은 일을 한다.