테이블 및 인덱스 등의 통계 정보를 활용하여 SQL문을 실행하는데 소요될 처리시간 및 CPU, I/0 자원량 등을 계산하여 가장 효율적일 것으로 예상되는 실행계획을 선택하는 옵티마이저를 CBO(cost based optimizer)라 한다
실행계획을 통해 알 수 있는 정보
; 액세스 기법, 질의 처리 예상 비용(cost), 조인 순서
실행계획의 실행순서
; 위에서 아래로, 안에서 밖으로
실행계획은 SQL 처리를 위한 실행 절차와 방법을 표현한 것
실행계획은 조인 방법, 조인 순서, 액세스 기법 등이 표현된다
실행계획 즉, 실행 방법이 달라진다고 해서 결과가 달라지지는 않는다
CBO의 실행 계획에는 단계별 예상 비용 및 건수 등이 표시된다
SQL 처리 흐름도는 SQL문의 처리 절차를 시각적으로 표현한 것으로, 인덱스 스캔 및 전체 테이블 스캔 등의 액세스 기법을 표현할 수 있으며, 성능적인 측면도 표현할 수 있음
관계형 데이터베이스의 인덱스 INDEX
- 기본 인덱스에 널 값들이 나타날 수 없다
- 테이블의 전체 데이터를 읽는 경우는 인덱스가 거의 불필요하다
- 인덱스는 오직 조회만을 위한 오브젝트
- B트리는 관계형 데이터베이스의 주요 인덱스 구조이다
- 대량의 데이터를 삽입할 때는 모든 인덱스를 제거하고, 데이터 삽입이 끝난 후에 인덱스를 다시 생성하는 것이 좋다
인덱스 종류
- B-TREE 인덱스 : 브랜치 블록과 리프 블록으로 구성되며, 브랜치 블록은 분기를 목적으로 하고 리프블록은 인덱스를 구성하는 컬럼의 값으로 정렬된다, 일반적으로 OLTP 시스템 환경에서 가장 많이 사용됨, 일치 및 범위 검색에 적절한 구조
- CLUSTERED 인덱스 : 인덱스의 리프 페이지가 곧 데이터 페이지며, 리프 페이지의 모든 데이터는 인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장됨, ORACLE의 IOT와 매우 유사
- BITMAP 인덱스 : 시스템에서 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해서 설계되었으며, 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조
- 규칙기반 옵티마이저는 적절한 인덱스가 존재하면 항상 인덱스를 사용하려고 한다
- 인덱스 범위 스캔은 결과가 없으면 한 건도 반환하지 않을 수 있다
- 인덱스는 INSERT와 DELETE 작업과는 다르게 UPDATE 작업에는 부하가 없을 수도 있다
- 인덱스를 활용하여 데이터를 조회할 때 인덱스를 구성하는 컬럼들의 순서는 SQL 실행 성능에 중요하다
옵티마이저와 실행계획
- ORACLE의 규칙 기반 옵티마이저에서 가장 우선 순위가 높은 규칙은 Single row by rowid 액세스 기법이다
- 비용기반 옵티마이저는 테이블, 인덱스, 컬럼 등 객체의 통계정보를 사용하여 실행계획을 수립하므로 통계정보가 변경되면 SQL의 실행계획이 달라질 수 있다
- ORACLE의 실행계획에 나타나는 기본적인 Join 기법으로는 NL Join, Hash join, Sort Merge Join 등이 있다
NL Join(nested loop join)
- 조인 칼럼에 적당한 인덱스가 있어서 자연조인이 효율적일 때 유용
- driving table의 조인 데이터 양이 큰 영향을 주는 조인 방식
- 유니크 인덱스를 활용하여 수행시간이 적게 걸리는 소량 테이블을 온라인 조회하는 경유 유용
- 데이터를 집계하는 업무보다는 OLTP의 목록 처리 업무에 많이 사용
- 주로 랜덤 액세스 방식
SMJ(sort merge join)
- 조인 컬럼에 적당한 인덱스가 없어서 nl조인이 비효율적일 때 사용
- driving table의 개념이 중요하지 않은 조인 방식
- 조인조건의 인덱스의 유무에 영향 받지 않는다
- 스캔 방식으로 데이터를 읽는다
Hash join
- 조인 컬럼에 적당한 인덱스가 없어서 자연조인이 비효율적일 때
- 자연조인시 드라이빙 집합 쪽으로 액세스량이 많아 random 액세스 부하가 심할 때
- 소트 머지 조인을 하기에는 두 테이블이 너무 커서 소트 부하가 심할 때
- 인덱스를 사용하지 않아 칼럼의 인덱스가 존재하지 않을 때도 사용 가능
- '='로 수행하는 즉, 동등 조인에서만 사용 가능
- 행의 수가 작은 테이블을 선행 테이블로 사용하는 것이 성능에 유리