SQLD정리 노트#3
인덱스
검색 조건에 부합하는 데이터를 효과적으로 검색할 수 있도록 돕는 기능, 인덱스키로 정렬되어 있어 조회 속도가 빠름, DML 작업 효율은 저하함.
트리기반 인덱스: DBMS에서 사용하는 가장 일반적인 인덱스, 1) 루트 블록 2) 브랜치 블록 3) 리프블록으로 구성됨.
포인터: 루트 블록과 브랜치 블록의 키 값, 하위 블록 키 값의 범위 정보
리프 블록은 1) 인덱스키 2)ROWID로 구성됨, Doubly Linked List 형태라서 양방향 탐색 가능
※ROWID: Oracle에서 데이터를 구분할 수 있는 유일한 값, 데이터를 입력하면 자동으로 생성됨, 데이터가 어떤 데이터 파일의 어느 블록에 속해 있는지 알려줌.
- 오브젝트 번호: 해당 데이터가 속하는 오브젝트 번호, 오브젝트 별로 유일한 값을가짐
- 상대 파일 번호: 테이블 스페이스 내 데이터 파일의 순번
- 블록 번호: 데이터 파일 내 데이터가 속해 있는 블록의 순번
- 데이터 번호: 데이터 블록에 데이터가 저장되어 있는 순번
클러스터형 인덱스
인덱스의 리프 페이지가 곧 데이터 페이지다. 따라서 테이블 탐색에 필요한 레코드 식별자가 리프 페이지에 없다.(인덱스 키 칼럼과 나머지 칼럼을 리프에 같이 저장, 테이블 랜덤 액세스 할 필요가 X). 클러스터형 인덱스의 리프 페이지를 탐색하면 해당 테이블의 모든 칼럼 값 바로 얻음.
리프 페이지의 모든 로우(=데이터)는 인덱스 키 칼럼 순으로 물리적으로 정렬되어 저장.
테이블 로우는 물리적으로 한 가지 순서로만 정렬될 수 있음
→클러스터형 인덱스는 테이블당 한 개만 생성할 수 있음
전체 테이블 스캔
Oracle 에서는 테이블의 고수위 마크(HWM) 아래의 모든 블록을 읽는다. 고수위 마크는 테이블에 데이터가 쓰여졌던 블록 상의 최상의 위치를 의미.
결과를 찾기 위해 꼭 필요해서 모든 블록을 읽은 것이기 때문에 이렇게 읽은 블록들은 재사용성이 떨어진다. 그래서 전체 테이블 스캔 방식으로 읽은 블록들은 메모리에서 곧 제거될 수 있도록 관리된다
옵티마이저가 연산으로써 전체 테이블 스캔 방식을 선택하는 일반적인 이유
- SQL문에 조건이 존재하지 않는 경우
- SQL문의 주어진 조건에 사용 가능한 인덱스가 존재하지 않는 경우, 또한 주어진 조건에 사용 가능한 인덱스는 존재하나, 함수를 사용하여 인덱스 칼럼을 변형한 경우에도 인덱스를 사용할 수 없다.
- 옵티마이저의 취사 선택 : 조건을 만족하는 데이터가 多, 대부분 블록 액세스 판단
- 그 밖에 병렬처리 방식으로 처리하는 경우 또는 풀테이블 스캔 힌트를 사용한 경우
NL Join
두 개의 테이블을 중첩된 반복문처럼 조인을 수행한다.
반복문 외부(처음 테이블)에 있는 테이블을 선행 테이블 또는 외부 테이블
반복문 내부(두번째 테이블)에 있는 테이블을 후행 테이블 또는 내부 테이블이라고 부름.
for 선행테이블 읽음 ( 외부 테이블 )
for 후행테이블 읽음 ( 내부 테이블 )
(선행테이블과 후행테이블 조인)
결과 행의 수가 적은 테이블을 선행 테이블로 선택한다.
→ 선행 결과값이 많을 경우 일량이 늘어날 수 있기 때문
Sort Merge Join
- 넓은 범위의 데이터를 처리할 때 주로 이용.
- 정렬 데이터가 많을 경우 성능이 떨어질 수 있다.(디스크 I/O로 인한 부하 발생)
- 비동등 조인에 대해서도 조인이 가능하다.
- 인덱스를 사용하지 않아 인덱스가 존재하지 않을 경우에 사용할 수 있다.
1. 선행 테이블에서 조건에 맞는 행을 찾는다
2. 선행 테이블의 조인 키를 기준으로 정렬 작업을 수행한다
3. 1~2번 작업을 반복 수행하여 모든 행을 찾아 정렬한다.
4. 후행테이블에서도 같은 작업을 진행한다
5. 정렬된 결과를 이용하여 조인을 수행하고 결과 값을 추출 버퍼에 저장한다
Hash Join
- NL Join의 랜덤 액세스 문제점과 Sort merge Join의 정렬 작업의 부담을 해결하기 위한 대안으로 상요한다.
- 조인 칼럼의 인덱스가 존재하지 않을 경우에도 사용할 수 있다.
- “=”로 수행하는 동등 조인만 가능하다 (해쉬 함수를 이용하기 때문에)
1. 선행테이블에서 조건에 만족하는 행을 찾음
2. 선행테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 해쉬 테이블 생성
3. 1~2번 작업을 반복하여 선행 테이블의 모든 조건에 맞는 행을 찾아 해쉬 테이블 완성
4. 후행테이블에서 조건에 만족하는 행을 찾음
5. 후행테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 해당 버킷을 찾음
6. 같은 버킷에 해당되면 조인에 성공하여 추출버퍼에 저장
7. 후행 테이블의 조건만큼 반복 수행하여 완료