MySQL 전체 구조 및 개요

MySQL은 오픈소스 관계형 데이터베이스 관리 시스템(RDBMS)이다.
여러 프로그래밍 언어에 드라이버와 API를 제공하며, 내부적으로 MySQL 엔진과 스토리지 엔진으로 구성되어 있다.

1. MySQL 엔진과 스토리지 엔진

MySQL 엔진

MySQL 엔진은 DBMS의 두뇌 역할을 한다.
커넥션 핸들러, SQL 파서(Parser), 전처리기, 옵티마이저 등이 포함되어 있으며,
SQL을 해석하고 최적화하여 실행 계획을 만든다.

스토리지 엔진

스토리지 엔진은 데이터를 실제로 저장하고 읽어오는 역할을 한다.
MySQL 엔진이 쿼리를 처리하고 스토리지 엔진에게 데이터를 읽고 쓰도록 요청한다.

하나의 MySQL 서버에는 하나의 MySQL 엔진이 존재하지만, 스토리지 엔진은 여러 개 존재할 수 있다.
테이블 생성 시 ENGINE=InnoDB와 같이 지정하면 해당 테이블은 지정된 스토리지 엔진을 사용한다.

대표적인 스토리지 엔진은 InnoDB와 MyISAM이다.

• InnoDB: 버퍼 풀(Buffer Pool) 사용
• MyISAM: 키 캐시(Key Cache) 사용

MySQL 5.7 이후 버전에서는 MyISAM을 사용할 이유가 거의 없으며,
InnoDB가 표준 스토리지 엔진으로 사용된다.

핸들러 API

MySQL 엔진의 쿼리 실행기가 스토리지 엔진에 데이터를 요청할 때 사용하는 인터페이스를 말한다.
즉, MySQL 엔진이 데이터를 읽거나 쓸 때 스토리지 엔진과 통신하기 위해 사용하는 API이다.

SHOW GLOBAL STATUS LIKE 'Handler%'

2. MySQL 스레딩 구조

MySQL은 포그라운드 스레드와 백그라운드 스레드로 나뉜다.

포그라운드 스레드 (클라이언트 스레드)

MySQL 서버에 접속한 클라이언트 수만큼 존재한다.
각 스레드는 클라이언트의 SQL 요청을 처리한다.

스레드 캐시(Thread Cache)가 존재하며,
시스템 변수로 캐시 크기를 조정할 수 있다.

InnoDB의 경우 포그라운드 스레드는 버퍼나 캐시까지만 작업하며,
디스크에 데이터를 실제로 쓰는 작업은 백그라운드 스레드가 처리한다.

백그라운드 스레드

InnoDB에서는 다음과 같은 작업이 백그라운드로 실행된다.

• 인서트 버퍼 병합
• 로그를 디스크에 기록
• 버퍼 풀의 데이터를 디스크에 기록
• 데이터를 버퍼로 읽어오기
• 잠금(lock) 및 데드락 감시

MySQL 5.5 이후부터는 읽기 스레드, 쓰기 스레드의 개수를 조정할 수 있다.
일반적으로 읽기 스레드는 많을 필요가 없고,
쓰기 스레드는 2~4개 정도로 설정하는 것이 적당하다.

3. 메모리 구조

MySQL의 메모리는 글로벌 메모리 영역과 로컬(세션) 메모리 영역으로 나뉜다.

글로벌 메모리 영역

MySQL 서버 시작 시 운영체제로부터 할당되며, 모든 스레드가 공유한다.
클라이언트 수와 관계없이 고정된 크기로 존재한다.

대표적인 구성 요소는 다음과 같다.

• 테이블 캐시
• InnoDB 버퍼 풀
• InnoDB 어댑티브 해시 인덱스
• InnoDB 리두 로그 버퍼

로컬(세션) 메모리 영역

각 클라이언트 스레드가 독립적으로 사용하는 메모리 영역이다.
쿼리 실행 시마다 임시로 사용되며, 다음과 같은 용도로 활용된다.

• 정렬 버퍼
• 조인 버퍼
• 바이너리 로그 캐시
• 네트워크 버퍼

플러그인 스토리지 엔진 모델

MySQL 플러그인과 컴포넌트 구조

MySQL은 플러그인(Plugin) 모델을 기반으로 동작한다.
인증 방식, 검색어 파서(parser) 등 여러 기능이 플러그인 형태로 개발되어 제공된다.

1. MySQL 엔진과 스토리지 엔진의 역할

MySQL에서 쿼리가 실행되는 과정은 대부분 MySQL 엔진에서 처리된다.
실제 데이터의 읽기와 쓰기 작업만 스토리지 엔진이 담당한다.

MySQL 엔진 내부의 주요 처리 순서는 다음과 같다.

• SQL 파서(Parser): 쿼리를 구문 분석
• SQL 옵티마이저(Optimizer): 최적의 실행 계획 수립
• SQL 실행기(Executor): 실행 계획을 수행

이후, 실행기의 요청에 따라 스토리지 엔진이 데이터를 읽거나 쓴다.
즉, SQL 파서 → 옵티마이저 → 실행기는 MySQL 엔진의 영역이고,
데이터 읽기/쓰기는 스토리지 엔진의 영역이다.

대부분의 데이터 접근은 1건의 레코드 단위로 처리된다.
스토리지 엔진을 변경하더라도 결과는 동일하며, 데이터 처리 방식만 달라질 뿐이다.

GROUP BY, ORDER BY 같은 복잡한 연산은 스토리지 엔진이 아닌
MySQL 엔진의 쿼리 실행기에서 처리된다.

현재 서버가 지원하는 스토리지 엔진은 다음 명령으로 확인할 수 있다.

결과의 주요 상태값은 다음과 같다.

• YES: 사용 가능
• DEFAULT: 기본 스토리지 엔진
• NO: 서버에 포함되어 있지 않음
• DISABLED: 서버에는 포함되어 있으나 파라미터 설정으로 비활성화됨

서버에 포함되지 않은 스토리지 엔진을 사용하려면 재컴파일이 필요하지만,
서버가 플러그인 구조로 준비되어 있다면 플러그인 형태의 스토리지 엔진을 추가로 설치할 수 있다.

2. 플러그인 구조

플러그인은 MySQL의 기능을 확장하기 위해 제공되는 구조이다.
스토리지 엔진뿐 아니라 인증, 복제, 보안 등 다양한 기능이 플러그인으로 제공된다.

하지만 기존 플러그인 구조에는 다음과 같은 한계가 있다.

1. 플러그인은 MySQL 서버와만 통신 가능하며, 플러그인 간 직접 통신이 불가능하다.
2. 플러그인은 MySQL 서버 내부 변수나 함수를 직접 호출하므로 캡슐화가 이루어지지 않아 안전하지 않다.
3. 플러그인 간 의존 관계를 설정할 수 없어 초기화 순서를 제어하기 어렵다.

3. 컴포넌트 구조

MySQL 8.0부터는 이러한 플러그인 구조의 한계를 개선하기 위해 컴포넌트(Component) 개념이 도입되었다.
컴포넌트는 기존 플러그인보다 더 안전하고 모듈화된 구조를 가진다.

컴포넌트는 다음과 같은 특징을 가진다.

• 컴포넌트 간 통신 가능
• 캡슐화된 인터페이스 제공
• 의존성 관리 가능

예를 들어, 비밀번호 검증 기능(validate_password) 은
기존 플러그인에서 컴포넌트 형태로 변경되어 제공된다.

이처럼 MySQL 8.0부터는 핵심 보안, 인증, 관리 기능들이
점차 컴포넌트 기반으로 전환되고 있다.

4. 쿼리 실행 구조

MySQL의 쿼리 실행 순서는 다음과 같다.

1. 쿼리 파서(Parser)
   SQL 문을 토큰 단위로 분리하고 트리 형태로 구조화한다.
   문법 오류는 이 단계에서 감지된다.
2. 전처리기(Preprocessor)
   파서 트리를 기반으로 구조적 문제를 검사한다.
   테이블, 칼럼, 권한 등의 존재 여부를 확인한다.
3. 옵티마이저(Optimizer)
   쿼리를 가장 효율적으로 실행할 방법(실행 계획)을 결정한다.
4. 실행 엔진(Execution Engine)
   옵티마이저의 계획에 따라 스토리지 엔진에 데이터를 요청한다.
5. 핸들러(스토리지 엔진)
   실제 데이터를 디스크에 저장하거나 읽는다.

쿼리 캐시

MySQL 5.7 버전까지는 SQL 실행 결과를 메모리에 캐싱하는 기능이 있었다.
하지만, 동시성 문제와 캐시 관리 오버헤드로 인해 8.0부터 제거되었다.

5. 스레드 풀

MySQL 커뮤니티 에디션은 스레드 풀을 지원하지 않는다.
엔터프라이즈 에디션 또는 Percona Server 플러그인을 통해 사용할 수 있다.

Percona Server의 스레드 풀은 CPU 코어 수만큼 스레드 그룹을 생성하며,
다음 변수를 통해 제어한다.

• thread_pool_size: 스레드 그룹 수
• thread_pool_oversubscribe: 초과 허용 작업 수
• thread_pool_stall_limit: 지연 허용 시간(ms)
• thread_pool_max_threads: 전체 스레드 최대 수

6. 트랜잭션 메타데이터 관리

MySQL 5.7까지는 메타데이터를 파일 기반으로 관리했다.
이 방식은 트랜잭션이 지원되지 않아 서버 비정상 종료 시 데이터 불일치가 발생했다.

8.0부터는 InnoDB 테이블 기반 메타데이터 관리로 개선되어,
모든 시스템 테이블이 InnoDB 엔진을 사용한다.
이로 인해 스키마 변경 작업이 트랜잭션 단위로 완전 성공 또는 실패로 처리된다.

7. InnoDB 스토리지 엔진 아키텍처

(InnoDB 아키텍처 구조도)

주요 특징

1. 프라이머리 키 클러스터링
   데이터가 PK 순서대로 저장되며, 세컨더리 인덱스는 PK를 주소로 사용한다.
2. 외래 키 지원
   InnoDB만 외래 키를 지원한다.
   부모-자식 관계 확인 시 잠금이 여러 테이블에 걸리므로 데드락에 주의해야 한다.
   foreign_key_checks 옵션을 일시적으로 비활성화하면 데이터 적재 속도를 높일 수 있다.
3. MVCC (Multi Version Concurrency Control)
   언두 로그(Undo Log)를 이용해 잠금 없는 일관된 읽기를 제공한다.
4. 자동 데드락 감지
   InnoDB는 데드락 감지 스레드를 통해 주기적으로 잠금 그래프를 검사한다.
   언두 로그가 적은 트랜잭션이 롤백 대상이 된다.
5. 자동 복구 기능
   MySQL 시작 시 InnoDB는 자동으로 복구를 수행한다.
   문제가 발생하면 innodb_force_recovery 변수를 설정해 복구할 수 있다.

8. InnoDB 버퍼 풀

버퍼 풀은 디스크의 데이터와 인덱스를 메모리에 캐싱하는 공간이다.
쓰기 작업을 지연시켜 일괄 처리하는 버퍼 역할도 한다.

구조

• LRU 리스트: 자주 접근된 페이지를 오래 유지
• 플러시 리스트: 변경된(Dirty) 페이지 관리
• 프리 리스트: 비어 있는 페이지 관리

버퍼 풀 크기는 innodb_buffer_pool_size로 설정하며,
5.7 이상부터는 동적 조절이 가능하다.

9. 리두 로그(Redo Log)

리두 로그는 비정상 종료 시 데이터 유실을 방지하는 복구용 로그이다.
데이터 변경 전 로그에 기록해두고, 서버 재시작 시 이를 기반으로 복구한다.

8.0부터는 리두 로그를 비활성화할 수 있어 대량 데이터 적재 시 속도를 높일 수 있다.

10. 어댑티브 해시 인덱스

InnoDB가 자주 접근하는 데이터를 자동으로 인덱싱하는 기능이다.
상황에 따라 성능에 도움이 되지 않을 수 있으며,
innodb_adaptive_hash_index 변수로 활성화 또는 비활성화할 수 있다.

• 활성화 권장: 데이터가 메모리에 대부분 존재하고 동등 비교가 많은 경우
• 비활성화 권장: 조인, LIKE 검색, 랜덤 I/O가 많은 경우

11. MyISAM 스토리지 엔진

MyISAM은 과거 MySQL 기본 엔진이었으나 현재는 거의 사용되지 않는다.
다음과 같은 제약이 있다.

• 레코드 단위 락 없음 (테이블 단위 락만 존재)
• 트랜잭션 미지원
• 외래 키 미지원

읽기 전용 용도 외에는 InnoDB가 항상 우수하다.

12. MySQL 로그 파일

에러 로그

서버 구동 중 발생하는 오류, 경고, 종료 메시지를 기록한다.

제너럴 쿼리 로그

모든 쿼리 실행 내역을 기록한다.
쿼리 실행 중 오류가 발생해도 로그에는 기록된다.

슬로우 쿼리 로그

long_query_time 변수에 설정된 시간보다 오래 걸린 쿼리를 기록한다.
쿼리 튜닝에 활용된다.

이상이 MySQL의 주요 구조와 구성 요소이다.
MySQL은 버전이 올라가면서 내부 구조가 단순 파일 기반에서 트랜잭션 기반으로 발전했고,
특히 InnoDB를 중심으로 안정성과 성능이 크게 향상되었다.

다른 DBMS와 다른점 : MySQL은 단순히 사용자의 아이디뿐만 아니라 해당 사용자가 어느 IP에서 접속하고 있는지도 확인

MySQL 8.0 버전부터는 권한을 묶어서 관리하는 역할도 도입되어 각 사용자의 권한으로 미리 준비된 권한 세트를 부여하는 것도 가능

-->

GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'user1'@'localhost';
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'user2'@'localhost';

전에는 이렇게 일일히 권한 부여했음

지금은?

CREATE ROLE developer; // 롤 생성

GRANT SELECT, INSERT, UPDATE ON mydb.* TO developer; // 역할 권한 부여

GRANT developer TO 'user1'@'localhost';
GRANT developer TO 'user2'@'localhost'; // 사용자에게 역할부여

SET DEFAULT ROLE developer TO 'user1'@'localhost'; // 역할 활성화

이렇게 롤 생성해서 유저에게 그 권한을 자동으로 바로바로 부여 가능

MySQL 계정은 '아이디 + 호스트' 조합으로 식별된다

'사용자명'@'호스트명'

--> 

'svc_id'@'127.0.0.1'과 'svc_id'@'%'는 서로 다른 계정

• 클라이언트의 IP 주소(192.168.0.10) 를 확인
• 접속 가능한 계정 중 “가장 구체적인 것(=범위가 좁은 것)” 을 선택
• 그래서 svc_id'@'% 비밀번호 사용하면 실패  svc_id'@'127.0.0.1 비밀번호 사용해야함

MySQL 8.0버전부터 SYSTEM_USER 권한 유무에 따라 시스템 계정과 일반계정으로 구분된다.

시스템 계정

