SQL 자격검정 실전문제 # 문제집 요점정리_과목 2_SQL 최적화 기본 원리

CBO(비용 기반 옵티마이저)

Cost Based Optimizer

• 테이블 및 인덱스 등의 통계 정보를 활용하여 SQL문을 실행하는데 소요될 처리시간 및 CPU, I/O 자원량 등을 계산하여 가장 효율적일 것으로 예상되는 실행계획을 선택하는 옵티마이저.
  이에 따라 인덱스가 존재하더라도 전체 테이블 스캔이 유리하다고 판단될 수 있다.
• 테이블, 인덱스, 칼럼 등 객체의 통계 정보를 사용해 실행계획을 수립.
  따라서 통계정보가 변경되면 SQL의 실행계획이 달라질 수 있다.
• CBO의 실행계획에는 단계별 예상 비용 및 건수 등이 표시된다.

 

규칙 기반 옵티마이저

• 적절한 인덱스가 존재하면 항상 인덱스를 사용하려고 한다.
• 제일 낮은 우선순위는 전체 테이블 스캔이고 높은 우선순위는 ROWID를 활용해 테이블을 액세스 하는 방법이다.
  (Oracle의 규칙 기반 옵티마이저에서 가장 우선순위가 높은 규칙은 Single row by rowid  액세스 기법이다.)

 

 

 

 

 

실행계획

SQL에서 요구한 사항을 처리하기 위한 절차와 방법

• 조인 방법, 조인 순서, 액세스 기법, 질의 처리 예상 비용, 최적화 정보 등이 표기된다.
  ※ 참고 : 실행 계획은 예상 정보이다. 실제 처리 건수는 트레이스 정보를 통해 알 수 있다.
  
• 다양한 처리방법마다 실행시간 및 성능이 다를 수 있다.
  단, 실행 계획 혹은 실행 방법이 달라진다고 해서 결과가 달라지지는 않는다.
• CBO의 실행계획에는 단계별 예상 비용 및 건수 등이 표시된다.
• Oracle의 실행계획에 나타나는 기본적인 JOIN기법으로는 Nested Loops Join, Hash Join, Sort Merge Join 등이 있다.

 

SQL 처리 흐름도(Access Flow Diagram)

실행계획을 시각화하여 표현한 것.

• 인덱스 스캔, 테이블 전체 스캔 등과 같은 액세스 기법이 표현된다.
• SQL의 내부적인 처리 절차를 시각적으로 표현해준다.
• 실행시간을 알 수는 없지만 성능적인 측면은 표현할 수 있다.

 

인덱스

• 기본 인덱스에 널 값들이 나타날 수 없다.
  (PK는 유니크하고 null이 아니어야 한다는 제약조건을 가진다.)
• 보조 인덱스는 UNIQUE인덱스가 아니라면 중복 데이터 입력이 가능하다.
• 자주 변경되는 속성은 UPDATE, DELETE성능에 좋지 않은 영향을 미치기 때문에 인덱스 후보로 적절하지 않다.
• 테이블의 전체 데이터를 읽는 경우는 인덱스가 거의 불필요하다.
  전체 데이터를 읽으려면 인덱스를 사용하지 않는 FTS를 사용.
• 인덱스는 조회만을 위한 오브젝트기 때문에 삽입 삭제 갱신의 경우 부하를 가중한다.
• 인덱스가 존재하는 상황에서 데이터를 입력하고 싶다면 모든 인덱스를 제거하고 데이터 삽입 후 다시 인덱스를 생성하자.
• 인덱스 범위 스캔은 결과가 없으면 한 건도 반환하지 않을 수 있다.
• 인덱스를 스캔하여 테이블로 데이터를 찾아가는 방식이 랜덤 액세스인데
  이 랜덤 액세스의 부하가 크기 때문에 많은 데이터를 읽을 경우엔 전체 스캔이 유리할 수도 있다.
• SQL Server의 클러스터형 인덱스는 Oracle의 IOT와 매우 유사하다.
• 인덱스는 INSERT와 DELETE작업과는 다르게 UPDATE작업에는 부하가 없을 수도 있다.

 

B-TREE 인덱스

관계형 데이터베이스의 주요 인덱스 구조

• 치 및 범위 검색에 적절한 구조
• 브랜치 블록과 리프 블록으로 구성된다.
• 브랜치 블록은 분기를 목적으로 리프블록은 인덱스를 구성하는 칼럼의 값으로 정렬된다.
• 일반적으로 OLTP 시스템 환경에서 가장 많이 사용된다.

 

CLUSTERED 인덱스

인덱스의 리프 페이지가 곧 데이터 페이지며

리프 페이지의 모든 데이터는 인덱스-키-칼럼 순으로 물리적으로 정렬되어 저장된다.

 

BITMAP 인덱스

시스템에서 사용될 질의를 시스템 구현시에 모두 알 수 없는 경우인 DW 및  AD-HOC질의 환경을 위해 설계되었고

하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조다.

 

Nested Loop Join(NL JOIN)

• 조인 칼럼에 적당한 인덱스가 있어서 자연 조인(Natural Join)이 효율적일 때 유용하다.
  자연조인(Natural Join)시 조인 액세스량이 많아 Random액세스 부하가 심하여 비효율적일 땐 Hash Join이 유용.
• Driving Table의 조인 데이터 양이 큰 영향을 주는 조인 방식이다.
• Sort Merge Join을 수행하기에 테이블이 너무 커서 Sort부하가 심할 땐 Hash Join이 유용.
• 유니트 인덱스를 활용하여 수행시간이 적게 걸리는 소량 테이블을 온라인 조회하는 경우엔 Hash Join보다유용하다.

 

Sort Merge Join(SMJ)

• 조인 칼럼에 적당한 인덱스가 없어서 NL JOIN(Nested Loops Join)이 비효율적일 때 사용할 수 있다.
• Driving Table의 개념이 중요하지 않은 조인 방식이다.
• 조인 조건의 인덱스의 유무에 영향받지 않는다.
• Not Equi Join에도 동작한다.
  
• Sort Merge Join을 하기엔 두 테이블이 너무 커서 Sort부하가 심할 때는 Hash Join이 유리.
• Hash Join과 함께 DW등의 데이터 집계 업무에서 많이 사용되는 Join 기법이다.

 

Hash Join

• EQUI JOIN에서만 동작하는 방식이다.
• 한쪽 테이블이 주 메모리의 가용 메모리에 담길 정도로 충분히 작고 해시 키 속성에 중복 값이 적을 때 효과적이다.
  즉, 행의 수가 작은 테이블을 선행 테이블로 선택하는 것이 유리하다.
• Sort Merge Join보다 일반적으로 더 우수한 성능을 보이지만 Join대상 테이블이 Join Key칼럼으로 정렬되어 있을 때는 Sort Merge Join이 더 우수한 성능을 낼 수도 있다.
  (Sort Merge Join을 수행하기에 테이블이 너무 커서 Sort부하가 심할 땐 Hash Join이 유용.)
  
• Sort Merge Join과 함께 DW등의 데이터 집계 업무에서 많이 사용되는 Join 기법이다.