소스: sql-src/batch_01_explain_plan/01_h2_explain.sql, 02_access_paths.sql, 03_dialects.sql.
세 파일 모두 orders 를 3,000행 만듭니다. emp_id 는 1~50 이 고르게 나오고, ord_date 는 2024 년 1 년에 퍼집니다. 인덱스는 emp_id 와 ord_date 에 있고 PK 는 id 입니다. amt 와 cust 에는 인덱스가 없습니다. 아래 결과는 모두 H2 형식이며 실제 DB 계획이 아닙니다.
재귀 CTE 로 1~3,000 번호를 만들고 INSERT ... SELECT 로 한 번에 넣습니다.
CREATE TABLE orders (id INT PRIMARY KEY, emp_id INT, cust VARCHAR(20), amt INT, ord_date DATE, status VARCHAR(10));
INSERT INTO orders
WITH RECURSIVE seq(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM seq WHERE n < 3000
)
SELECT
n
, MOD(n, 50) + 1
, 'C' || MOD(n, 200)
, (MOD(n, 97) + 1) * 100
, DATEADD('DAY', MOD(n, 365), DATE '2024-01-01')
, CASE WHEN MOD(n, 10) = 0 THEN 'CANCEL' ELSE 'DONE' END
FROM seq;
CREATE INDEX idx_orders_emp ON orders (emp_id);
CREATE INDEX idx_orders_date ON orders (ord_date);
SELECT COUNT(*) AS cnt FROM orders;CNT
----
3000
(1행)H2 는 INSERT INTO ... WITH RECURSIVE 를 받아 줍니다. Oracle 과 MySQL 은 INSERT INTO ... WITH ... SELECT 순서이고 MSSQL 은 WITH ... INSERT INTO ... SELECT 순서입니다. RECURSIVE 키워드는 MySQL 8.0 만 필요하고 Oracle 은 컬럼 목록이 필요합니다. 문법을 확인하고 옮깁니다.
amt 에는 인덱스가 없고 emp_id 에는 있습니다. 같은 모양의 동등 조건인데 계획이 어떻게 달라지는지 봅니다.
EXPLAIN SELECT id FROM orders WHERE amt = 5000;PLAN
-------------------------------------------------------------------------------------------
SELECT
"ID"
FROM "PUBLIC"."ORDERS"
/* PUBLIC.ORDERS.tableScan */
WHERE "AMT" = 5000
(1행)주석이 ORDERS.tableScan 이므로 3,000행을 처음부터 끝까지 읽습니다. 실제 DB 라면 Oracle TABLE ACCESS FULL, MySQL type ALL, MSSQL Clustered Index Scan 에 해당합니다.
EXPLAIN SELECT id FROM orders WHERE emp_id = 7;PLAN
-----------------------------------------------------------------------------------------------------
SELECT
"ID"
FROM "PUBLIC"."ORDERS"
/* PUBLIC.IDX_ORDERS_EMP: EMP_ID = 7 */
WHERE "EMP_ID" = 7
(1행)IDX_ORDERS_EMP: EMP_ID = 7 이 있어 인덱스에서 emp_id 가 7 인 곳만 찾아 들어갑니다. 조건에 맞는 행이 전체의 50 분의 1 이라 이득이 큽니다. 실제 DB 라면 INDEX RANGE SCAN 에 해당합니다.
EXPLAIN SELECT id FROM orders WHERE ord_date >= DATE '2024-03-01' AND ord_date < DATE '2024-03-08';PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
"ID"
FROM "PUBLIC"."ORDERS"
/* PUBLIC.IDX_ORDERS_DATE: ORD_DATE >= DATE '2024-03-01'
AND ORD_DATE < DATE '2024-03-08'
*/
WHERE ("ORD_DATE" >= DATE '2024-03-01')
AND ("ORD_DATE" < DATE '2024-03-08')
(1행)주석에 두 조건이 함께 있으므로 인덱스의 한 구간만 읽습니다. 범위가 전체의 상당 부분이면 실제 DB 는 인덱스 대신 전체 읽기를 고를 수도 있습니다. 그 판단은 통계에 달렸습니다.
emp_id 와 ord_date 에는 인덱스가 있지만 컬럼에 식이나 함수를 씌우면 인덱스로 찾아 들어갈 수 없습니다. 아래 첫 조건은 예제 2 와 결과가 같습니다.
EXPLAIN SELECT id FROM orders WHERE emp_id + 0 = 7;PLAN
-----------------------------------------------------------------------------------------------
SELECT
"ID"
FROM "PUBLIC"."ORDERS"
/* PUBLIC.IDX_ORDERS_EMP */
WHERE ("EMP_ID" + 0) = 7
(1행)EXPLAIN SELECT id FROM orders WHERE FORMATDATETIME(ord_date, 'yyyy-MM') = '2024-03';PLAN
-------------------------------------------------------------------------------------------------------------------------------
SELECT
"ID"
FROM "PUBLIC"."ORDERS"
/* PUBLIC.IDX_ORDERS_DATE */
WHERE FORMATDATETIME("ORD_DATE", 'yyyy-MM') = '2024-03'
(1행)두 계획 모두 인덱스 이름만 있고 조건이 붙지 않았습니다. 예제 2 의 EMP_ID = 7 이 사라졌으므로 인덱스로 찾은 것이 아니라 인덱스를 처음부터 훑으며 식을 계산한 것입니다. 이 출력만으로는 H2 가 왜 인덱스를 훑기로 했는지까지는 알 수 없습니다. 확실한 것은 조건으로 좁혀 들어가지 못했다는 점입니다.
핵심인덱스 컬럼을 함수나 식으로 감싸면 그 컬럼의 인덱스로 값을 찾아 들어가지 못합니다. 조건은 컬럼 쪽을 그대로 두고 값 쪽을 바꿔 씁니다. 예를 들어
ord_date >= DATE '2024-03-01' AND ord_date < DATE '2024-04-01'입니다.
같은 orders 를 세 가지 방식으로 조회합니다. 접근 방식이 셋으로 갈리는 것을 확인합니다.
EXPLAIN SELECT id, amt FROM orders WHERE id = 100;PLAN
-----------------------------------------------------------------------------------------------------------
SELECT
"ID",
"AMT"
FROM "PUBLIC"."ORDERS"
/* PUBLIC.PRIMARY_KEY_8: ID = 100 */
WHERE "ID" = 100
(1행)EXPLAIN SELECT id, amt FROM orders WHERE id BETWEEN 100 AND 110;PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
"ID",
"AMT"
FROM "PUBLIC"."ORDERS"
/* PUBLIC.PRIMARY_KEY_8: ID >= 100
AND ID <= 110
*/
WHERE "ID" BETWEEN 100 AND 110
(1행)EXPLAIN SELECT id FROM orders WHERE cust = 'C7';PLAN
--------------------------------------------------------------------------------------------
SELECT
"ID"
FROM "PUBLIC"."ORDERS"
/* PUBLIC.ORDERS.tableScan */
WHERE "CUST" = 'C7'
(1행)PK 단건은 ID = 100 한 값이라 한 건을 찾습니다. 실제 DB 의 INDEX UNIQUE SCAN, MySQL const 입니다. PK 범위는 ID >= 100 AND ID <= 110 이라 인덱스 구간만 읽고 실제로 11행이 나옵니다. cust 는 인덱스가 없어 전체 읽기입니다.