• 다른 계정 관리 : 다른 사용자 생성, 삭제, 권한 부여/회수 가능
• 다른 세션 강제 종료 : (다른 사용자가 실행 중인 쿼리나 세션을 종료 가능 (KILL QUERY, KILL CONNECTION)
• 스토어드 프로그램에 DEEFINER 지정 가능 : 트리거나 프로시저를 만들 때 DEFINER로 자신이 아닌 다른 사용자 계정을 지정 가능
• 서버 관리 작업 수행 가능 : 예: 서버 변수 변경, 복제(Replication) 제어 등

내장 시스템 계정들 : 내부 기능을 위해서만 사용

• mysql.infoschema : INFORMATION_SCHEMA 관련 내부 작업 수행
• mysql.session : 백그라운드 스레드, 복제, 쿼리 로그 등 내부 세션에 사용
• mysql.sys : sys 스키마(성능 보기, 관리용 뷰) 실행용

-> MySQL 내부에서만 사용되며 직접 로그인 불가능
보안상으로도 안전하게 account_locked = 'Y' 로 설정되어 있어서 접속 시도 시 에러

SELECT user, host, account_locked FROM mysql.user WHERE user LIKE 'mysql.%';
+------------------+-----------+----------------+
| user | host | account_locked |
+------------------+-----------+----------------+
| mysql.infoschema | localhost | Y |
| mysql.session | localhost | Y |
| mysql.sys | localhost | Y |
+------------------+-----------+----------------+
3 rows in set (0.01 sec)

MySQL 5.7 버전 까지는 GRANT 명령 만으로 권한 부여와 동시에 계정 생성까지 가능했다.

GRANT ALL ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';

MySQL 8.0부터는 더 안전하게 관리하기 위해 계정 생성과 권한 부여를 완전히 분리

• CREATE USER : 계정 생성
• GRANT : 권한 생성

CREATE USER 'user'@'%'
IDENTIFIED WITH 'mysql_native_password' BY 'password'
REQUIRE NONE
PASSWORD EXPIRE INTERVAL 30 DAY
ACCOUNT UNLOCK;

• IDENTIFIED WITH
  어떤 인증 방식을 쓸지, 그리고 비밀번호를 뭘로 할지를 지정.
  MySQL 8.0부터는 기본 인증 방식이 Caching SHA-2 Authentication으로 변경.
  (이전의 mysql_native_password보다 보안이 강함)
• REQUIRE
  SSL/TLS 암호화 채널을 반드시 쓸지 결정
  “REQUIRE NONE”은 암호화 없이 연결하겠다는 뜻.
  다만, Caching SHA-2 방식을 쓰면 자동으로 암호화된 채널로 연결.
• PASSWORD EXPIRE INTERVAL 30 DAY
  비밀번호를 30일마다 바꾸게 강제.
• PASSWORD HISTORY / REUSE INTERVAL
  예전에 썼던 비밀번호를 일정 기간 동안 재사용하지 못하게 하는 기능.
  예를 들어 PASSWORD HISTORY 5는 “최근 5번 쓴 비밀번호는 다시 못 씀”을 의미
• ACCOUNT LOCK / UNLOCK
  계정을 잠그거나 품.
  보안상 문제가 생긴 계정은 잠가두면 로그인할 수 없음.

MySQL은 기본적으로 “길이”나 “문자 조합”만 검사했지만,
8.0부터는 비밀번호 정책을 세분화할 수 있는 컴포넌트가 들어왔음

-> validate_password 컴포넌트.

이걸 설치하려면:

이제 비밀번호의 복잡도를 검사가능.

• LOW → 길이만 확인
• MEDIUM → 숫자, 대소문자, 특수문자 조합 확인
• STRONG → 위 조건 + “금칙어(금지된 단어)” 포함 여부도 검사

금칙어 목록을 직접 등록가능.

이렇게 하면 금칙어 파일을 참조해서
비밀번호에 특정 단어(예: “password”, “admin” 등)가 들어가면 거부.

이중 비밀번호 (Dual Password)

MySQL 8.0부터 한 계정에 비밀번호를 두 개까지 설정할 수 있다.

• 최근 비밀번호: Primary
• 이전 비밀번호: Secondary

둘 중 하나만 맞아도 로그인할 수 있다.
비밀번호 변경 시 서비스 중단 없이 교체할 수 있다.

권한 (Privilege)

사용자가 수행할 수 있는 작업을 정의한다.

• 글로벌 권한: 서버 전체에 영향을 미침 (예: SUPER, CREATE USER, SHUTDOWN) → ON *.*으로 지정
• 객체 권한: 특정 데이터베이스나 테이블에만 적용 (예: SELECT, INSERT, UPDATE, DELETE) → 대상 명시 필요
• 동적 권한: 실행 중 동적으로 생성되는 권한 (복제, 플러그인 관리 등)

권한 부여 예시:

역할 (Role)

MySQL 8.0에서 새로 도입된 기능으로, 여러 권한을 묶어 하나의 세트로 관리한다.

역할 생성 및 권한 부여 예시:

이렇게 하면 user1, user2는 developer 역할을 통해 해당 권한을 자동으로 가진다.

오라클은 데이터베이스와 이를 액세스하는 프로세스 사이에 SGA라고 하는 메모리 캐시 영역이 존재한다.

디스크를 경유한 입출력은 물리적으로 액세스 암(Arm)이 움직이면서 헤드를 통해 데이터를 읽고 쓰는 반면 메모리 캐시를 통한 입출력은 전기적 신호에 불가하기에 디스크 I/O와 비교하면 엄청 빠르다.

많은 프로세스가 동시에 데이터를 액세흐하므로 사용자 데이터를 보호하는 Lock은 물론 공유 메모리 영역인 SGA상에 위치한 데이터 구조에 대한 액세스를 직렬화 하기 위한 Lock 매커니즘 (Latch)도 필요하다.

오라클은 블록 단위로 읽고, 변경이 발생한 블록만 찾아 블록 단위로 저장한다.

오라클 백그라운드에서 DBWR과 CKPT 프로세스가 캐시와 데이터파일간 동기화를 주기적으로 수행해준다.

오라클에서 디스크에 저장된 데이터 집합(Datafile, Redo Log File, Control File 등)을 데이터베이스라고 부른다. 그리고 SGA 공유 메모리 영역과 이를 액세스하는 프로세스 집합을 합쳐서 인스턴스 라고 한다.

프로세스 집합을 다시 서버 프로세스와 백그라운드 프로세스로 나눌수 있다. 

- 서버 프로세스 : 전면에서 사용자가 던지는 명령 처리

- 백그라운드 프로세스 : 뒤에서 명령을 처리

리스너에 연결요청을 하는 순간 하나의 프로세스를 띄우고 PGA 메모리를 할당한다. 이는 비용이 매우 큰 작업이므로, 일련의 SQL문을 수행하기 위해 매번 연결요청을 한다면 결코 성능이 좋지 않다. 오라클에 접속하는 애플리케이션을 구축할때 커넥션 풀 기능이 필요한 이유다.

DB 버퍼 캐시

사용자가 입력한 데이터를 데이터파일에 저장하고 이를 다시 읽는 과정에서 거쳐 가는 캐시 영역은 SGA 구성요소 중 하나인 DB버퍼캐시이다.

////

LRU 리스트를 보호하기 위해 사용하는 래치를 cache buffers lru chain 래치하고 한다.

버퍼 상태

- Free 버퍼 : 인스턴스 기동 후 아직 데이터가 읽히지 않아 비어있는 상태 (Clean) 이거나 데이터가 담겼지만 데이터파일과 서로 동기화돼 있는 상태여서 언제든지 덮어 써도 무방한 버퍼 블록, 오라클이 데이터 파일로부터 새로운 데이터 블록을 로딩하려면 먼저 Free 버퍼를 확보해야한다. Free 상태인 버퍼에 변경이 발생하면 그 순간 Dirty 버퍼로 상태가 바뀐다.

- Dirty 버퍼 : 버퍼에 캐시된 이후 변경이 발생했지만, 아직 디스크에 기록되지 않아 데이터 파일 블록과 동기화가 필요한 버퍼 블록을 말한다. 이 버퍼 블록들이 다른 데이터 블록을 위해 재사용되려면 디스크에 먼저 기록이 되어야하며, 디스크에 기록되는 순간 Free 버퍼로 상태가 바뀐다.

- Pinned 버퍼 : 읽기 또는 쓰기 작업을 위해 현재 액세스되고 있는 버퍼 블록을 말한다.

비용기반 옵티마이저는 사용자 SQL을 최적화에 유리한 형태로 재작성하는 작업을 먼저한다.

비용기반 옵티마이저의 서브 엔진 Query Transformer가 해당 역할을 담당한다.

쿼리변환 : 쿼리 옵티마이저가 SQL을 분석해 의미적으로 동일(같은 결과를 리턴)하면서도 더 나은 성능을 기대되는 형태로 재작성하는 것을 말한다.

1. 서브쿼리 Unnesting
2. 뷰 Merging
3. 조건절 Pushing
4. 조건절 이행
5. 공통 표현식 제거
6. Outer 조인을 Inner 조인으로 변환
7. 실체화 뷰 쿼리로 재작성
8. Star 변환
9. Outer 조인 뷰에 대한 조인 조건 Pushdown
10. OR-expansion

이 존재한다.

쿼리 변환은 2가지 종류가 있는데

• 휴리스틱 쿼리 변환 : 결과만 보장된다면 무조건 쿼리 변환을 수행한다. 규칙 기반 최적화 기법이라고 할 수 있다 
• 비용기반 쿼리 변환 : 변환된 쿼리의 비용이 더 낮을때만 그것을 사용하고 그렇지 않을때는 원본 쿼리 그대로 두고 최적화를 수행한다. 

서브쿼리 Unnesting

서브쿼리는 하나의 SQL문장 내에서 괄호로 묶인 별도의 쿼리 블록을 말한다. 즉 쿼리에 내장된 또 다른 쿼리다.

1. 인라인 뷰 : from 절에 나타나는 서브쿼리를 말한다
2. 중첩된 서브쿼리 : 결과집합을 한정하기 위해 where절에 사용된 서브쿼리를 말한다. 서브쿼리가 메인쿼리에 있는 컬럼을 참조하는 형태를 '상관관계 있는 서브쿼리'라고한다.
3. 스칼라 서브쿼리 : 한 레코드당 정확히 하나의 컬럼 값만을 리턴하는 것, select-list에서 대부분 사용하지만 컬럼이 올 수 있는 대부분 위치에서 사용가능하다.

옵티마이저는 쿼리 블록 단위로 최적화를 수행한다. 즉 쿼리 블록 단위로 최적의 액세스 경로와 조인 순서, 조인 방식을 선택하는 것을 목표로 한다.

하지만 각 서브쿼리가 최적화되었다고 쿼리 전체가 최적화됐다고 할 수 없다.

서브쿼리를 풀어내는 두 가지 쿼리 변환중 '서브쿼리 Unnesting'은 중첩된 서브쿼리와 관련있고

뷰 Merging은 인라인 뷰와 관련있다.

서브쿼리 Unnesting의 의미

nest는 상자 등을 차곡차곡 포개넣다 라는 설명이 있다.

즉 unnest는 중첩된 상태를 풀어낸다가 된다.

중첩된 서브쿼리는 부모와 자식이라는 중속적이고 계층적인 관계가 존재한다.

이런 종속적이고 계층적인 존재는 처리과정에서 IN, Exists를 불문하고 필터 방식이어야한다. 즉 메인 쿼리에서 읽히는 레코드마다 서브쿼리를 반복수행하면서 조건에 맞지 않는 데이터를 골라내는 것이다.

하지만 필터 방식이 항상 최적의 수행속도를 보장하지 못하므로 옵티마이저는 둘 중 하나를 선택한다.

• 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화한다. -> 서브쿼리 Unnesting
• 서브쿼리를 Unnesting하지 않고 원래대로 둔 상태에서 최적화한다. 메인쿼리와 서브쿼리를 별도의 서브플랜으로  구분해 각각 최적화를 수행하며 이때 서브쿼리에 필터 오퍼레이션이 나타난다.

첫번째 서브쿼리 Unnesting은 메인과 서브쿼리 간의 서브쿼리 간의 계층구조를 풀어 서로 같은 레벨로 만들어준다는 점에서 서브쿼리 Flattienng이라고도 부른다.

두번째처럼 Unnesting하지 않고 쿼리 블록별로 최적화할때는 각각의 최적이 쿼리문 전체의 최적을 달성하지 못할때가 많다. 그리고 Plan Generator가 고려대상으로 삼을만한 다양한 실행계획을 생성해 내는 작업이 매우 제한적인 범위 내에서만 이루어진다.

서브쿼리를 위해 오라클 옵티마이저가 사용하는 최적화 기법이 한가지 더 있다. where 조건절에 사용된 서브쿼리가 메인쿼리와 상관관계에 있지 않으면서, 단일로우를 리턴하는 아래와 같은 형태의 서브쿼리를 처리할 때 나타나는 방식이다.
- SELECT * FROM tab1 WHERE key1 = (select avg(col1) from tab2)l
- SELECT * FROM tab1 WHERE (key1, key2) = (SELECT col1, col2 from tab2 WHERE col3 >=5000 and rownum = 1);
위와 같은 서브쿼리는 Fetch가 아닌 Execute 시점에 먼저 수행되어 그 결과 값을 메인 쿼리에 상수로 제공한다
- SELECT * FROM tab1 WHERE key1 = value1
- SELECT * FROM tab1 WHERE (key1, key2) = (value1, value2)
조건절에서 서브쿼리를 in이 아닌 '='조건으로 비교한다는 것은 서브쿼리가 단일 로우를 리턴하게 됨을 의미하므로 이런 방식을 사용할 수 있는 것이다. 이들 서브쿼리가 2개 이상의 로우를 리턴하면 에러가 발생하므로 rownum <= 1이나 stopkey, min, max, avg등 집계함수가 사용된다.

서브쿼리 Unnesting의 이점

서브쿼리를 메인쿼리와 같은 레벨로 풀어낸다면 다양한 액세스 경로와 조인 메소드를 평가할 수 있다. 특히 옵티마이저는 많은 조인 테크닉을 가지기 때문에 조인 형태로 변활했을때 더 나은 실행계획을 찾을 가능성이 높아진다.

그래서 옵티마이저는 서브쿼리 Unnesting을 선호한다

옵티마이저 힌트 : unnest, no_unnest

예시 : SELECT * FROM emp WHERE deptno in (SELECT deptno FROM dept)

Unnesting하지않고 그대로 최적화하면 옵티마이저는 필터 방식의 실행계획을 수립한다.

Predicate 정보를 보면 필터 방식으로 수행된 서브쿼리의 조건절이 바인드 변수로 처리된 부분 (DEPTNO = :B1)이 눈에 띄는데, 이것을 통해 옵티마이저가 서브쿼리를 별도의 서브플랜으로 최적화한다는 것을 알수있다. 메인 쿼리도 하나의 쿼리 블록이므로 서브쿼리를 제외한 상태에서 별도의 최적화가 이루어졌다.

Unnesting하지 않은 서브쿼리를 수행할 때는 메인 쿼리에서 읽히는 레코드마다 값을 넘기면서 서브쿼리를 반복수행한다.

서브쿼리 Unnest를 하면 서브쿼리인데도 일반적인 NL 조인 방식으로 수행되었다.

Unnestinge된 쿼리의 조인 순서 조정

leading 힌트를 사용해서 쿼리의 조인 순서를 조정할 수 있다.

서브쿼리가 M쪽 집합이거나 Nonunique인덱스일때

결과 오류가 발생하거나. Unique 인덱스가 없어서 옵티마이저는 emp와 dept 간의 관계를 알 수 없고, 결과를 확신할 수 없어서 일반 조인문으로 쿼리 변환을 시도하지 않는다.

그래서

• 1쪽 집합임을 확신할 수 없는 서브쿼리 쪽 테이블이 드라이빙된다면, 먼저 Sort unique 오퍼레이션을 수행함으로써 1쪽 집합으로 만든 다음에 조인한다.
• 메인 쿼리 쪽 테이블이 드라이빙 된다면 세미 조인 방식으로 조인한다.

Sort Unique 오퍼레이션

Sort Unique는 distinct로 만들어진거다.

세미 조인 방식으로 수행

만약 같은 값을 찾으면 바로 끝내도록 수행, 해시 세미 조인, 소트머지 조인도 가능

필터 오퍼레이션과 세미조인의 캐싱효과

서브쿼리를 Unnesting 하지 않으면 쿼리를 최적화하는 데 있어 선택의 폭이 넓지 않아 불리하다. 메인 쿼리를 수행하면서 건건이 서브쿼리를 반복 수행하는 단순한 필터 오퍼레이션을 사용할 수 밖에 없기때문. 대량의 집합을 기준으로 Random 액세스 방식으로 서브쿼리 집합을 필터링 한다면 결코 빠른 수행 속도를 얻을 수 없다.

--> 오라클은 서브쿼리 수행 결과를 버리지않고 내부 캐시에 저장하고 있다가 같은 값이 입력되면 저장된 값을 출력하는 캐싱을 사용한다.

근데 10g이상부터는 세미 조인도 캐싱 효과를 가지므로 필터 오퍼레이션은 따잇 당해버렸다.

Anti 조인

not exists, not in 서브쿼리도 unnesting하지 않으면 아래와 같이 필터 방식으로 처리된다.

기본 루틴은 exists 필터와 동일하며 조인에 성공하는 레코드가 하나도 없을 때만 결과집합에 포함시킨다는 점이다.

- exists 필터 : 조인에 성공하는 레코드를 만나는 순간 결과집합에 담고 다른 레코드로 이동한다.

- not exists 필터 : 조인에 성공하는 레코드를 만나는 순간 버리고 다음 레코드로 이동한다. 조인에 성공하는 레코드가 하나도 없을때만 결과집합에 담는다.

집계 서브쿼리 제거

집계함수를 포함하는 서브쿼리를 Unnesting하고 이를 다시 분석 함수로 대체하는 쿼리 변환이 10g에서 도입되었다.

Pushing 서브쿼리

Pushing 서브쿼리는 실행계획상 가능한 앞단계에서 서브쿼리 필터링이 처리되도록 강제하는 것. (push_subq)

Pushing 서브쿼리는 Unnesting 되지않은 서브쿼리에만 작동된다. --> push_subq는 no_unnest힌트와 같이 기술

뷰 Merging

뷰 쿼리 블록은 액세스 쿼리 블록과의 머지 과정을 거쳐 일반적인 쿼리 형태로 변환하는데 이를 뷰 Merging이라고 한다. 이래야 옵티마이저가 더 다양한 액세스 경로를 조사대상으로 삼는다.

단순 뷰 Merging

조건절과 조인문만을 포함하는 단순 뷰는 no_merge 힌트를 사용하지 않는 한 언제든 Merging이 일어난다.

반면 group by절이나 distinct연산을 포함하는 복합 뷰는 파라미터 설정 또는 힌트 사용에 의해서만 뷰 Merging이 가능하다. 또한 집합 영산자. connect by, rownum등을 포함하는 복합 뷰는 아예 뷰 머징이 불가능한다.

복합 뷰 Merging

group by 절, select-list에 distinct 연산자

같은 복합 뷰는 _complex_view_merging 파라미터를 true로 했을때만 머징이 일어난다.

merge 힌트를 써도

• 집합 연산자 (union, union all, intersect, minus)
• connect by 절
• ROWNUM pesudo 컬럼
• select-list에 집계 함수 사용
• 분석 함수

는 뷰 머징을 쓸수없다.

Merging 되지않은 뷰의 처리방식

뷰 Merging을 시행했을때 오히려 비용이 증가한다고 판단되거나 부정확한 결과집합이 만들어질 경우, 옵티마이저는 뷰 Merging을 포기한다. 어떤 이유에서건 뷰 Merging이 이루어지지 않았을땐 2차적으로 조건절 Pushing을 시도한다. 하지만 이마저도 실패한다면 뷰 쿼리 블록을 개별적으로 최적화하고, 거기서 생성된 서브플랜을 전체 실행계획을 생성하는 데 사용한다. 실제 쿼리를 수행할 때도 뷰 쿼리의 수행 결과를 액세스 쿼리에 전달하는 방식을 사용한다.

조건절 Pushing

뷰를 액세스하는 쿼리를 최적화할때 옵티마이저는 1차적으로 뷰 머징을 고려한다. 하지만

• 복합 뷰 머지 기능이 비활성화
• 사용자가 no_merge 힌트사용
• Non-mergable Views : 뷰 머징 시행하면 부정확한 결과 가능성
• 비용기반 쿼리 변환이 작동해 No Merging 선택시

뷰 머징을 실패할 수 있다.

실패하면 옵티마이저는 뷰를 참조하는 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 Pushing하는 기능을 일컫는다.

조건절이 가능한 빨리 처리되도록 뷰 안으로 밀어 넣는다면, 뷰 안에서의 처리 일량을 최소화하게 됨은 물론 리턴되는 결과 건수를 줄임으로써 다음 단계에서 처리해야할 일량을 줄일 수 있다.

종류 

• 조건절 Pushdown : 쿼리 블록 밖에 있는 조건들은 쿼리 블록 안쪽으로 밀어 넣는 것을 말함
• 조건절 Pullup : 쿼리 블록 안에 있는 조건들을 쿼리 블록 밖으로 내오는 것을 말하며, 그것을 다시 다른 쿼리 블록에 Pushdown 하는데 사용함
• 조인 조건 Pushdown : NL 조인 수행 중에 드라이빙 테이블에서 읽은 값을 건건이 Inner 쪽 뷰 쿼리 블록 안으로 밀어 넣는 것을 말함

조건절 Pushdown과 Pullup은 항상 더 나은 성능을 보장하므로 별도의 힌트를 제공하지 않는다. 하지만 조인 조건 Pushdown은 NL 조인을 전제로 하기 때문에 성능이 더 나빠질 수 있다. 따라서 오라클은 조인 조건 Pushdown을 제어할 수 있도록 push_pred와 no_push_pred 힌트를 제공한다.

그런데 해당 기능이 10g에서 비용기반 쿼리 변환으로 바뀌었고 이 때문에 9i에서 빠르게 수행되던 쿼리가 10g로 이행하면서 오히려 느려지는 현상이 종종 나타난다. 이때는 opt_param('_optimizer_push_pred_cost_based', 'false') 힌트를 사용하면 된다.

옵티마이저 : 사용자가 요청한 SQL을 가장 효율적이고 빠르게 수행할 수 있는 최적의 처리경로를 선택해주는 DBMS의 핵심엔진

구조화된 질의언어로 사용자가 원하는 결과집합을 정의하면 이를 얻는데 필요한 처리절차(프로시저)는 DBMS에 내장된 옵티마이저가 자동으로 생성해준다.

• 규칙기반 옵티마이저 (RBO)
  • 인덱스 구조, 연산자, 조건절 형태가 순위를 결정짓음 (우선순위 표가 있음)
  • OLTP 환경의 중소형 데이터베이스 시스템이라면 RBO 규칙이 어느정도 보편 타당성을 갖지만 대용량 데이터를 처리하는데 있어 합리적이진 않다. (항상 인덱스를 신뢰해서 Full Table Scan과의 손익 따지지않음)
  • 이제 오라클은 CBO만 지원함
• 비용기반 옵티마이저 (CBO)
  • 비용을 기반으로 최적화 수행 (비용 : 쿼리를 수행하는데 소요되는 일량, 시간)
  • 비용은 예상치이다. 미리구해놓은 테이블과 인덱스에 대한 여러 통계정보를 기초로 각 오퍼레이션 단계별 예상 비용을 산정하고 이를 합산한 총비용이 가장 낮은 실행계획 하나를 선택한다.
  • 비용을 산정할때 통계항목에는 레코드 개수, 블록 개수, 평균 행 길이, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 같은 것들이 있다. (CPU속도, 디스크 I/O속도 등도 이용)

최적화 수행단계

1. 사용자가 던진 쿠리스행을 위해, 후보군이 될만한 실행계획을 찾는다.

2. 데이터 딕셔너리에 미리 수집해놓은 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.

3. 각 실행계획의 비용을 비교해서 최저비용을 갖는 하나를 선택한다.

만약 테이블과 인덱스에 대한 통계정보가 없거나 너무 오래돼 신뢰할 수 없을때 옵티마이저가 동적으로 샘플링을 수행하도록 할 수 있다.

optimizer_dynamic_sampling 파라미터로 동적 샘플링 레벨을 조정한다 (9i : 1단계 [조건 만족시 수행]-> 10g 2단계 [통계정보없는 테이블을 발견시 무조건 동적 샘플링 수행])

레벨은 10까지 가능, 레벨이 높을수록 샘플링에 사용되는 표본 블록 개수도 증가, 동적 샘플링 통계정보는 데이터 딕셔너리에 영구저장X, 하드파싱할때마다 동적 샘플링 SQL이 추가로 수행되므로 성능이 안좋으니 주기적으로 통계정보 관리해야한다.

• Query Transformer : 사용자가 던진 SQL을 그대로 최적화하는 것이 아니라 우선 최적화 하기 쉬운 형태로 변환을 시도한다. 쿼리 변환 전후 결과가 동일함을 보장할때만
• Estimator : 쿼리 오퍼레이션이 각 단계의 선택도, 카디널리티, 비용을 계산하고, 궁극적으로 실행계획 전체에 대한 총 비용을 계산해낸다. 각 단계를 수행하는 데 필요한 I/O, CPU, 메모리 사용량등을 예측하기 위해 데이터베이스 오브젝트 통계정보와 하드웨어적인 시스템 성능 통계정보를 이용한다.
• Plan Generator : 하나의 쿼리를 수행하는 데 있어, 후보군이 될만한 실행계획들을 생성해내는 역할

옵티마이저 모드

• rule
  • RBO 모드로 변경
• all_rows
  • 쿼리 최종 결과집합을 끝까지 Fetch하는 것을 전제로, 시스템 리소스를 가장 적게 사용하는 실행계획 선택
  • DML 문장은 일부 데이터만 가공하고 멈출수 없으므로 모드 상관없이 all_rows 모드로 작동, select 문장도 union, minus 같은 집합 연산자나 for update절을 사용하면 all_rows 모드로 작동
• first_rows
  • 전체 결과집합 중 일부 로우만 Fetch하다가 멈추는 것을 전데로, 가장 빠른 응답 속도를 낼 수 있는 실행계획 선택, 끝까지 Fetch한다면 오히려 더 많은 리소스를 사용하고 전체 수행 속도도 느려질 수 있다.
  • 9i부터 새로운 first_row_n이 도입되어 과거 버전과의 호환성을 위한 용도로만 남음
• first_rows_n
  • 처음 n개 로우만 Fetch하는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택 (n = 1, 10, 100, 1000  4개중 선택), n개 로우 이상 fetch한다면 오히려 많은 리소스를 사용하고 전체 수행 속도도 느려질 수 있다.
  • 완전한 CBO 모드
• choose
  • 액세스되는 테이블 중 적어도 하나에 통계정보가 있다면 CBO, all_rows모드 선택. 없으면 RBO

옵티마이저 행동에 영향을 미치는 요소

• SQL과 연산자 형태
• 인덱스, IOT, 클러스터링, 파티셔닝, MV등 옵티마이징 팩터
• 제약 설정 : PK, FK, Not Null, Check (개체무결성, 참조 무결성, 도메인 무결성, 사용자 정의 무결성)
• 옵티마이저 힌트
• 통계정보 : 오브젝트 통계, 시스템 통계
• 옵티마이저 관련 파라미터
• DBMS 버전과 종류

옵티마이저의 한계

• 부족한 옵티마이징 팩터 : 적절한 옵티마이징 팩터를 제공하는 것은 사람의 몫
• 부정확한 통계
• 히스토그램의 한계 : 히스토그램 버킷 개수로 254개까지만 허용되는 제약조건 있음
• 바인드 변수 사용 시 균등분포 가정 : 바인드 변수 사용한 SQL은 정확한 컬럼 히스토그램도 무용지물이다. 옵티마이저가 균등분포를 가정하고 비용을 계산하기 때문
• 결합 선택도 산정의 어려움 : 조건절 컬럼이 서로 상관관계에 있으면 정확한 데이터 분포와 카디널리티를 산정하기 어려움
• 비현실적인 가정 : CBO는 쿼리 수행 비용을 평가할때 여러 가정을 사용하는데 그중 매우 비현실적인게 있음 -> optimizer_index_caching, optimizer_index_cost_adj 같은 파라미터로 보정
• 규칙에 의존하는 CBO : 부분적으로는 규칙에 의존한다. (ex. 원격 테이블, External 테이블)
• 하드웨어 성능 특성

두 대안의 인덱스의 예상 비용이 같을때 알파벳 순으로 앞선 것을 선택한다.

-> 이럴때 문제점은 만약 둘이 같다면 PK를 사용해야하는데 인덱스를 쓸수도있음

통계정보 

옵티마이저가 참조하는 통계정보

• 테이블 통계
• 인덱스 통계
• 컬럼 통계
• 시스템 통계

카디널리티

선택도

전체 대상 레코드 중에서 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율

선택도를 가지고 카디널리티를 구하고, 다시 비용을 구함으로써 인덱스 사용 여부, 조인 순서와 방법등을 결정하므로 선택도는 최적의 실행계획을 수립하는 데 매우 중요

카디널리티

특정 액세스 단계를 거치고 나서 출력될 것으로 예상되는 결과 건수

카디널리티 = 총 로우 수 x 선택도

카디널리티 = 총 로우 수 x 선택도 = num_rows / num_distinct

히스토그램

• 높이균형 히스토그램
  • equi-depth 히스토그램으로도 불린다. 컬럼이 가진 값의 수보다 적은 버킷을 요청할 때 만들어진다. 높이 균형히스토그램에서는 버킷 개수보다 값의 수가 많기 때문에 하나의 버킷이 여러 개 값을 담담한다.
  • 말그래도 버킷의 높이가 같다.
• 도수분포 히스토그램
  • value-based 히스토그램으로도 불리고, 빈도수를 저장하는 히스토그램
  • 컬럼 값마다 하나의 버킷을 할당 (값의 수 = 버캣 개수), 사용자가 요청한 버킷 개수가 컬럼이 가진 값의 수보다 많거나 같을때 사용되며, 최대 254ㅐ 버킷만 허용하므로 넘으면 사용할 수 옶더,

• FREQUENCY : 값별로 빈도수를 저장하는 도수분포 히스토그램
• HEIGHT-BALANCED : 각 버킷의 높이가 동일한 높이 균형 히스토그램
• NONE : 히스토그램을 생성하지 않은 경우

비용

Nested Loops 조인

NL조인 힌트

• ordered 힌트는 from 절에서 기술된 순서대로 조인하라고 옵티마이저에게 지시
• use_nl 힌트는 NL 방식으로 조인하라고 지시
• -> dept 테이블 = Driving (Outer Table), 을 기준으로 emp 테이블 (Inner Table)과 NL 조인하라는듯

• C를 기준으로 A, D를 NL조인하고 B는 해쉬 조인하라는 뜻

• 옵티마이저야 알아서 해줘

NL 조인 수행 과정 분석

1. 먼저 dept_loc_idx 스캔
2. 인덱스 rowid로 dept 테이블 액세스
3. emp_deptno_idx 인덱스 범위 스캔
4. rowid로 emp테이블 액세스
5. sal 기준 내림차순

OLTP 시스템에서 조인을 튜닝할 때는 일차적으로 NL 조인부터 고려하는 것이 올바른 순서이다. 

각 단계의 수행일량을 분석해 과도한 Random 액세스가 발생하는 지점 파악

• 조인 순서를 변경해 Random 액세스 발생량을 줄이거나
• 인덱스 컬럼 구성을 변경해서 다른 인덱스 사용 고려

-> 여기까지 했는데 안되면 해시조인이나 소트 머지 조인검토

NL 조인의 특징

• NL 조인은 Random 액세스 위주 --> 대량 데이터 조인할때 비효율
• 한 레코드씩 순차적으로 진행 --> 부분범위처리 가능한 상황에서 대용량 집합이라도 효율적 (Rownum <= 10)
• 인덱스 구성전략 중요 --> 조인 컬럼에 대한 인덱스, 컬럼 구성

튜닝 해봅시다

select /*+ ordered use_nl(e) index(j) index(e) */
       j.job_title, e.first_name, e.last_name
     , e.hire_date, e.salary, e.email, e.phone_number
from   jobs j, employees e
where  e.job_id = j.job_id                            -- ①
and    j.max_salary >= 1500                          -- ②
and    j.job_type = 'A'                              -- ③
and    e.hire_date >= to_date('19960101', 'yyyymmdd') -- ④

* pk_jobs : jobs.job_id
* jobs_max_sal_ix : jobs.max_salary
* pk_employees : employees.employee_id
* emp_job_ix : employees.job_id
* emp_hiredate_ix : employees.hire_date

이런 SQL과 인덱스 구성일떄

이런 경우 뭐가 문제일까

JOBS_MAX_SAL_IX 인덱스를 스캔하고 jobs 테이블을 액세스한 횟수가 278번인데 job_type ='A' 조건을 필터링 한 결과는 3건이다.

불필요한 테이블 액세스를 많이 한 셈이다.

--> jobs_max_sal_ix 인덱스에 job_type 컬럼을 추가해야겠구나

이런 경우 뭐가 문제일까

jobs_max_sal_ix 인덱스로 부터 3건을 받기위해 인덱스 블록을 1000개 읽었다.

--> 인덱스 컬럼 구성을 변경해줘야겠구나. 불필요한 데이터 검색 막기.

이건 뭐가 문제일까

1278건을 읽기위해 인덱스에서 스캔한 블록이 4개뿐이라 효율적

테이블 액세스한 불필요한 데이터도 없음

jobs테이블을 읽고나서 employees 테이블과의 조인 시도 횟수가 1278번인데 최종은 5건

--> 조인 순서를 바꿔야겠구나

테이블 Prefetch

테이블 Prefetch : 인덱스를 경유해 테이블 레코드를 액세스하는 도중 디스크에서 캐시로 블록을 적재해야하는 상황이 발생할 수 있는데, 그때 다른 테이블 블록까지 미리 적재해 두는 기능

새로운 포맷의 실행계획이 생긴다고 테이블 prefetch가 작동한것이 아닌 그 기능이 활성화되었다는뜻

Prefetch 기능이 실제 작성할 때면 db file sequential read 대기 이벤트 대신 db file parallel reads 대기 이벤트가 나타난다.

Prefetch는 디스크 I/O와 관련있다. 디스크 I/O는 비용이 많이 들기 때문에 한번 I/O Call할때 읽을 가능성이 큰 블록들을 캐시에 미리 적재해 두는 기능이다. 한번 I/O Call로 여러 Single Block I/O 동시에 수행 (배민기사가 여러 개 적재하는겨)

Outer 쪽 인덱스를 Unique Scan 할 때는 작동하지 않는다

• Inner 쪽 Non-Unique 인덱스를 Range Scan 할때는 테이블 Prefetch 실행계획이 항상 나타난다.
• Inner 쪽 Unique 인덱스를 Non-unique 조건 range scan 할때도 테이블 prefetch 실행계획이 항상 나타난다.
• Inner 쪽 Unique 인덱스를 unique 조건으로 액세스 할떼도 테이블 prefetch 실행계획이 나타날수있다. 이때 인덱스는 range scan으로 액세스한다. 테이블 prefetch 실행계획이 안나타날때는 unique scan으로 액세스한다.

224쪽  예시 확인

배치 I/O

Inner 쪽 인덱스만으로 조인을 하고나서 테이블과의 조인은 나중에 일괄 처리하는 메커니즘$

1. 드라이빙 테이블에서 일정량의 레코드를 읽어 Inner 쪽 인덱스와 조인하면서 중간 결과집합을 만든다.
2. 중간 결과집합이 일정량 쌓이면 Inner 쪽 테이블 레코드를 액세스한다. 이때 테이블 블록을 버퍼 캐시에서 찾으면 바로 최종 결과집합에 담고, 못 찾으면 중간 집합에 남겨 둔다.
3. 2번 과정에서 남겨진 중간 집합에 대한 Inner쪽 테이블 블록을 디스크로부터 읽는다. 이때 multiple single block i/o 방식을 사용한다.
4. 버퍼 캐시에 올라오면 테이블 레코드를 읽어 최종 결과 집합에 담는다.
5. 모든 레코드를 처리하거나 사용자가 fetch call을 중단할때까지 1~4 반복한다.

-> Outer 테이블로부터 액세스되는 Inner 쪽 테이블 블록에 대한 디스크 I/O Call 횟수를 줄이기 위해, 테이블 Prefetch에 이어 추가로 도입된 메커니즘, nlj_batching no_nlj_batching(테이블 prefetch) 힌트로 제어 

배치 I/O를 사용하면 Inner 쪽 테이블 블록이 모두 버퍼캐시에서 찾아지지 않으면 = 실제 배치 IO가 작동한다면 데이터 정렬 순서가 달라질수있다. 모두 버퍼 캐시에서 찾을 때는 이전 메커니즘과 똑같은 정렬 순서를 보인다.

버퍼 Pinning 효과

• 8i : 테이블 블록에 대한 버퍼 Pinning 기능이 작동하기 시작
  • Inner 쪽 인덱스를 통해 액세스되는 테이블 블록이 계속 같은 블록을 가리키면 논리I/O가 추가로 발생하지않는다.
  • Outer 레코드에 대한 Inner 쪽과의 조인을 마치고 다른 레코드를 읽기 위해 Outer 쪽으로 돌아오는 순간 Pin 해제
• 9i : Inner 쪽 인덱스 루트 블록에 대해 버퍼 Pinning 효과 나타나기 시작
• 10i : 하나의 Outer 레코드에 대한 Inner 쪽과의 조인을 마치고 Outer 쪽으로 돌아오더라도 테이블 블록에 대한 Pinning 상태를 유지
• 11i : inner쪽 루트 아래 인덱스 블록들도 Pinning 하기 시작

소트 머지 조인

기본 메커니즘

두 테이블을 각각 정렬한 다음 두 집합을 머지하면서 조인을 수행

1. 소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬
2. 머지 단계 : 정렬된 양쪽 집합을 서로 머지

- Sort Area는 PGA 영역에 할당되므로 SGA를 경유해 인덱스와 테이블을 액세스할 때보다 훨씬 빠르다. PGA는 프로세스만을 위한 독립적인 메모리 공간이어서 데이터를 읽을 때 래피 획득 과정이 없다.

1. Outer 테이블인 dept를 deptno 기준으로 정렬
2. Inner 테이블인 emp를 deptno 기준으로 전렬
3. Sort Area에 정렬된 dept 테이블을 스캔하면서 정렬된 emp 테이블과 조인

* emp 테이블이 정렬되어있기 때문에 조인에 실패하는 레코드를 만나는 순간 멈출수있다.

* Outer 테이블인 dept도 같은 순서로 정렬되어있기 때문에 매번 스캔 시작점을 탐색하지 않아도된다.

단 M:M 테이블은 쉽지않다.

특징

소트 머지 조인은 조인을 위해 실시간으로 인덱스를 생성하는 것과 다름없다.

• 양쪽 집합을 정렬한 다음 NL조인과 같은 방식으로 진행하지만 PGA 영역에 저장된 데이터를 이용하기 때문에 빠르다. --> 소트 부하만 감수하면 건건이 버퍼 캐시를 거치면서 조인하는 NL 조인보다 유리
• 조인 컬럼에 대한 인덱스 유무에 따라 크게 영향을 받지 않는다.
• 양쪽 집합을 개별적으로 읽고 나서 조인함 --> 조인 컬럼에 인덱스가 없는 상황에서 두 테이블을 독립적으로 읽어 조인 대상 집합을 줄일 수 있을때 유리
• 스캔 위주의 액세스 방식을 사용한다 (모든 처리가 스캔방식으로 이루어지지는 않음)
  • 양쪽 소스 집합에서 정렬 대상 레코드를 찾는 작업만큼은 인덱스를 이용해 Random 액세스 방식으로 처리될 수 있고, 그때 발생하는 Random 액세스량이 많다면 소트 머지 조인의 이점이 사라진다.

이때 소트 머지 사용

• First 테이블에 소트 연산을 대체할 인덱스가 있을때
  • 소트 머지 조인은 해시 조인과 마찬가지로, 한쪽 집합(Second)은 전체범위를 처리하고 다른 한쪽(First)은 일부만 읽고 멈출수있다. (240쪽)
• 조인할 first 집합이 이미 정렬돼있을때
  • First 쪽 집합이 조인 컬럼 기준으로 이미 정렬된 상태일 수 있다. group by, order by, distinct 연산등을 먼저 수행한 경우인데, 그때는 조인을 위해 다시 정렬하지 않아도 되므로 소트 머지 조인이 유리하다. 여기서도 First 집합이 정렬돼있을때만 소트 연산이 생략되며, Second 집합은 설사 정렬돼있더다도 Sort Join 오퍼레이션을 수행 (244쪽)
• 조인 조건식이 등치 조건이 아닐때

해시 조인

둘 중 작은 집합(Build Input)을 읽어 Hash Area에 해시 테이블을 생성하고 반대쪽 큰 집합(Probe Input)을 읽어 해시 테이블을 탐색하면서 조인

• 해시 테이블을 생성할 때 해시 함수를 사용한다. 즉, 해시 함수에서 리턴받은 버킷 주소로 찾아가 해시 체인에 엔트리를 연결한다.
• 해시 테이블을 탐색할 때도 해시 함수를 사용한다. 해시 함수에서 리턴받은 버킷 주소로 찾아가 해시 체인을 스캔하면서 데이터를 찾음
• NL조인처럼 조인 관점에서 발생하는 랜덤 액세스 부하가 없음 소트 머지처럼 정렬해야하는 부담이 없지만 해시 테이블을 생성하는 비용이 있다. 즉 Build input이 작아야한다.

-> PGA 메모리에 할당되는 Hash Area에 담길정도로 충분히 작아야한다. (In-Memory 해시 조인), 만약 Build Input이 Hash Area 크기를 초과한다면 디스크에 썼다가 다시 읽어들이는 과정을 거치기 때문에 성능이 많이 저하한다.

또한 해시 키 값으로 사용되는 컬럼에 중복값이 거의 없어야 효과적이다.

해시 조인은 해시 테이블이 PGA 영역에 있기때문에 래치 획득과정이 필요없어 빠르다.

힌트를 이용한 조인 순서 및 Build Input 조정

swap_join_inputs 힌트를 사용하거나 ordered, leading 힌트로 조정 (254쪽 ~)

Build Input이 Hash Area를 초과할때 처리 방식

• Grace 해시 조인 : 분할 정복 방식
  • 파티션 단계 : 조인되는 양쪽 집합 모두 조인 컬럼에 해시 함수를 적용하고 반환된 해시 값에 따라 동적으로 파티셔닝을 실시한다. 독립적으로 처리할 수 있는 여러 개의 작은 서브 집합으로 분할함으로써 파티션 짝을 생성한다. 파티션 단계에서 양쪽 집합을 모두 읽어 디스크 상의 Temp 공간에 일단 저장해야하므로 In-memory 해시 조인보다 성능이 크게 떨어지게 된다.
  • 조인 단계 : 각 파티션 짝에 대해 하나씩 조인을 수행한다. 각가에 대한 Build Input과 Probe Input은 독립적으로 결정된다. 파티션하기 전 어느 쪽이 작은 테이블이었는지 상관없이 각 파티션 짝별로 작은 쪽 파티션을 Build Input으로 선택해 해시 테이블을 생성한다. 해시테이블이 생성되고 나면 반대 쪽 파티션 로우를 하나씩 읽으면서 해시 테이블을 탐색하며, 모든 파티션 짝에 대한 처리가 완료될때까지 반복
• Hybrid 해시조인 : 부하가 심한 Grace 해시 조인을 대신한 방식. 조인에 성공할 가능성이 없는 대상 집합까지 일단 디스크에 모두 쓰고 나중에 디스크부터 다시 읽어 조인해야 하기 때문에 Grace는 부하가 심하다.
• Recursive 해시 조인
• 비트-벡터 필터링

Build Input 해시 키 값에 중복이 많을때 비효율

260쪽 확인

해시 조인 사용기준

• 조인 컬럼에 적당한 인덱스가 없어 NL 조인이 비효율적일때
• 조인 컬럼에 인덱스가 있더라도 NL 조인 드라이빙 집합에서 Inner 쪽 집합으로의 조인 엑세스량이 많아 Random 액세스 부하가 심할때
• 소트 머지 조인하기에 두 테이블이 너무 커 소트 부하가 심할때
• 수행빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할때

수행빈도가 낮고, 쿼리 수행 시간이 오래걸리는, 대용량 테이블을 조인할때

이때 사용한다 

• 한 쪽 테이블이 Hash Area에 담길 정도로 충분히 작아야함
• Build Input 해시 키 컬럼에 중복 값이 거의 없어야함

조인 순서의 중요성

266쪽

Outer 조인

Outer NL 조인

Outer 조인할때 방향이 한쪽으로 고정되며. Outer 기호(+)가 붙지 않은 테이블은 항상 드라이빙 테이블로 선택된다. leading 힌트를 이용해 순서를 바꿔보려고해도 안된다.

조인 순서때문에 성능이 나빠지지 않게 하려면 불필요한 Outer 조인이 발생하지 않게 해야한다.

272쪽 확인

Outer 소트 머지 조인

Outer 해시 조인

1. Outer 집합인 dept 테이블을 해시 테이블로 빌드한다.
2. Inner 집합인 emp 테이블을 읽으면서 해시 테이블을 탐색한다.
3. 조인에 성공한 레코드는 곧바로 결과집합에 삽입하고, 조인에 성공했음을 해시 엔트리에 표시
4. Probe 단계가 끝나면 Inner 조인과 동일한 결과집합이 만들어진 상태. 이제 조인에 실패했던 레코드를 결과집합에 포함시켜야하므로 해시 테이블을 스캔하면서 체크가 없는 dept 엔트리를 결과집합에 삽입

276쪽 확인 추가 ~ 284

스칼라 서브쿼리를 이용한 조인

스칼라 서브쿼리

조인을 내포한 DML 튜닝

고급 조인 테크닉

ㅊ

IOT, 클러스터 테이블 활용

IOT

Random 액세스가 발생하지 않도록 테이블을 아예 인덱스 구조로 생성하면 어떨까 -> 그게 IOT (Index-Organized Table)

IOT는 모든 행 데이터를 리프 블록에 저장한다. 즉 인덱스 리프 블록이 곧 데이터 블록이다.

create table index_org_t (a number primary key, b varchar(10) )
organization index ;

organization heap --> 우리가 일반적으로 사용하는 테이블 구조 (힙 구조 테이블) 얜 생략해도 됨

클러스터형 인덱스와 비슷한 개념이지만 오라클 IOT는 PK 컬럼 순으로만 정렬할 수 있다

• 장점
  • IOT는 인위적으로 클러스터링 팩터를 좋게 만드는 방법이다. 같은 값을 가진 레코드들이 100% 정렬된 상태로 모여있기 때문에 Sequential 방식으로 데이터를 액세스할 수 있으므로 넓은 범위를 액세스할 때 유리
  • PK 컬럼 기준으로 데이터가 모여있더라도 선행 컬럼이 '=' 조건이 아니면 조회 대상 레코드들이 서로 흩어져 많은 스캔을 유발하지만, 적어도 테이블 Random 액세스는 발생하지않아 빠른 성능
  • PK 인덱스를 위한 별도의 세그먼트를 생성하지 않아도 되어 저장공간 절약
• 단점
  • 데이터 입력 시 성능이 느림
    • 똑같이 PK 인덱스를 두고 비교해보면 차이가 별로 없음 -> 이때 성능차이가 난다면? 인덱스 분할 발생량 차이 때문 IOT는 인덱스 구조이므로 중간에 꽉 찬 블록에 새로운 값을 입력할 일이 종종 생기고 그럴 때 인덱스 분할이 발생, PK 이외에 많은 컬럼을 갖는다면 리프 블록에 저장해야할 데이터량이 늘어나 그만큼 분할 빈도 높아짐. -> 컬럼 수가 그렇게 많은 테이블이라면 인덱스 스캔 효율때문에 IOT로는 부적합
    • Direct Path Insert 작동하지 않음 (그래서 느림) (* 기존의 버퍼 캐시(Buffer Cache)를 거치지 않고, 바로 데이터 파일(Data File)에 기록하는 방식)

그래서 IOT 언제 쓰는데

•  크기가 작고 NL 조인으로 반복 룩업하는 테이블 (코드성 테이블 : 자주 사용되는 코드 값(코드 + 설명)을 저장하는 테이블 [국가 코드, 직급 코드, 상태 코드)
  • NL 조인에서 Inner 쪽 룩업 테이블로서 액세스되는 동안 건건이 인덱스와 테이블 블록을 다 읽으면 비효율적이므로, 그런 테이블을 IOT로 구성하면 적어도 테이블은 반복 액세스하지 않아도 된다. (하지만 인덱스 높이가 증가한다면 역효과 날수있음)
• 폭이 좁고 긴 테이블 (M:M 관계를 해소하기 위한 Association 테이블 101쪽참고)
• 넓은 범위를 주로 검색하는 테이블
  • Between, Like 조건으로 넓은 범위를 검색하는 테이블
  • 102쪽 그림처럼 통계성 테이블은 일반속성은 몇개 되지않고 PK 속성이 많다. -> 분석 관점과 액세스 경로가 아주 다양한데, 이를 위해 B*TRee 결합 인덱스를 계속 추가해 나가는 것은 저장공간이나 DML 부하 측면에서 문제가 많다. -> PK 인덱스를 위한 별도 공간이 필요없는 IOT가 효율적이고 Random 액세스 발생안해서 좋음
  • * '='조건으로 항상 사용되는 컬럼 한두 개를 선두에 두고 이어서 일자 컬럼이 오도록 컬럼 구성하는 것이 효과적
• 데이터 입력과 조회 패턴이 서로 다른 테이블
  • 즉 클러스터링 팩터가 매우 안좋은 테이블일때

여러 비트맵 인덱스로 Bit-Wise 오퍼레이션을 수행한 결과 테이블 액세스량이 획기적으로 줄어드는 경우가 아니라면 (Random 액세스 발생량이 많다면) 비트맵 인덱스는 성능 개선에 도움이 안된다.

Partitoned IOT

초대용량 데이터를 읽을때 인덱스보단 Full Scan 때리면 성능이 좋기야한다만 다른 종목 거래 데이터까지 모두 읽은 비효율발생. 초대용량 테이블을 단일 IOT로 구성하는 것은 메우 부담스러움

--> 이럴때 Partitioned IOT

Overflow 영역

PK이외 컬럼이 많은 테이블일수록 IOT 구성하기 부적합 -> 인덱스 분할에 의한 DML 부하, 검색을 위한 스캔량 증가

그런데도 IOT를 꼭 써야겠다면 -> Overflow 기능

• OVERFLOW TABLESPACE : Overflow 세그먼트가 저장될 테이블스페이스를 저장
• PCTTHRESHOLD : 디폴트 값은 50, 예를들어 60이면 블록크기의 60%를 초과하기 직전 컬럼까지만 인덱스 블록에 저장하고 그 뒤쪽 컬럼은 모두 Overflow 세그먼트에 저장. 로우 전체 크기가 지정된 비율 크기보다 작다면 모두 인덱스 블록에 저장. 테이블 생성하는 시점에 모든 컬럼의 데이터 타입 Max 길이를 합산한 크기가 이 비율 크기보다 작다면 Overflow 세그먼트는 불필요하지만 초과한다면 오라클은 Overflow Tablespace 옵션을 반드시 지정하도록 강제하는 에러 던짐
• INCLUDING : 여기에 지정된 컬럼까지만 인덱스 블록에 저장하고 나머지는 무조건 Overflow 세그먼트에 저장

오라클은 Pctthreshold 또는 Including 둘 중 하나를 만족하는 컬럼을 Overflow 영역에 저장. 즉 Including 이전에 위치한 컬럼이더라도 Pctthreshold에 지정된 비율 크기를 초과한다면 Overflow 영역에 저장한다. 반대여도 마찬가지

Overflow 영역을 읽을 때도 건건이 Random 액세스가 발생한다. 따라서 Overflow 세그먼트에 저장된 컬럼 중 일부를 자주 액세스해야하는 상황이면 IOT 액세스 효율 저하. (Pctthreshold를 신중하게 선택, Incluing에 어떤 컬럼을 지정하는가가 중요)

* Overflow 영역에도 버퍼 Pinning 효과가 나타나기 때문에 연속적으로 같은 Overflow 블록을 읽을때 랜덤 블록 I/O 최소화

Secondary 인덱스

IOT는 secondary 인덱스 추가 가능성이 크기 않을 때만 선택하는 것이 바람직함

오라클 Logical Rowid : IOT 레코드의 위치는 영구적이지 않기 때문에 오라클은 secondary 인덱스로부터 IOT 레코드를 가리킬때 물리적 주소대신 logical rowid(PK + physical guess : secondary 인덱스를 최초 생성하거나 재생성한 시점에 IOT 레코드가 위치했던 데이터 블록 주소)를 사용한다.

PCT_DIRECT_ACCESS

secondary 인덱스가 유효한 physical guess를 가진 비율을 나타내는 지표로 secondary 인덱스 탐색 효율을 결정짓는 값

• 이 값이 100% 미만이면 오라클은 바로 PK를 이용해 IOT를 탐색
• 100%일때 Physical guess를 이용. 레코드를 찾아갔을때 해당 레코드가 다른 곳으로 이동하고 없으면 PK로 다시 IOT 탐색. 그런 비율이 높으면 성능은 당연히 안좋음

인덱스를 최초 생성하거나 재생성하면 PCT_DIRECT_ACCESS 값은 100 이땐 비효율이 없다

• 휘발성이 강한 (레코드 위치가 자주 변하는) IOT의 경우 시간이 지나면서 physical guess에 의한 액세스 실패 확률이 높아져 성능이 점점 저하된다. -> 통계정보를 다시 수집해서 PCT_DIRECT_ACCESS가 피지컬개스를 반영해주도록 한다. (통계정보를 재수집한 이후로는 Direct 액세스로 전환된다. PK를 이용한다는 것)
  • 인덱스를 Rebuild하거나 update block references 옵션을 이용해 피지컬개스를 주기적으로 갱신해준다면 가장 효과적
  • 과정은 111쪽을 확인하자

비휘발성 IOT에 대한 Secondary 인덱스 튜닝 방안

• Direct 액세스 성공률이 높으므로 PCT_DIRECT_ACCESS 값이 100을 가리키도록 유지하는 것이 중요 (주기적으로 피지컬 개스를 갱신)
• Direct 액세스 성공률이 100%에 가깝다면 일반 테이블을 인덱스 rowid로 액세스할때와 같은 성능을 보이므로 secondary 인덱스를 추가하는 데 대한 부담이 적다

휘발성 IOT에 대한 Secondary 인덱스 튜닝 방안

• secondary 인덱스 크기가 작을때 -> 주기적으로 피지컬 개스를 정확한 값으로 갱신
• secondary 인덱스 크기가 클때 (피지컬 개스 갱신이 힘듬) -> 아예 피지컬 개스를 사용못하도록 PCT_DIRECT_ACCESS 값을 100미만으로 떨어뜨리는것. 인덱스 분할이 어느정도 발생한 상태에서 통계정보 수집 -> rowid로 액세스할때보다 느려지겠지만 선택도가 매우 낮은 secondary 인덱스 위주로 구성해주면 큰 비효율이 없다

Right-Growing IOT (맨우측 블록에만 값이 입력됨) 에서 PCT_DIRECT_ACCESS가 100미만으로 떨어지는 이유

인덱스 높이가 분할될때마다 생기는 현상

블록이 꽉차면 기존 100번 블록을 그대로둔채 101,102 블록이 새로생길것같지만 실제로는 100번 블록 레코드를 새로 할당한 101번 블록에 모두 복제하고 100번은 루트 레벨로 올라가고 새로 추가되는 값들은 102번 리프 블록에 입력되어 100번 블록을 가리키던 secondary 인덱스 피지컬 개스가 부정확해진다. 그 이후는 115쪽 참고

오라클은 왜 이따구로 개발했을까

인덱스 루트 블록은 매우 특별함. 수직적 탐색할때 항상 시작점으로 사용됨. 루트 블록 주소가 바뀌면 해당 인덱스를 참조하는 많은 실행계획이 영향을 받아 엄청난 파급효과가 생심

IOT_REDUNDANT_PKEY_ELIM

117쪽 참고

여기까지가 IOT

클러스터 테이블

인덱스 클러스터 테이블

클러스터 키 값이 같은 레코드가 한 블록에 모이도록 저장하는 구조. 한 블록에 모두 담을 수 없을때는 새로운 블록을 할당해 클러스터 체인으로 연결한다. (단 정렬해두지않음 (IOT와의 차이))

여러 테이블 레코드가 물리적으로 같이 저장될 수도있다. 여러 테이블을 서로 조인된 상태로 저장해두는 건데. 일반적으로 하나의 데이터 블록이 여러 테이블에 의해 공유될수없음

create cluster c_deptno# (deptno number(2) ) index; -- 클러스터 생성

클러스터 테이블에 담기전에 인덱스 정의 반드시 (클러스터 인덱스는 데이터 검색 용도로 사용될 뿐만 아니라 데이터가 저장될 위치를 찾을때도 사용)

create index i_deptno# on cluster c_deptno# -- 클러스터 인덱스

클러스터 인덱스는 일반적으로 B*Tree 구조를 사용하지만, 해당 키 값을 저장하는 첫번째 데이터 블록만을 가리킴
- 클러스터 인덱스의 키 값은 항상 Unique
- 테이블 레코드와 1:M 대응 관계를 갖는다. (일반적인 인덱스는 테이블과 1:1 대응)
--> 이런 특성때문에 클러스터 인덱스를 스캔하면서 값을 찾을때는 Random 액세스가 값 하나당 한번씩 밖에 발생하지않고 Sequential 방식으로 스캔하여 넓은 범위 읽어도 비효율없음

인덱스 클러스터 테이블 유형

• 단일 테이블 인덱스 클러스터
• 다중 테이블 인덱스 클러스터 (120쪽 확인)

클러스터 테이블이 실무적으로 자주 활용되지 않은 이유 --> DML 부하

• 새로운 값이 입력돼 많이 느려진다면 클러스터 키를 잘못 선정한 거. 클러스터 테이블을 구성하고 인덱스 2,3개 없애면 DML부하 오히려 감속
• 수정이 자주 발생하는 컬럼은 클러스터 키로 선정하지 않는 것이 조지만 삭제 작업때문에 클러스터 테이블이 불리할 것은 없다. 전체 데이터를 지우거나 테이블을 통째로 Drop 할때는 성능 문제 생길수있음
• 전체 테이블을 지울 때는 Truncate Table 문장을 쓰는 것이 빠름. 클러스터 테이블에는 이 문장을 쓸수없음. 또한 테이블을 drop하려할때도 내부적으로 건건이 delete 수행됨. (1:M 테이블이므로)
• 그외 고려
  • Direct Path Loading을 수행할 수 없다
  • 파티셔닝 기능을 함께 적용할 수 없다. 
  • 다중 테이블 클러스터를 Full Scan할때는 다른 테이블 데이터까지 스캔하여 불리

Size 옵션

클러스터 키 하나당 레코드 개수가 많지 않을때 클러스터마다 한블록씩 통째로 할당하는 것은 낭비 -> 하나의 블록에 여러 키 값이 상주할수있는 Size 옵션

한 블록에 여러 클러스터 키가 담기더라도 하나당 가질 수 있는 최소 공간을 미리 예약하는 기능. 즉 하나의 블록에 담을 최대 클러스터 키 개수를 결정지음 (124쪽 확인, 126쪽 설명확인)

해시 클러스터 테이블

해시함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조. 클로스터 키로 데이터를 검색하고 저장할 위치를 찾을때는 해시 함수를 사용. 해시 함수가 인덱스 역할을 대신하는 것. 해싱 알고리즘을 이용해 클러스터 키 값을 데이터 블록 주소로 변환한다.

'=' 검색만 가능하다. 즉 '='조건으로만 검색되는 컬럼을 해시 키로 선정해야한다. (127쪽 확인)

물리적인 인덱스를 따로 갖지않아 그만큼 블록 I/O가 덜 발생

인덱스 스캔 효율

• Sequential 액세스 : 레코드간 논리적 또는 물리적인 순서에 따라 차례대로 읽어 나가는 방식
• Random 액세스 : 레코드간 논리적, 물리적인 순서를 따르지 않고 한건을 읽기 위해 한 블록씩 접근

--> I/O 튜닝은 Sequential 액세스의 선택도를 높인다, Random 액세스 발생량을 줄인다. 2가지가 있다.

여기서는 Sequential 액세스를 다룬다.

1. 비교 연산자 종류와 컬럼 순서에 따른 인덱스 레코드의 군집성

인덱스 레코드는 '값은 값을 갖는' 레코드들이 항상 서로 군집해있다. (등치조건)

근데 등치조건이 아닐때는 인덱스 레코드도 서로 흩어진 상태일수도있다.

여기서 결국 선행 컬럼이 모두 등치조건인 상태에서 첫번째 나타나는 범위검색조건까지만 만족하는 인덱스 레코드는 모두 연속되게 모여있지만 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다.

2. 인덱스 선행 컬럼이 등치 조건이 아닐때 발생하는 비효율

Sequential 액세스 효율은 선택도에 의해 결정된다. -> 같은 결과 건수를 내는데 얼마나 적은 레코드를 읽느냐

인덱스 Sequential 액세스에 따른 선택도는 인덱스 컬럼이 조건절에 모두 등치 조건으로 사용될때 가장 높다. 리프 블록을 스캔하면서 읽은 레코드는 하나도 필터링되지 않고 모두 테이블 액세스로 이어지기 때문에 인덱스 스캔단계에서 비효율은 전혀 없다.

• 인덱스 선행 컬럼이 조건절에 누락됨
• between, 부등호, like 같은 범위검색 조건이 사용될때

인덱스 스캔하는 단계에서 비효율 발생

135, 136쪽 확인

3. BETWEEN 조건을 IN-List로 바꾸었을 때 인덱스 스캔 효율

운영중인 시스템에서 인덱스 구성을 바꾸기는 쉽지않다.

between 조건을 아래와 같이 IN-List로 바꿔주면 좋다 137쪽확인

In-List 개수만큼 union all 브랜치가 생성되고, 각 브랜치마다 모든 컬럼을 등치조건으로 검색하지 때문에 between 조건으로 비교할때와 같은 비효율이 사라진다. Index Skip Scan 방식으로 유도해도 비슷한 효율을 얻을수있다.

In-List 항목 개수가 늘거나 줄 수 있다면, 아래처럼 NL 방식의 조인문이나 서브쿼리로 구현하면 된다.

between 조건을 IN-List 조건으로 바꿀 때 주의사항


In-List 개수가 많지 않아야한다. 개수가 많으면 between 조건때문에 리프 블록을 추가로 스캔하는 비효율보다 In-list조건때문에 브랜치 블록을 반복 탐색하는 비효율이 더 크다 (인덱스 높이가 크면 클수록 더더욱)
인덱스 스캔 과정에서 선택되는 레코드들이 서로 멀리 떨어져 있을때만 유용하다. 선택되는 레코드는 소량일때 In-List로 변환이 효과를 낸다. 많은 레코드를 스캔하는 비효율이 있을지언정 블록 I/O 측면에서는 대개 소량에 그치는 경우가 많기때문

4. Index Skip Scan을 이용한 비효율 해소

인덱스 선두 컬럼이 누락됐을때나, 부등호 between like같은 범위 검색 조건일때 유용하게 사용가능

140-142쪽확인

5. 범위검색 조건을 남용할 때 발생하는 비효율

like는 between일때와 기본원리는 같고 스캔범위에 약간의 차이가 있다.

like가 인덱스 구성 컬럼일때는 주의가 필요한다. (143 -147쪽확인)

6. 같은 컬럼에 두 개의 범위검색 조건 사용 시 주의 사항

147쪽 확인

OR-Expansion을 이용하는 방법과 주의사항

use_concat 힌트를 사용하면 union all 사용할때보다 SQL 코딩량을 줄일수있다. Or조건에 대한 expansion이 일어나면 뒤쪽 조건절이 먼저 실행되다는 특징이 있다.
주의할점은 버전에 따라 실행되는 순서가 달라진다

7. Between 과 Like 스캔 범위 비교

153쪽 확인

결국 Like보단 Between

8. 선분이력의 인덱스 스캔 효율

선분이력이랑 시작시점과 종료시점을 함께 관리하는 것 (고객의 변경이력을 관리할때 이력 시작시점만 관리하는것은 점이력)

158쪽

9. Access Pridicate 와 Filter Predicate

170쪽

10. Index Fragmentation

오라클은 B트리를 사용하므로 불균형은 생길수없다.

Index Skew

인덱스 엔트리가 왼쪽 또는 오른쪽에 치우치는 현상

Index Sparse

인덱스 블록 전반에 걸쳐 밀도가 떨어지는 현상

Index Rebuild

인덱스 설계

• 조건절에 항상 사용되거나, 자주 등장하는 컬럼들을 선정한다.
• '=' 조건으로 자주 조회되는 컬럼들을 앞쪽에 둔다
• 나머지는188쪽
  • 쿼리 수행 빈도
  • 업무상 중요도
  • 클러스터링 팩터
  • 데이터량
  • DML 부하
  • 저장공간
  • 인덱스 관리 비용
• 인덱스 생성 여부를 결정할때는 선택도가 충분히 낮은지가 중요한 판단기준 (테이블을 액세스하는 양이 일정 수준을 넘는 순간 Full Table Scan 보다 오히려 느려지기 때문) -> 선택도가 높은 인덱스는 생성해봐야 효용가치가없다
  • '=' 조건으로 항상 사용되는 컬럼들을 앞쪽에 위치시켰다면, 그 중 선택도가 낮은 것을 앞쪽에 두려는 노력은 의미가 없는 것이나 오히려 손해
  • 그외 192쪽확인

비트맵 인덱스

키 값에 중복이 없고, 키 값별로 하나의 비트맵 레코드를 갖는다. 그리고 비트맵 상의 각 비트가 하나의 테이블 레코드와 매핑된다. 비트가 1로 설정돼있으면 상응하는 테이블 레코드가 해당 키 값을 포함하고 있음을 의미

인덱스 구조

인덱스는 B*tree 구조로 되어있다.

테이블은 처음부터 끝까지 모든 레코드를 읽어야 완전한 결과집합을 얻을 수 있다 (IOT는 특정 컬럼 순으로 정렬 상태를 유지하며 값을 입력하므로 범위 스캔이 가능하다). 아무리 정렬상태가 유지되도록 해도 옵티마이저가 그것을 신뢰하여 Range Scan하지 않는다.

<--> 인덱스는 키 컬럼 순으로 정렬돼 있기 때문에 특정 위치에서 스캔을 시작해 검색 조건에 일치하지 않는 값을 만나는 순간 멈출 수 있다

• 루트, 브랜치 블록에는 하위 노드 블록을 찾아가기 위한 DBA (1800AFC) 정보를 갖는다
• 리프 블록에는 인덱스 키 컬럼과 함께 해당 테이블 레코드를 찾아가기 위한 주소정보(rowid)를 갖는다.
• LMC (LeftMost Child)는 키 값을 가지지않는다. 브랜치 첫번째 엔트리로 '키 값을 가진 첫번째 엔트리보다 작은 값'을 가진다
• LMC는 다른 엔트리와 별도 장소에 저장되어있다.

• 오라클은 인덱스 구성 컬럼이 모두 null인 레코드는 저장하지 않는다.
• 인덱스와 테이블 레코드 간에 서로 1:1 대응 관계를 갖는다. (클러스터 인덱스는 1:M관계를 갖는다)
• 브랜치에 저장된 레코드 개수는 바로 하위 레벨 블록 개수가 일치한다

-

• 리프 노드상의 인덱스 레코드와 테이블 레코드 간에는 1:1관계
• 리프 노드상의 키값과 테이블 레코드 키 값은 서로 일치
• 브랜치 노드상의 레코드 개수는 하위 레벨 블록 개수와 일치
• 브랜치 노드상의 키 값은 하위 노드가 갖는 값의 범위

인덱스 탐색

• 수직적 탐색
  • 수평적 탐색을 위한 시작 지점을 찾는 과점 (루트에서 리프블록까지 아래쪽으로 진행)
• 수평적 탐색
  • 범위 스캔 (리프 블록을 인덱스 레코드 간 논리적 순서에 따라 좌에서 우, 또는 우에서 좌 (DESC)으로 스캔

결합 인덱스 구조

만약 deptno = 20 and sal >= 2000 조건을 쿼리할때 (2번째부터 스캔)

ROWID 포맷

• ROWID : 데이터파일 번호, 블록번호, 로우 번호 같은 테이블 레코드의 물리적 위치정보를 포함 (그 값이 테이블에 저장하진 않음)
• 오브젝트 및 데이터파일 번호, 그리고 그 파일 내에서의 상대적인 블록 번호가 데이터 블록 헤더에 저장돼있다.
• 오라클 7까진 6바이트, 8부터는 10바이트 (파티션 기능 지원으로 오브젝트 번호까지 저장 -> 페타바이트 단위 데이터 저장)
  • 6 바이트 (제한 rowid 포맷)
    • 파티션되지 않은 일반 테이블에 생성한 인덱스
    • 파티션된 테이블에 생성한 로컬 파티션 인덱스
    • 00000DD5.0000.0001 (블록번호 + 로우번호 + 데이터파일번호)
      • 블록 번호 : 해당 로우가 저장된 데이터 블록 번호, 데이터파일 내에서의 상대적 번호 (테이블 스페이스x)
      • 로우 번호 : 블록내에서 각 로우에 붙여진 일련번호, 0부터 시작함
      • 데이터파일 번호 : 로우가 속한 데이터파일 번호, 데이터베이스에서 유일한 값
  • 10 바이트 (인덱스 블록 덤프를 통해 확인) (확장 rowid 포맷)
    • 파티션 테이블에 생성한 글로벌 파티션 인덱스
    • 파티션 테이블에 생성한 비파티션 인덱스
    • AAAM6PAAEAAAE2cAAA (데이터 오브젝트 6 [데이터베이스 세그먼트 식별] + 데이터 파일 3 + 블록 6 + 로우 3)
      • dbms_rowid 패키지를 사용하면 각 구성요소에 대한 정보를 쉽게 찾을수있다.

인덱스 기본원리

인덱스 선두 컬럼이 조건절에 사용되지 않으면 범위 스캔을 위한 시작점을 찾을 수 없다.

-> 옵티마이저는 인덱스 전체를 스캔하거나 테이블 전체를 스캔하는 방식을 선택한다.

인덱스 컬럼을 조건절에서 가공할 경우

• 인덱스 컬럼을 조건절에서 가공 (where substr(depno, 1, 2) = '11') (단 FBI 인덱스 정의하면 가능)
• 부정형 비교 (직업 <> '학생')
• is not null (단 단일 컬럼 인덱스가 존재한다면 그 인덱스 전체를 스캔하면서 얻은 레코드는 모두 조건을 만족한다. 오라클은 단일 컬럼 인덱스에 null 값은 저장하지 않기 때문이다)

-> 정상적인 인덱스 범위 스캔 불가, 단 인덱스 사용 자체는 가능해서 Index Full Scan 가능

• is null
  • 해당 조건으로는 인덱스 사용이 불가
  • 만약 해당 조건절이 not null 제약이 있으면 옵티마이저는 어차피 null 값이 없는걸 알고 인덱스 스캔을 통해 공집합을 리턴
  • 다른 인덱스 구성 컬럼에 is null 이외의 조건식이 하나라도 있으면 Index Range Scan 가능 (28쪽)

인덱스 컬럼의 가공 (29쪽 표 확인)

• NVL
  • not null 컬럼이면 nvl 함수를 제거
  • null 컬럼이면 Table Full Scan될 수 있음
    • 건수가 아주 많다면 Table Full Scan
    • 얼마 안된다면 함수기반 인덱스 생성 (create index 주문_x01 on 주문 (nvl(주문수량, 0) );

사례

묵시적 형변환

모든 조인 컬럼의 데이터 타입은 varchar2이다.

대상 연월에서 varchar2 컬럼에 숫자 값을 더하거나 빼는 연산을 가하면 숫자형으로 형변환이 일어난다. 묵시적 형변환 발생

숫자형, 문자형이 비교될때는 숫자형이 우선시된다.

• 묵시적 형변환은 쿼리 수행 도중 에러가 발생하거나 결과가 틀릴 수 있다는 측면이 더 중요하다.
• like로 비교할때만큼은 숫자형이 문자형으로 변환된다.

함수기반 인덱스(FBI) 활용

그닥 권장할 만한 해법은 아님

다양한 인덱스 스캔방식

Index Range Scan

인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후 리프 블록을 필요한 범위만 스캔하는 방식

• 인덱스 스캔하는 범위를 얼만큼 줄일수 있는냐
• 테이블로 액세스하는 횟수를 얼마나 줄일수있느나

가 관건이며 인덱스를 구성하는 선두 컬럼이 조건절에 사용되어야한다

얘를 잘 사용하면 sort order by나 min/max 값을 빠르게 추출할 수 있다.

Index Full Scan

수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로만 탐색 (실제로는 수직적 탐색이 먼저 발생, 첫번째 리프 블록으로 찾아갈 방법이 없음)

인덱스 선두 컬럼이 조건절에 없으면 Table Full Scan 고려, 대용량 테이블이라 부담이라면 Index Full Scan 방식 고려

SELECT * FROM emp

WHERE sal > 5000

ORDER BY ename

연봉 5000을 초과하는 사원이 극히 일부라면 Index Full Scan을 통한 필터링이 큰 효과를 준다

근데 왠만해선 인덱스 구성을 조정해야한다.

Index Unique Scan

수직적 탐색만으로 데이터를 찾는 스캔 방식

인덱스 키 컬럼을 모두 '=' 조건으로 검색

Unique 인덱스라도 범위검색 (between, 부등호, like) 쓰면 Index Range Scan 된다.

Index Skip Scan

인덱스 선두 컬럼이 조건절로 사용되지 않을때 발생, 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건에 부합하는 레코드를 포함할 '가능성이 있는' 리프 블록만 골라서 액세스하는 방식

조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용

예를 들어 선두 컬럼이 성별 (남, 여) 이고 후행 컬럼이 연봉이면 선두 컬럼의 Distinct Value 는 2개이므로 Skip scan이 효율

index_ss 옵티마이저 힌트

그림은 점프하는 것처럼 보이지만 사실 다음에 방문해야할 블록을 찾는 방법은 없다.

여기서 버퍼 Pinning이 활용된다.

브랜치 블록 버퍼를 Pinning 한 채로 리프 블록을 방문했다가 다시 브랜치 블록으로 되돌아와 다음 방문할 리프 블록을 찾는 과정 반복.

브랜치 블록들 간에도 서로 연결할 수 있는 주소정보를 갖지 않기 때문에 하나의 브랜치 블록을 모두 처리하면 다시 그 상위 노드를 재 방식하는 식으로 진행된다.

인덱스 스킵 스캔이 작동하기 위한 조건

• Distinct Value 개수가 적은 선두 컬럼이 조건절에서 누락됐고 후행 컬럼의 Distinct Value 개수가 많을 때 효과적
• A + B + C 일때 B가 누락되어도 사용가능
• A + B + C 일때 A, B모두 누락되어도 사용 가능
• 선두 컬럼이 부등호, between, like 같은 범위검색 조건일때도 사용 가능

In-List Iterator 과 비교

Index Skip Scan에 의존하지 않고 더 빠르게 결과집합 얻을 수 있음

단, 해당 컬럼의 Distinct Value가 더 늘지 않음이 보장되어야하며, In-List로 제공하는 값의 종류가 적어야한다.

Index Fast Full Scan (index_ffs)

Index Full Scan보다 빠르다. 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock Read 방식으로 스캔하기 때문

• 디스크로부터 대량의 인덱스 블록을 읽어야 하는 상황에서 큰 효과 발휘
• 인덱스 리프 노드가 갖는 연결 리스트 구조를 이용하지 않기 때문에 인덱스 키 순서대로 정렬하지 않음
• 쿼리에 사용되는 모든 컬럼이 인덱스 컬럼에 포함돼 있을때만 사용 가능
• 인덱스가 파티션 돼 있지 않더라도 병령 쿼리가 가능함 (Direct Path Read 가능)
• 버퍼 캐시 히트율이 낮아 디스크 I/O가 많이 발생할 때 유리함
• 컬럼 개수가 많아 테이블보다 인덱스 크기가 현저히 작은 상황에서 큰 효과, 데이터 건수가 아주 많으면 (parallel_index) 힌트 사용

Index Range Scan Descending (index_desc)

And-Equal, Index Combine, Index Join

2개 이상 인덱스를 함께 사용하는 방법

And-Equal

• 단일 컬럼의 Non-Unique 인덱스 여야 함과 동시에 인덱스 컬럼에 대한 조건절이 '='이어야 한다.
• Idex Combine이 생겨서 효용성이 없다

Index Combine

1. 인덱스를 스캔하면서 조건에 만족하는 rowid 목록을 얻는다 
2. rowid 목록을 가지고 비트맵 인덱스 구조를 하나씩 만든다.
3. 비트맵 인덱스에 대한 Bit-Wise 오퍼레이션을 수행한ㄷ.
4. Bit-Wise 오퍼레이션 수행한 결과가 참인 비트 값들을 rowid 값으로 환상해 최종적으로 방문할 테이블 rowid 목록을 얻는다
5. rowid를 이용해 테이블을 액세스한다.

• 데이터 분포도가 좋지 않은 두 개 이상의 인덱스를 결합해 Random 액세스량을 줄이는데 목적
• 조건절이 = 이어야할 필요가 없다 Non-Unique 인덱스일 필요도 없다.
• 조건절이 OR로 결합된 경우에도ㅗ 유용한다.
• _b_tree_bitmap_plans가 Ture일때만 작동하는데 9i부턴 디폴트가 트루임

Index Join

테이블에 속한 여러 인덱스를 이용해 테이블 액세스 없이 결과집합을 만들때 사용하는 인덱스 스캔방식

쿼리에 사용된 컬럼들이 인덱스에 모두 포함될때만 작동한다. 둘 중 한쪽만 포함되기만 하면 된다.

1. 크기가 비교적 작은 쪽 인덱스에서 키 값과 rowid를 읽어 PGA 메모리에 해시 맵 생성, 해시 키로는 rowid 사용
2. 다른쪽 인덱스를 스캔하면서 앞서 생성한 해시 맵에 값는 rowid 값을 갖는 레코드가 있는지 탐색
3. rowid끼리 조인에 성공한 레코드만 결과집합에 포함. rowid 값이 양쪽 인덱스 집합에 모두 속한다면. 그 rowid가 가리키는 테이블은 각 인덱스 컬럼에 대한 검색 조건을 모두 만족한다는 것

테이블 Random 액세스 부하

(1) TABLE ACCESS (BY INDEX ROWID)

쿼리에서 참조되는 컬럼이 인덱스에 모두 포함되는 경우가 아니라면 인덱스 스캔 이후 '테이블 Random 액세스'가 일어난다.

(TABLE ACCESS (BY INDEX ROWID))

rowid : 오브젝트 번호 + 데이터파일 번호 + 블록 번호 -> 물리적 위치 정보로 구성되지만 테이블 레코드로 직접 연결되는 구조는 아니다.

메인 메모리 DB(MMDB)는 데이터를 모두 메모리에 로드해놓고 메모리를 통해서만 I/O를 수행하는 DB이다.

잘 튜닝된 OLTP성 오라클 데이터베이스라면 버퍼 캐시 히트율이 99%이상이고 대부분 디스크를 경유하지 않을텐데도 MMDB만큼 빠르지않다. 대량의 테이블을 인덱스를 통해 액세스를 할때는 더더욱

왜?

MMDB는 인스턴스를 기동하면 디스크에 저장된 데이터를 버퍼 캐시에 로딩하고 이어서 인덱스를 실시간으로 만듬. 이때 인덱스를 오라클처럼 디스크 상의 주소정보를 담는게 아니라 메모리상의 주소정보 (Pointer)를 담는다. 그 비용은 0에 가깝다. 인덱스를 경유해 테이블을 액세스하는 비용이 오라클과 비교할수없다.

즉, 오라클은 테이블 블록이 수시로 버퍼캐시에 밀려났다가 다시 캐싱됨. 대신 디스크 상의 블록 위치 정보 (DBA)를 해시 키 값으로 삼아 해싱 알고리즘을 통해 버퍼 블록을 찾는다. 매번 위치가 달리지더라도 캐싱되는 해시버킷만큼은 고정적

-> rowid에 의한 테이블 액세스가 생각만큼 빠르지 않은 이유

1. 인덱스에서 하나의 rowid를 읽고 DBA를 해시 함수에 적용해 해시 값을 확인한다.
   1. 각 해시 체인은 래치에 의해 보호되므로 해시 값이 가리키는 해시 체인에 대한 래치를 얻으려고 시도한다. 하나의 cache buffers chains 래치가 여러 개 해시 체인을 동시에 관리한다
2. 다른 프로세스가 래치를 잡고 있으면 래치가 풀렸는데 확인하는 작업을 일정 횟수 만큼 반복
   1. 그래도 실패하면 CPU를 OS에 반환하고 잠시 대기 상태로 빠진다. 이때 latch free 대기 이벤트 발생
3. 정해진 시간동안 잠자다가 깨어나서 래치 확인 (안풀려있느면 다시 잠)
4. 래치가 해제되었다면 래치를 획득하고 해시 체인으로 진입
5. 데이터 블록이 찾아지면 래치를 해제하고 바로 읽으면 됨. 앞서 해당 블록을 액세스한 프로세스가 아직 일을 마치지 못해서 버퍼 Lock을 쥔 상태라면 다시 대기. (buffer busy waits)
6. 블록 읽기를 마치고나면 버퍼 Lock을 해제해야하므로 다시 해시 체인 래치를 얻으려고 시도 (이때 경합 발생)

해시 체인을 스캔했는데 데이터 블록을 찾지못하면

1. 디스크로부터 블록을 퍼 올리려면 우선 Free 버퍼를 할당받아야하므로 LRU 리스트를 스캔한다. 이때 cache buffers lru chain 래치를 얻어야하는데 래치 경합이 심할때는 latch free 이벤트가 발생
2. LRU 리스트를 정해진 임계치만큼 스캔했는데도 Free 상태의 버퍼를 찾지 못하면 DBWR에게 Dirty 버퍼를 디스크에 기록해 Free 버퍼를 확보해달라는 신호를 보낸다. 그런 후 해당 작업이ㅣ 끝날때 까지 잠시 대기 상태에 빠진다. (free buffer waits)
3. Free 버퍼를 할당 받은후 I/O 서브시브템에 I/O 요청을 하고 다시 대기상태에 빠진다 (db file sequential read 대기 이벤트)
4. 읽은 블록을 LRU 리스트 상에서 위치를 옮겨야 하기 때문에 cache buffers lru chain 래치를 얻어야하는데 원할하지못하면 latch free 이벤트가 나타난다.

즉 인덱스 rowid는 테이블 레코드와 물리적으로 연결돼있지 않기 때문에 인덱스를 통한 테이블 액세스는 생각보다 고비용구조다. 모든 데이터가 메모리가 캐싱돼있더라도 테이블 레코드를 찾기위해 매번 DBA를 해싱하고 래치 획득 과정을 반복해야하며, 동시 액세스가 심할때는 래치와 버퍼Lock에 대한 경합까지 발생

군집성 계수 (클러스터링 팩터)

Clustering Factor은 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미한다. CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 좋다.

clustering_factor 수치가 테이블 블록에 가까울수록 데이터가 잘 정렬돼있음을 의미. 레코드 갯수에 가까울수록 흩어져있음을 의미.

오라클이 인덱스 통계를 수집할때 clustering_factor 계산을 위한 사용하는 로직

1.  counter 변수하나 선언
2.  인덱스 리프 블록을 처음부터 끝까지 스캔하면서 인덱스 rowid로부터 블록 번호를 취한다.
3.  현재 읽고 있는 인덱스 레코드의 블록 번호가 바로 직전에 읽은 레코드의 블록 번호와 다를때마다 counter 변수 값을 1씩 증가시킨다.
4. 스캔을 완료하고서, 최종 counter 변수 값을 clustering_factor로서 인덱스 통계에 저장

• blevel : 리프 블록에 도달하기 전 읽게 될 브랜치 블록 개수
• 유효 인덱스 선택도 : 전체 인덱스 레코드 중에서 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율
• 유효 테이블 선택도 : 전체 레코드 중에서 인덱스 스캔을 완료하고서 최종적으로 테이블을 방문할 것으로 예상되는 비율

인덱스 CF가 좋다 -> 인덱스 정렬 순서와 테이블 정렬 순서가 서로 비슷하다, 인덱스를 경유해 테이블 전체 로우를 액세스할 때 읽을 것으로 예상되는 논리적인 블록 개수가 좋다.

• 물리적 I/O 횟수를 감소시키는 효과
  • 오라클에서 I/O는 블록 단위로 이루어지므로 인덱스를 통해 하나의 레코드를 읽으면 같은 블록에 속한 다른 레코드들도 함께 캐싱되는 결과를 가져오고 CF가 좋으면 그 레코드들도 가까운 시점에 읽힐 가능성이 높다. 따라서 인덱스를 스캔하면서 읽는 테이블 블록들의 캐시 히트율이 높아진다
• 논리적 I/O
  • CF가 좋으면 인덱스 통계에 나타나는 clustering_factor가 전체 테이블 블록 개수와 일치하고, 가장 안좋을때는 총 레코드 개수와 일치한다. (72쪽)

똑같은 개수의 레코드를 읽는데 CF에 따라 논리적인 블록 I/O 개수 차이나느 이유 : 인덱스를 통해 액세스되는 하나의 테이블 버퍼 블록을 Pinning하기 때문

버퍼 Pinning : 방금 액세스한 버퍼에 대한 Pin을 즉각 해제하지 않고 데이터베이스 Call 내에서 계속 유지하는 기능. 즉 인덱스 레코드가 같은 블록을 가리키면 래치 획득 과정을 생략하고 버퍼를 Pin한 상태에서 읽어 논리적인 블록 읽기 횟수가 증가하지 않음. (74쪽)

인덱스 손익분기점

인덱스 rowid에 의한 테이블 액세스는 생각보다 고비용이고 일정량이 넘어가면 테이블 전체를 스캔할때보다 느려짐. Index Range Scan에 대한 테이블 액세스가 Table Full Scan보다 느려지는 지점을 손익분기점이라고한다.

• 인덱스 rowid에 의한 테이블 액세스는 Random 액세스인 반면, Full Table Scan은 Sequential 방식으로 이루어짐
• 디스크 I/O시, 인덱스 rowid에 의한 테이블 액세스는 Single Block Read이지만 Full Table Scan은 Multiblock Read방식

이러한 요인으로 테이블이 인덱스보다 좋아지는 지점이 있다.

테이블을 Reorg함으로써 CF를 좋게 만들면 인덱스 손익분기점이 높아져 인덱스의 효용성이 증가하지만 이건 최후의 수단이어야함

부분범위처리 방식을 적극 활용

손익분기점 극복 방안

1. IOT 테이블 : 테이블을 인덱스 구조로 생성 (즉 항상 정렬됨) 인덱스 리프 블록이 곧 데이터블록이므로 Random 액세스 불필요
2. 클러스터 테이블 (Clustered Table) : 키 값이 같은 레코드는 같은 블록에 모이도록 저장, 클러스터 인덱스를 이용할 때는 테이블 Random 액세스가 키 값별로 한번 씩만 발생. 클러스터에 도달해서는 Sequential 방식으로 스캔하기 때문에 넓은 범위를 읽어도 비효율 없음
3. 파티셔닝 : 대량 범위 조건으로 자주 사용되는 컬럼 기준으로 테이블을 파티셔닝하면 Full Table Scan해도 일부 파티션만 읽고 멈추도록 할 수 있다. 클러스터는 기준 키값이 같은 레코드를 블록 단위로 모아놓지만 파티셔닝은 세그먼트 단위로 모아 놓는 차이가 있다.

테이블 Random 액세스 최소화 튜닝

(1) 인덱스 컬럼 추가

80쪽 참고

• 테이블을 액세스하는 단계에서만 265.957 (266,968 - cr, 1,011)개 I/O 발생 이는 전체 I/O의 99.6% (265.957/266.968)
• 또한 266.476(Rows) 방문하는 동안 블록 I/O가 265.957개가 발생한것은 클러스터링 팩터도 안좋음
• 가장 큰 문제는 266.476방문했지만 최종 결과집합이 1.909(테이블 액세스 단계 출력건수)개 뿐이라는 것. 테이블 방문후 사용여부에서 대부분 버려진것

• 테이블 1909번 방문했지만 모두 결과집합에 포함되어있음

(2) PK 인덱스에 컬럼 추가

emp 기준으로 Nl 조인하고 조인에 성공한 14건 중 최종 결과집합은 3건

dept_pk 인덱스에 loc 컬럼을 추가하면 불필요한 11번의 Random 액세스를 없앨 수 있지만 PK 인덱스에는 컬럼을 추가할 수없다. 그래서 PK 컬럼 + 필터조건 컬럼 형태의 Non-Unique 인덱스를 추가하는 경우가 있다. Non-Unique 인덱스를 이용해 PK 제약을 설정한다면 인덱스 개수를 줄일 수 있다.

PK 제약에는 중복 값 확인을 위한 인덱스가 반드시 필요하다. 인덱스가 없다면 값이 입력될때마다 테이블 전체를 읽어 중복값 존재 여부를 체크해야하기 때문. 근데 Unique인덱스말고 Non-Unique 인덱스로도 가능하다. 중복 여부 체크할때 one-plue 스캔이 발생하는 비효율이있지만 무시할만하다.

PK제약을 위해 사용되는 인덱스는 PK 제약 순서와 서로 일치하지 않아도 상관없음

중복 값 유무를 체크하는 용도이므로 PK 제약 컬럼들이 선두에 있기만 하면 된다.

(3) 컬럼 추가에 따른 클러스터링 팩터 변화

인덱스에 컬럼을 추가해서 테이블 Random 액세스 부하를 줄이는 효과가 있지만 인덱스 클러스터링 팩터가 나빠지는 부작용을 초래할 수도있음 (87쪽 확인)

즉 변별력이 좋지않은 컬럼 뒤에 변별력이 좋은 다른 컬럼을 추가할때는 클러스터링 팩터 변화에 주의를 기울여야한다.

(4) 인덱스만 읽고 처리

테이블 Random 액세스가 아무리 많더라도 필터 조건에 의해 버려지는 레코드가 거의 없다면 거기에 비효율은 없다

이때는 아예 테이블 액세스가 발생하지 않도록 모든 필요한 컬럼을 인덱스에 포함시키는 방법 -> Covered 인덱스 (인덱스만 읽고 처리하는 쿼리는 Covered 쿼리)

아 그냥 90쪽 확인

인덱스 컬럼이 많아지면 DML 속도가 느려지지만 사용빈도를 감안해서 결정한것이면 이득

(5) 버퍼 Pinning 효과 활용

한번 인력된 테이블 레코드는 절대 rowid가 바뀌지 않는다. 즉 레코드 이동이 발생하지 않는다. 따라서 미리 알고있던 테이블 rowid를 이용해 레코드를 조회하는 것이 가능한다. 해당 레코드가 지워지지않으면

(6) 수동으로 클러스터링 팩터 높이기

테이블에는 데이터가 무작위로 입력되지만 그것을 가리키는 인덱스는 정해진 키순으로 정렬되기에 대개 CF가 좋지 않음. 

-> 해당 인덱스를 기준으로 테이블을 재생성해서 인위적으로 CF를 좋게할수있다.

• 주의 : 인덱스가 여러 개인 상황에서 특정 인덱스를 기준으로 재정렬하면 다른 인덱스의 CF가 나빠짐 (즉 자주 사용되는 인덱스 기준으로 하기), 또한 데이터베이스의 관리 비용 증가 (테이블과 인덱스를 Rebuild하는 부담이 적고 효과가 확실할때만 사용)

95쪽확인

1. cr (Consistent Reads, 논리적 읽기)

• Undo 정보를 적용한 블록을 읽는 횟수
• 일반적으로 SQL 실행 시, 현재 SCN(System Change Number)에 맞춰 데이터 일관성을 유지해야 해.
• 변경된 블록이 있다면, Undo 정보를 적용하여 과거 시점 데이터를 조회.
• 캐시에서 블록을 읽는 경우에도 cr 값이 증가함.
• cr이 높으면 읽기 작업이 많고, Undo 블록을 자주 참조하는 상황.

📌 예시)
SELECT 쿼리를 실행할 때, 이미 버퍼 캐시에 있는 블록을 읽으면 cr 값이 증가함.

2. pr (Physical Reads, 물리적 읽기)

• 디스크에서 블록을 읽은 횟수
• 데이터가 버퍼 캐시에 없으면 디스크에서 직접 블록을 읽음.
• pr이 높으면 버퍼 캐시 히트율이 낮아서, 성능이 저하될 가능성이 높음.

📌 예시)
데이터가 캐시에 없어서, 디스크에서 읽어야 하면 pr 값이 증가.

3. pw (Physical Writes, 물리적 쓰기)

• 디스크에 블록을 기록한 횟수
• 변경된 블록을 DBWR(Database Writer)가 디스크로 플러시할 때 증가.
• INSERT, UPDATE, DELETE가 많거나, Checkpoint 발생 시 증가.

📌 예시)
트랜잭션이 많아서 데이터 변경이 잦으면 pw 값이 높아질 수 있음.

선택도와 카디널리티

• 선택도 : 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율
  • 선택도 = 1 / NDV (ex. 가전, 의류, 식음료, 생활용품이 100만건 있을때 가전 선택도는 1/4 = 25%)
• 카디널리티 : 전체 레코드 중에서 조건절에 의해 선택되는 레코드 개수
  • 카디널리티 = 총 로우 수 x 선택도 = 총 로우 수 / NDV (ex. 위의 예시로 카디럴리티는 25만건)

통계정보

인덱스 통계

컬럼 통계

컬럼 히스토그램

데이터 분포가 균일하지 않은 컬럼에는 선택도를 잘못 구하면 데이터 액세스 비용을 잘못 산정하게 되고, 결국 최적이 아닌 실행계획으로 이어진다. 그래서 옵티마이저는 일반적인 컬럼통계 외에 히스토그램을 추가로 활용한다.

시스템 통계 (sys.aux_stats$)

• CPU 속도
• 평균적인 Single Block I/O 속도
• 평균적인 Multiblock I/O 속도
• 평균적인 Multiblock I/O 개수
• I/O 서브시스템의 최대 처리량
• 병렬 Slave의 평균적인 처리량

옵티마이저

• 비용기반 옵티마이저 : 사용자 쿼리를 위해 후보군이 될만한 실행계획들을 도출하고, 데이터 딕셔너리에 미리 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용을 산정하고, 그중 가장 낮은 비용의 실행계획 하나를 선택
  • 데이터량, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 사용
• 규칙기반 옵티마이저 : 통계정보 활용하지 않고 단순한 규칙에만 의존 (대량 데이터를 처리하는 데 부적합)

옵티마이저에 영향을 미치는 요소

• SQL과 연산자 형태
• 인덱스, IOT, 클러스터, 파티션, MV 등 옵티마이저 팩터
• 제약 설정
• 통계정보
• 옵티마이저 힌트
• 옵티마이저 관련 파라미터

트랜잭션 동시성 제어

동시성 제어는 2가지 종류 나뉜다.

• 비관적 동시성 제어 : 사용자들이 같은 데이터를 동시에 수정할 것으로 가정한다. 한 사용자가 데이터를 읽는 시점에 Lock을 걸고 조회 또는 갱신처리가 완료될 때까지 이를 유지한다. Lock을 걸고 조회 또는 갱신처리가 완료될 때까지 이를 유지한다. Lock은 첫번째 사용자가 트랜잭션을 완료하기 전까지 다른 사용자들이 같은 데이터를 수정할수 없게 만들어서 잘못사용하면 동시성이 나빠진다 (잘사용하면 된다)
• 낙관적 동시성 제어 : 사용자들이 같은 데이터를 동시에 수정하지 않을 것으로 가정. 따라서 ㄷ이터를 읽을때 Lock을 설정하지 않는다. 하지만 데이터를 수정하고자 하는 시점에 앞서 읽은 데이터가 다른 사용자에 의해 변경되었는지 반드시 검사해야한다.

비관적 동시성 제어

비관적 동시성 제어는 자칫 시스템 동시성을 심각하게 떨어뜨릴수 있지만. FOR UPDATE에 WAIT또는 NOWAIT옵션을 함께 사용하면 Lock을 얻기 무한정 기다리지않아도된다.

낙관적 동시성 제어

SELECT 문에서 읽은 컬럼이 매우 많다면 UPDATE 문에 조건절을 일일히 기술하는 것은 귀찮

-> UPDATE 대상 테이블에 최종변경일시를 관리하는 컬럼이 있다면 굳

동시성 제어 없는 낙관적 프로그래밍

낙관성 동시성 제어를 사용하면 Lock이 유지되는 시간이 매우 짧아져 동시성을 높일수있지만 다른 사용자가 같은 데이터를 변경했는지 검사하고 그에 따라 처리 방향성을 결정하는 것은 귀찮

1. 온라인 쇼핑몰의 상품 조회/결제 처리 문제를 다루고 있음. TX1이 상품을 1,000원에 조회, TX2가 가격을 1,200원으로 바꾼 상황을 예시로 설명하고 있음
2. 이런 상황의 처리 방법은

• 상품 조회했을 때 가격으로 주문 처리하기
• 가격이 바뀌었다면 사용자한테 알려주고 어떻게 처리할지 확인하는 프로세스 만들기

1. 해결하기 어려운 점들:

• 주문하는 동안 가격이 바뀔 수 있는 가능성을 완전히 막기 힘듦
• 동시성 제어를 제대로 안 하면 고객 정보가 잘못 바뀔 수 있음
• 이런 동시성 문제로 클레임이 생길 수 있음

데이터 품질과 동시성 향상을 위한 제언

• 성능보다 데이터 품질이 더 중요하며, FOR UPDATE 사용을 신중히 고려해야 한다.
• 트랜잭션이 존재하는 환경에서는 JAVA 프로그래밍할 때 synchronized 키워드의 역할을 상기해야 한다.
• 데이터 변경 목적이라면 반드시 Lock을 걸어야 하며, FOR UPDATE를 제대로 활용하지 않는 개발자들의 문제점을 지적했다.
• Lock을 불필요하게 유지하지 않고, 트랜잭션의 원자성을 보장하는 범위 내에서 가급적 짧게 커밋해야 한다.
• 낙관적, 비관적 동시성 제어를 상황에 맞게 사용하되, 다른 트랜잭션에 의해 데이터가 변경된 경우 롤백하고 다시 시도하는 방식을 택한다.
• SQL 튜닝이 기본이며, 효율적인 인덱스 구성과 처리 성능을 높이기 위해 Array Processing과 One SQL 구현을 활용한다.

INSERT 성능 비교

INSERT, UPDATE, DELETE, MERGE 중 가장 중요하고 튜닝 요소가 많은 것은 INSERT다. 채번 방식에 따른 성능 차이가 매우 크기 때문이다. 3가지 태번 방식이 있다.

• 채번 테이블 : 각 테이블 식별자의 단일컬럼 일련번호 또는 구분 속성별 순번을 채번하기 위해 별도 테이블을 관리하는 방식. 채번 레코드를 읽어서 1을 더한 값으로 변경하고 그 값을 새로운 레코드를 입력하는 데 사용. 채번 레코드를 변경하는 과정에 자연스럽게 액세스 직렬화가 이루어지므로 두 트랜잭션이 중복 값을 채번할 가능성을 원천적으로 방지
  • 장점 
    • 범용성이 좋음
    • INSERT 과정에서 중복 레코드 발생을 대비한 예외 처리에 크게 신경쓰지 않아도 되므로 채번 함수만 잘 정의하면 편리하게 사용할 수 있다.
    • INSERT 과정에 결번을 방지할 수 있다.
    • PK가 복합컬럼일때도 사용할수있다.
  • 단점 (동시 INSERT가 아주 많은 테이블에는 그냥 사용불가)
    • 다른 채번 방식에 비해 성능이 안좋다 -> 채번 레코드를 변경하기 위한 로우 Lock 경합 때문
    • 동시 INSERT가 아주 많으면 채번 레코드 뿐만아니라 채번 테이블 블록 자체에도 경합발생 -> 서로 다른 레코드를 변경하는 프로세스끼리도 경합할 수 있다.
• 시퀸스 오브젝트 
  • 시퀀스의 가장 큰 장점은 성능이 빠르다는 점이다. 테이블과 달리 INSERT 과정에서 중복 레코드가 발생해도 별도의 예외처리가 필요 없다.
  • 시퀀스 오브젝트는 내부적으로 채번 테이블을 관리하며, 이는 SYS.SEQ$ 테이블로 조회할 수 있다.
  • Lock 메커니즘이 있어 성능 저하가 있을 수 있지만, 캐시 사이즈를 적절히 설정하면 최상의 성능을 낼 수 있다.
  • 시퀀스는 자체적으로 트랜잭션 기능을 가지고 있어 테이블처럼 읽고 변경하는 과정에서도 Lock이 작동한다.
  • 테이블별로 시퀀스를 생성하고 관리하는 부담이 있지만, 개발 시 사용이 편리하다는 장점이 있다.
    • 단점 : 기본적으로 PK 단일컬럼일 때만 사용가능하다는점. PK가 복합컬럼일때도 사용할 수는 있지만 각 레코드를 유일하게 식별해야하는 최소 컬럼으로 PK를 구성해야 한다는 최소성 요건을 위배하게 된다.
      1. 신규 데이터 입력 시 결번이 생기는 주요 원인은 두 가지다:
         • 시퀀스 채번 후 트랜잭션 롤백하는 경우
         • CACHE 옵션 설정 시퀀스가 캐시에서 밀려나는 경우
      2. 인스턴스 재기동으로 인한 결번은 피할 수 없지만, NOCACHE 옵션을 사용하면 성능 문제가 생길 수 있다.
      3. Shared Pool에 KEEP하는 방법으로 해결 가능하다:
         EXEC SYS.DBMS_SHARED_POOL.KEEP('SCOTT.MY_SEQ', 'Q');
         일련번호의 결번을 완전히 막는 것은 불가능하다. 채번 테이블이나 MAX + 1 방식을 사용해도 데이터 삭제 시 발생하는 결번은 막을 수 없다.
• MAX + 1 조회
• 대상 테이블의 최종 일련번호를 조회하고 거기에 1을 더해서 INSERT하는 방식
• SQL로는 insert into 상품거래(...) values ((select max(거래일련번호) + 1 from 상품거래), ...)와 같이 구현
  • 장점:
    • 시퀀스나 별도의 채번 테이블을 관리할 필요가 없음
    • 동시 트랜잭션에 의한 충돌이 없으면 성능이 빠름
    • PK가 복합컬럼인 경우 각 구문 속성별 순번 채번에 사용 가능
  • 단점:
    • 레코드 중복에 대비한 예외처리가 필요
    • 다중 트랜잭션에 의한 동시 채번 시 시퀀스보다 성능이 낮음
    • 로우 Lock 경합이 발생하고 재실행 횟수가 늘어남
    • 선행 트랜잭션이 롤백하지 않는 한, INSERT는 결국 실패하므로 채번과 INSERT를 다시 실행해야 함
     

• 동시 채번이 많지 않은 경우:
  • 세 가지 방식(시퀀스, 채번테이블, MAX+1) 중 아무거나 사용해도 무방
  • 관리 부담을 고려하면 MAX+1 방식이 유리

• 동시 채번이 많고 PK가 단일컬럼 일련번호라면:
  • 시퀀스 방식이 가장 효율적

• 동시 채번이 많고 PK 구문 속성에 값 종류가 많은 경우:
  • 로우 Lock 경합과 재실행 가능성이 높음
  • 시퀀스보다 MAX+1 방식이 구조적으로 유리 (PK 컬럼의 Minimality 측면)

• 동시 채번이 많고 PK 구문 속성의 값 종류가 적은 경우:
  • MAX+1 방식은 성능 문제 발생 가능
  • 순환(cycle) 옵션을 가진 시퀀스 오브젝트 활용 검토
   

스퀸스보다 좋은 솔루션

• PK 구성과 입력일시:
  • 구문 속성과 함께 순번 대신 입력일시(주문일시, 계약일시 등)를 두는 방식으로 PK 구조를 설계하면 Lock 이슈 해소 가능
  • SYSDATE나 SYSTIMESTAMP 함수만 호출하면 되어서 간단함

• 데이터 타입 선택:
  • 값의 종류가 많으면 DATE 타입
  • 값의 종류가 적으면 TIMESTAMP 타입
  • OS별 지원하는 소수점 자리수가 다름 (유닉스/리눅스 6자리, MS 윈도우 3자리)

• 주의사항:
  • 적절한 데이터 타입 선택 시 중복 가능성은 낮지만 예외처리는 필요
  • MAX+1 방식이나 시퀀스 방식 모두 예외처리 필요
  • 시퀀스 객체 재생성 후 last_number 누락 시 중복 발생 가능

• ILM 활용 중요성:
  • 데이터 삭제는 매우 중요하며 빠른 입력만큼 빠른 삭제도 중요
  • 시스템 오픈 후 10억 건 데이터가 쌓이면, 삭제한 공간을 재활용해야 함

• 입력일시의 PK 포함 의미:
  • 파티션 단위 커팅 시 PK 인덱스가 로컬 파티션이어야 함
  • 삭제 기준이 대개 입력일시 컬럼임

인덱스 블록경합

• 일반적인 인덱스 블록 경합:
  • INSERT 성능이 너무 떨어지는 문제 발생
  • 채번 테이블의 로우 Lock이나 시퀀스 Lock이 있어도 인덱스 블록 경합 발생
  • MAX + 1 방식 사용 시에도 자주 발생

• Right Growing 인덱스:
  • 인덱스에 기준으로 정렬된 상태로 값이 입력됨
  • 일련번호나 입력일시/변경일시처럼 증가하는 단일컬럼 인덱스는 맨 우측 블록에만 데이터 입력
  • 같은 블록을 갱신하는 프로세스 간 Lock 경합 발생 가능
  • RAC 환경에서 심각한 성능 저하 유발 (여러 노드가 동시에 Current 블록 하나를 서로 주고 받기 때문)

• 인덱스 블록 경합 해소 방법:
  • 인덱스를 해시 파티셔닝하는 것이 가장 일반적
  • 값이 순차적으로 증가해도 해시 함수에 따라 서로 다른 파티션에 입력되어 경합 감소
  • 인덱스를 리버스 키 인덱스로 전환하는 방법도 고려 가능