참조 무결성 룩업이 사라지는 것, 1:1/1:N 매핑 보장이 사라지는 것, transitive predicate 생성이 안 된다는 것

참조 무결성 룩업이 사라지는 것은 어떤 의미이고, 1:1/1:N 매핑 보장이 사라지는 것은 어떤 의미이고, transitive predicate 생성이 안 된다는 것은 어떤 의미인가?

 

 

세 가지 문장이 모두 “옵티마이저가 확신할 수 있는 정보가 하나씩 사라진다”는 뜻입니다.
각각을 실행계획 관점에서 풀어 쓰면 다음과 같습니다.

---

1. “참조 무결성 룩업이 사라진다”

---

의미

• INSERT/UPDATE 시 “부모에 이 값이 존재하는가?” 를 검사하는 SELECT(=PK 룩업)가
  SQL 실행 계획에 아예 나타나지 않는다는 뜻입니다.
• 룩업이 없으므로
  – 부모 PK 인덱스 리프 블록을 읽을 일이 없고
  – 부모 테이블의 칼럼 통계(치수, 클러스터링 팩터 등)를 실행 단계에서도 읽지 않게 됩니다.
• 결과적으로 옵티마이저가 “부모 인덱스는 얼마나 선택적인가”를 **실제로 측정해 볼 기회조차 없어집니다.

예시
자식 테이블에 FK가 있을 때

INSERT INTO order_item(order_id, …) VALUES (123, …);

내부적으로

SELECT 1 FROM orders WHERE order_id = 123; -- 룩업

이 실행되므로 orders PK 인덱스 통계가 메모리에 적재됩니다.
FK를 삭제하면 이 SELECT가 사라지므로 orders 인덱스를 읽을 일이 아예 없어집니다.

---

1. “1:1 / 1:N 매핑 보장이 사라진다”

---

의미

• FK 제약이 있으면 옵티마이저는 논리적으로
  “자식 쪽 값 하나는 부모 쪽에서 0 또는 1건만 만난다”(N:1)는 것을 100 % 확신합니다.
• 이 확신이 있어야
  – 조인 순서를 부먼저 읽고 자식을 루프로 찾는 것이 안전하다고 판단하고
  – 부모 테이블의 선택도(selectivity)를 ‘1건 매칭’ 가정 아래 낙관적으로 계산합니다.
• FK가 없으면 “혹시 부모에 2건 이상 있을 수도 있다”는 가능성이 열려 있으므로
  – 동일한 계산을 보수적으로 하거나
  – 아예 부모를 먼저 읽는 쪽안을 후보에서 제거해 버립니다.

예시

sql

SELECT *
FROM   orders o
JOIN   order_item i ON i.order_id = o.order_id
WHERE  o.order_date = DATE '2025-09-26';

• FK가 있으면
  “i → o 방향으로 조인하면 최대 1건”이라는 사실을 알기 때문에
  order_item을 먼저 읽고 orders를 인덱스 루프로 찾는 계획이 저비용으로 선택됩니다.
• FK가 없으면
  “i.order_id 하나당 o가 여러 건 나올 수도 있다”고 가정해야 하므로
  위 계획의 비용이 높게 책정되거나 아예 배제됩니다.

---

1. “transitive predicate 생성이 안 된다”

---

의미

• FK가 있으면 옵티마이저가 조인 열을 통해 필터를 전파할 수 있습니다.
  즉, 부모 쪽 WHERE 조건을 자식 쪽에도 동일한 조건으로 복사해 주는 최적화가 가능합니다.
• 이 복사된 조건을 transitive predicate라고 합니다.
• FK가 없으면 “조인 열이 실제로 동일 값을 갖는다”는 보장이 없기 때문에
  이런 전파를 논리적으로 할 수 없어 predicate 자체가 생성되지 않습니다.

예시

sql

SELECT *
FROM   orders o
JOIN   order_item i ON i.order_id = o.order_id
WHERE  o.order_id = 123;

• FK가 있으면 옵티마이저는 내부적으로
  i.order_id = 123 -- transitive predicate
  를 추가 생성합니다.
  결과적으로 order_item에 대한 인덱스 range scan이 가능해집니다.
• FK가 없으면
  “i.order_id가 o.order_id와 같다고 해도 123일지 확신할 수 없다” 고 판단하므로
  위 predicate를 만들지 않습니다.
  따라서 order_item은 풀 스캔이나 다른 필터만으로 접근해야 하고,
  부모 테이블 쪽 인덱스 선택도 정보를 자식 쪽에서 재활용할 수 없게 됩니다.

---

한 문장으로 정리

