Database summary

📂 DB

1. SQL 기초

• SQL: Structured Query Language(구조적 질의 언어).
• SQL Optimizer: 프로시저를 생성하는 DBMS 내부 엔진. SQL 결과집합을 만드는 과정의 주체.
• SQL 최적화 과정
  1. SQL 파싱: 파싱 트리 생성 → 문법(Syntax) 체크 → 의미(Semantic, 권한 등) 체크
  2. SQL 최적화: 통계정보를 바탕으로 실행경로를 생성하고 최적 경로 선택
  3. 로우 소스 생성: 선택된 실행경로를 실행 가능한 코드/프로시저로 포맷팅 (Row-Source Generator)
• Library Cache: 파싱·최적화·로우소스 생성으로 만든 내부 프로시저를 재사용하도록 캐싱.

2. 데이터 저장 구조 및 I/O

• SQL 병목 원인: 디스크 I/O가 일어나는 동안 프로세스는 대기(waiting) 상태로 잠든다.
• 저장 구조 (오라클 기준)
  • Table Space: Segment를 담는 컨테이너. 여러 데이터 파일(물리적 OS 파일)로 구성.
  • Segment: 저장 공간이 필요한 오브젝트(테이블, 인덱스 등). 여러 Extent로 구성. (파티션이 아니면 테이블/인덱스 1개 = Segment 1개)
  • Extent: 공간을 확장하는 단위. 공간이 부족하면 Extent 단위로 확장.

3. 인덱스 구조 및 탐색

• 데이터 탐색 방법
  • 테이블 전체 스캔: 대상 데이터가 많을 때 유리
  • 인덱스 스캔: 대상 데이터가 적을 때 유리
• 인덱스 튜닝이 중요한 이유: OLTP 시스템은 주로 소량 데이터를 검색하기 때문.
• 인덱스 탐색 2단계
  • 수직적 탐색: 인덱스 스캔 시작 지점을 찾는 과정 (루트 → 브랜치 → 리프)
  • 수평적 탐색: 리프 블록에서 실제 데이터를 찾는 과정

4. 인덱스 튜닝

• Index ROWID에 대한 오해: ROWID는 포인터가 아니다. DBA(데이터 파일 번호 + 블록 주소) + 로우 번호로 구성 → 포인터만큼 빠르지 않고 오히려 느림.
  • (메인 메모리 DB는 인덱스에 실제 포인터가 들어감 — 기동 시 데이터를 전부 메모리에 로드)
• I/O 메커니즘: 데이터가 캐싱돼 있어도 DBA 해싱 + 래치 획득 과정 필요. 동시 액세스가 심하면 캐시 버퍼 체인 래치·버퍼 Lock 경합이 추가 발생.
• 튜닝 방향: ① 인덱스 스캔 효율화, ② 테이블 액세스(랜덤 I/O) 최소화.

5. 인덱스 종류 (스캔 방식)

• Index Range Scan: B*Tree의 가장 기본 형태. 루트→리프 수직 탐색 후 필요한 범위만 스캔. 선두 컬럼을 가공하지 않은 상태로 조건절에 써야 함. 성능은 테이블 액세스 횟수를 얼마나 줄이냐로 결정.
• Index Full Scan: 수직 탐색 없이 리프 블록을 처음부터 끝까지 수평 스캔. 인덱스만으로 대부분 필터링되고 일부만 테이블 액세스할 때 효과적.
• Index Unique Scan: 수직 탐색만으로 데이터를 찾음. Unique 인덱스를 = 조건으로 탐색할 때.
• Index Skip Scan: 루트/브랜치 블록의 컬럼 값 정보를 이용해 조건절에 없는 선두 컬럼을 건너뛰며 탐색.

6. 테이블 액세스 최소화

• Index ROWID: 논리적 주소에 가까움. ROWID로 데이터를 찾는 과정 → ① 해싱으로 버퍼 헤더·버퍼 블록 탐색, ② 없으면 디스크에서 읽어 버퍼 캐시 적재. 캐싱돼 있어도 매번 DBA 해싱·래치 획득 필요.
• 클러스터링 팩터(CF): 특정 컬럼 기준으로 같은 값의 데이터가 모여 있는 정도. CF가 좋을수록 ROWID 탐색 속도가 빠름.
• 버퍼 Pinning: 인덱스 ROWID로 테이블 액세스 시, 어렵게 찾아간 테이블 블록 포인터를 바로 해제하지 않고 유지 → 다음 인덱스 레코드가 같은 블록을 가리키면 래치·해시 체인 스캔을 생략.

7. 부분범위 처리 활용

• 부분범위 처리: 결과집합을 한 번에 다 보내지 않고, 사용자의 Fetch Call마다 일정량씩 나눠 전송.
• 정렬 조건이 있을 때: 모든 데이터를 읽어 정렬해야 하면 → 전체 범위 처리. 단, 인덱스로 정렬을 생략할 수 있으면 부분범위 처리 가능.
• OLTP에서의 효과: 대량 조회라도 보통 전체를 한 번에 보여줄 필요가 없음. 인덱스 + 부분범위 처리로 정렬을 생략하고 앞부분을 빠르게 출력 가능.
• 핵심 전제: 앞쪽 일부만 출력하고 멈출 수 있어야 의미가 있음.

8. 인덱스 스캔 효율화

• 액세스 조건: 인덱스 스캔 범위를 결정하는 조건. 수직 탐색의 시작점과 리프 스캔의 멈출 지점에 영향.
• 필터 조건: 인덱스/Full Scan 여부와 무관하게 테이블 액세스 단계에서 처리되는 조건절. 다음 단계로 보낼지·결과집합에 포함할지를 결정.
• 인덱스 액세스 비용 공식

  비용 = 인덱스 수직적 탐색 비용 + 인덱스 수평적 탐색 비용 + 테이블 랜덤 액세스 비용

  = (루트·브랜치에서 읽는 블록 수) + (리프 블록 스캔 중 읽는 블록 수) + (테이블 액세스 중 읽는 블록 수)

• 비교 연산자 종류와 컬럼 순서에 따라 액세스 조건/필터 조건이 달라짐.