소스: sql-src/batch_04_stats_hints/01_distribution.sql, 02_stale_stats.sql, 03_hint_syntax.sql.
세 파일 모두 orders 를 5,000행 만듭니다. status 는 DONE 95%, WAIT 2%, REFUND 2%, CANCEL 1% 로 치우쳐 있고, 01 파일의 cust 는 20 행마다 NULL 입니다. 아래 결과는 H2 로 실행한 데이터 분포이며 실제 DB 의 통계 내용이 아닙니다.
옵티마이저가 저장하는 고유 값 수, NULL 수, 최소, 최대를 직접 쿼리로 구합니다. 실제 DB 의 통계 뷰와 같은 종류의 정보입니다.
SELECT
'emp_id' AS col
, COUNT(DISTINCT emp_id) AS ndv
, COUNT(*) - COUNT(emp_id) AS nulls
, CAST(MIN(emp_id) AS VARCHAR(20)) AS min_v
, CAST(MAX(emp_id) AS VARCHAR(20)) AS max_v
FROM orders
UNION ALL
SELECT
'cust'
, COUNT(DISTINCT cust)
, COUNT(*) - COUNT(cust)
, MIN(cust)
, MAX(cust)
FROM orders
UNION ALL
SELECT
'amt'
, COUNT(DISTINCT amt)
, COUNT(*) - COUNT(amt)
, CAST(MIN(amt) AS VARCHAR(20))
, CAST(MAX(amt) AS VARCHAR(20))
FROM orders
UNION ALL
SELECT
'ord_date'
, COUNT(DISTINCT ord_date)
, COUNT(*) - COUNT(ord_date)
, CAST(MIN(ord_date) AS VARCHAR(20))
, CAST(MAX(ord_date) AS VARCHAR(20))
FROM orders
UNION ALL
SELECT
'status'
, COUNT(DISTINCT status)
, COUNT(*) - COUNT(status)
, MIN(status)
, MAX(status)
FROM orders;COL | NDV | NULLS | MIN_V | MAX_V
---------+-----+-------+------------+-----------
emp_id | 50 | 0 | 1 | 50
cust | 190 | 250 | C1 | C99
amt | 97 | 0 | 100 | 9700
ord_date | 365 | 0 | 2024-01-01 | 2024-12-30
status | 4 | 0 | CANCEL | WAIT
(5행)cust 는 NULL 이 250 개이고 고유 값이 200 이 아니라 190 입니다. 20 의 배수 행이 NULL 이라 값 열 개가 통째로 빠졌기 때문입니다. 최소와 최대는 문자열 순서로 비교되어 cust 의 최대가 C99 입니다.
치우침은 값별로 세어야 보입니다. 히스토그램이 저장하는 정보가 바로 이 결과입니다.
SELECT
status
, COUNT(*) AS cnt
, ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM orders), 1) AS pct
FROM orders
GROUP BY status
ORDER BY cnt DESC;STATUS | CNT | PCT
-------+------+-----
DONE | 4750 | 95.0
REFUND | 100 | 2.0
WAIT | 100 | 2.0
CANCEL | 50 | 1.0
(4행)DONE 이 95% 이고 CANCEL 은 1% 입니다. 값 종류는 네 개지만 어느 값이냐에 따라 조건에 걸리는 행 수가 95 배 차이 납니다.
균등 가정으로 계산한 예상 행 수와 CANCEL 의 실제 행 수를 나란히 봅니다.
SELECT
COUNT(*) AS total_rows
, COUNT(DISTINCT status) AS ndv
, COUNT(*) / COUNT(DISTINCT status) AS uniform_estimate
, SUM(CASE WHEN status = 'CANCEL' THEN 1 ELSE 0 END) AS actual_cancel
FROM orders;TOTAL_ROWS | NDV | UNIFORM_ESTIMATE | ACTUAL_CANCEL
-----------+-----+------------------+--------------
5000 | 4 | 1250 | 50
(1행)5,000 나누기 4 는 1,250 이고 실제는 50 입니다. 히스토그램이 없으면 옵티마이저는 1,250 이라고 믿고 계획을 짭니다. 실제 DB 의 계산은 이보다 세부적이지만 균등 가정이 치우친 값에서 틀린다는 원리는 같습니다.
통계를 모은 시점의 분포를 확인하고, 취소 20,000 건을 한 번에 넣은 뒤 분포를 다시 봅니다. H2 에서는 ANALYZE TABLE orders 가 실행됩니다. H2 형식이며 실제 DB 의 통계 수집 동작의 근거가 아닙니다.
ANALYZE TABLE orders;
SELECT
COUNT(*) AS total_rows
, SUM(CASE WHEN status = 'CANCEL' THEN 1 ELSE 0 END) AS cancel_rows
, ROUND(100.0 * SUM(CASE WHEN status = 'CANCEL' THEN 1 ELSE 0 END) / COUNT(*), 1) AS cancel_pct
FROM orders;TOTAL_ROWS | CANCEL_ROWS | CANCEL_PCT
-----------+-------------+-----------
5000 | 50 | 1.0
(1행)이 시점의 통계는 5,000 행, CANCEL 1% 를 기록합니다. 이어서 취소 상태 20,000 행을 한 번에 넣습니다.
INSERT INTO orders
WITH RECURSIVE seq(n) AS (
SELECT 5001
UNION ALL
SELECT n + 1 FROM seq WHERE n < 25000
)
SELECT
n
, MOD(n, 50) + 1
, 'C' || MOD(n, 200)
, (MOD(n, 97) + 1) * 100
, DATEADD('DAY', MOD(n, 365), DATE '2024-01-01')
, 'CANCEL'
FROM seq;
SELECT
COUNT(*) AS total_rows
, SUM(CASE WHEN status = 'CANCEL' THEN 1 ELSE 0 END) AS cancel_rows
, ROUND(100.0 * SUM(CASE WHEN status = 'CANCEL' THEN 1 ELSE 0 END) / COUNT(*), 1) AS cancel_pct
FROM orders;TOTAL_ROWS | CANCEL_ROWS | CANCEL_PCT
-----------+-------------+-----------
25000 | 20050 | 80.2
(1행)CANCEL 이 1% 에서 80.2% 로 바뀌었습니다. 통계를 다시 모으지 않았다면 옵티마이저는 아직 5,000 행 중 50 행이라고 믿고 있습니다.
아래 쿼리를 대량 INSERT 직후에 돌린다고 가정합니다.
-- Oracle · Tibero
SELECT /*+ GATHER_PLAN_STATISTICS */
COUNT(amt)
FROM orders
WHERE status = 'CANCEL';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));도식(H2 미지원, 실행 결과 아님)
| Id | Operation | Name | E-Rows | A-Rows |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS | 50 | 20050 |
|* 3 | INDEX RANGE SCAN | IDX_ORDERS_STATUS | 50 | 20050 |E-Rows 는 옛 통계로 계산한 50 이고 A-Rows 는 실제 20,050 입니다. 20,000 번 넘게 인덱스에서 표로 오가는 계획이라 전체 읽기보다 느려질 수 있습니다. 통계를 다시 모으면 E-Rows 가 실제에 가까워지고 계획이 전체 읽기로 바뀌는 것이 정상적인 결과입니다.