• 룩업이 사라지면 → 부모 인덱스를 읽을 일이 아예 없어진다.
• 매핑 보장이 사라지면 → 조인 순서·선택도 계산을 보수적으로 한다.
• transitive predicate 생성이 안 되면 → 부모 쪽 필터를 자식 쪽에 붙여 줄 수 없다.

즉, 옵티마이저가 “이 정보를 믿고 계획을 세울 근거” 를 하나씩 잃어가는 것입니다.

 

 

옵티마이저에서 index와 hint의 목적은 무엇이고, 어떤 점이 다르고, 수행 방법 및 순서는?

 

옵티마이저에게
“어떤 인덱스를 써라”는 지시를 내릴 수 있는 방법이 두 가지 있습니다.
하나는 통계를 기반으로 자동으로 고르게 하는 것(INDEX) 이고,
다른 하나는 개발자가 강제로 지정하는 것(HINT) 입니다.
목적·차이·수행(적용) 순서를 정리하면 다음과 같습니다.

---

1. 목적

---

• INDEX(자동 선택)
  – 통계 정보(칼럼 분포, 리프 블록 수, 클러스터링 팩터 …)를 바탕으로
  최소 비용을 내는 실행계획을 옵티마이저가 스스로 찾는 것.
• HINT(힌트)
  – 통계가 부정확하거나 특수한 데이터 패턴 때문에 자동 계획이 나쁠 때
  개발자가 원하는 계획을 강제로 삽입해 주는 것.
  – 힌트는 지시일 뿐, 반드시 따른다는 보장은 없지만(무시될 수 있음)
  대부분의 경우 지정한 대로 계획이 고정됨.

---

1. 차이점

---

항목 INDEX(자동) HINT

---

결정 주체 옵티마이저 개발자/DBA 정보 근거 데이터 딕셔너리 통계 사람의 판단, 경험치 변동성 통계 바뀌면 계획 바뀜 쿼리를 고쳐야 바뀜 유연성 높음(데이터 변화에 자동 반영) 낮음(계획 고정) 위험도 통계 오류 시 나쁜 계획 가능 힌트 잘못 시 극단적으로 나쁜 계획 유지보수 필요 없음 SQL 전체를 재배포해야 할 수도 있음 버전 업그레이드 계획이 자동으로 개선될 수 있음 힌트가 새 버전에서 무시/오류 날 수 있음

---

1. 수행(적용) 방법

---

1. INDEX(자동)
   ① SQL 파싱 → ② 통계 읽기 → ③ 옵티마이저가 가능한 인덱스 후보군 생성
   → ④ 각 후보별 I/O·CPU 비용 계산 → ⑤ 최소 비용 인덱스 선택 → ⑥ 실행계획 고정
2. HINT
   ① SQL 파싱 단계에서 힌트 문법 검사 → ② 힌트가 지정한 엑세스 경로를
   후보군 맨 위로 강제 삽입 → ③ 비용 계산 단계에서 해당 경로만
   “매우 낮은 비용”으로 책정 → ④ 결과적으로 힌트된 인덱스 선택
   (※ 힌트가 물리적으로 불가능하면 무시되고 자동 모드로 돌아감)

---

1. 처리 순서(실행계획 수립 흐름)

---

1. SQL 문 파싱
2. 힌트 존재 여부 확인
   • 힌트 있음 → 지정된 엑세스 경로를 우선 후보로 삽입
   • 힌트 없음 → 모든 가능한 인덱스 후보 수집
3. 통계 정보 로드(테이블, 인덱스, 칼럼 히스토그램)
4. 조인 순서·조인 방식 후보 생성
5. 각 후보별 예상 비용 산정
   • 힌트 경로는 인위적으로 낮은 비용 부여
6. 최소 비용 계획 선택
7. 실행계획 생성 및 캐시(공유 풀)

---

1. 실무 팁

---

• 힌트는 최후의 수단으로 사용하고, 가능하면 통계 갱신이나 인덱스 재설계로 해결
• 힌트 후 반드시 실행계획 확인(EXPLAIN PLAN, DBMS_XPLAN)
• 19c 이상에서는 SQL Plan Management(SPM) 나 어댑티브 플랜으로
  힌트 없이도 계획을 안정시킬 수 있음
• 힌트 문법이 바뀌는 경우(업그레이드) 호환성 파라미터 확인 필수

---

한 줄 요약
INDEX는 “통계로 계산한 최선”, HINT는 “개발자가 지정한 최선”이며,
옵티마이저는 파싱 → 힌트 반영 → 통계 활용 → 비용 계산 → 최종 계획 확정 순으로 두 경로를 구분해 처리합니다.