개발자를 위한 인덱스 생성과 SQL 작성 노하우 : 12-14

 

- 오라클의 조인 방식

 

관계형 데이터베이스에서 쿼리는 조인(Join) 없이 단독으로 사용하는 경우가 거의 없다. 대부분의 쿼리는 여러 테이블 간의 관계를 맻는 경우이고, 오라클의 조인은 이러한 테이블 간 관계에 있어서 어떤 방식으로 접근하는가에 대한 조인이다. 오라클에서의 조인은 방식에 따라 쿼리의 비용과 성능이 달라지기 때문에 중요하다.

 

조인 방식 

1. Nested Loop Join : 순차적 루프에 의한 접근 방식, 소량 데이터 처리에 적합
2. Sort Merge Join : 정렬을 통한 접근 방식, 거의 사용하지 않음
3. Hash Join : 해시 함수를 이용한 접근 방식, 대량의 데이터 처리에 효율

 

Nested Loop Join(NL 조인)

 

보통 가장 흔하게 접할 수 있는 오라클 조인 방식으로 OLTP(Online Transaction Processing) 쿼리에서 가장 일반적이고 흔한 조인 방식이다. 소향의 데이터를 처리하거나 부분범위 처리에 적합하다. 테이블 간 조인을 순차적으로 수행하고, 테이블 간 접근 순서가 매우 중요하다. 선행 테이블의 처리 범위가 작아야 하고, 조인절의 목적지 컬럼에 반드시 인덱스가 존재해야 한다.

 

 

처리 순서

1. 고객 테이블에서 이름이 '홍길동'인 고객을 구한다(선행 테이블 결정).
2. '홍길동' 고객의 수만큼 순차적으로 주문 테이블 고객번호 컬럼으로 접근한다(순차적 접근).
3. 주문 테이블에서 주문일자가 '20220101'인 정보만 필터한다.

 

Sort Merge Join

 

보통 Hash Join으로 대체해서 사용한다. 이 조인 방식은 조인절에 인덱스가 없을 때 자주 발생하는데, 소트가 발생하므로 대상 건수가 많을수록 소트 부하가 올라가고 따라서 성능이 저하될 수 있다.

 

처리 순서

1. 고객 테이블에서 이름이 '홍길동'인 고개글 구한 후 고객번호 순으로 정렬한다(SORT).
2. 주문 테이블에서 주문일자가 '20220101'인 주문을 구한 후 고객번호 순으로 정렬한다(SORT).
3. 정렬된 고객정보와 정렬된 주문정보를 고객번호로 조인한다(MERGE).

 

Hash Join

 

Hash Join은 대량의 데이터 처리에 효율적이다. 이 조인은 메모리에 해시 테이블을 생성하고, 해시 함수를 이용해 연산 조인을 함에 따라 CPU 사용이 증가할 수 있어, 조회 빈도가 높은 온라인 프로그램에는 적합하지 않는 방식이다.

 

 

처리 순서

1. 조직 테이블에서 사업부가 '강원사업부'인 조직들을 구한 후, 조인절 컬럼인 조직코드를 해시 함수로 분류한 다음, 해시 테이블을 생성한다(해시 함수를 이용해 해시 테이블 생성).
2. 집계 테이블에서 처리년월이 '202201'인 자료를 구한 후, 조인절 컬럼인 조직코드를 해시 함수로 변환 후 해시 테이블로 순차적으로 접근한다(해시 함수를 통해 해시 테이블 탐색).

 

Hash Join에서는 작은 테이블에 먼저 접근하는 것이 성능 면에서 좋다. 해시 테이블 구성 작업에 부하가 많이 발생하기 때문이다. 작은 테이블에 접근해 해시 함수로 해시 테이블을 생성하고, 이후 큰 테이블에 접근해 해시 함수를 통해 순차적으로 해시 테이블로 접근하다. 이 방식은 대량 데이터를 처리하는 배치성 프로그램에 유용하다.

 

 

- 실행 계획

 

 

• ACCESS FULL : 고객 테이블 풀스캔(전체 접근)
• Cost=633K : 633,000 비용발생(논리적 비용 = IO + 메모리 + CPU + 네트워크 + ...)
• Card=42M : 42,000,000건(접근하는 레코드 수)
• Bytes=15G : 15,000,000,000(42,000,000 * 1 로우의 총 길이)

 

ACCESS FULL은 고객 테이블을 풀스캔한다는 의미이다. 어떠한 인덱스도 통하지 않고 테이블을 직접 접근해 전체 데이터를 읽는다는 뜻으로, 실행계획에서 이 용어가 보이면 다음 3가지 경우에 해당한다.

1. 해당 쿼리에 대한 적절한 인덱스가 존재하지 않는 경우로서 필요한 인덱스를 생성함으로써 해결 가능하다.
2. 인덱스는 존재하지만 부정확한 통계정보로 인해 인덱스를 타지 않는 경우. 최신의 통계 정보를 구성하거나 힌트절을 사용해 해결할 수 있다.
3. 테이블 풀스캔이 인덱스를 통한 랜덤 엑세스보다 유리한 경우. 데이터 조회 범위가 커서 인덱스를 사용하는 것이 별로 효용성이 없을 때이다.

 

Cost는 비용으로, 해당 쿼리가 동작됐을 때 소용되는 비용을 말한다. 비용이 클수록 오라클이 많은 일을 하고 있는 것으로 무거운 쿼리에 해당한다. 여기서 비용은 논리적 비용을 의미한다.

 

Card(Cardinality)는 쿼리 조건에 맞는 레코드 건수를 의미한다. K는 10의 3승, M은 10의6승, G는 10의9승을 의미하는 것으로 Card=42M은 테이블의 데이터 건수가 4200만임을 뜻한다.

 

Bytes는 쿼리 실행 시 발생하는 네트워크 트레픽, 즉 I/O 발생량을 의미한다. 1로우를 구성하는 컬럼의 길이 총합을 구한 후 Card 값을 곱하면 된다.

 

실행계획 해석 순서

1. 레벨(깊이)이 다른 경우에는 안쪽 레벨부터 해석한다.
2. 레벨(깊이)이 같은 경우에는 위에서 아래로 해석한다.

 

위 예시의 경우 '①가져와서 ②SORT하여 ③보여준다' 순으로 해석할 수 있다.

 

위 예시의 경우에는 ① -> ② -> ③ -> ④ -> ⑤ -> ⑥ 순으로 해석한다.

 

옵티마이저의 실행계획과 개발자의 실행계획

 

오라클 옵티마이저의 실행계획은 통계정보(과거+현재)를 기반으로 하고, 개발자의 실행계획은 과거+현재+미래 정보를 기반으로 한다. 개발자들은 향후 데이터 증감 추이가 어떻게 될 것인지, 생성 예정 인덱스는 무엇인지, 프로그램을 누가 어떠 용도로 얼마나 빈번하게 사용하는지 등 옵티마이저보다 더 많은 정보를 얻을 수 있기 때문에 더 좋은 실행계획을 계획할 수 있다.

 

옵티마이저와 개발자의 실행계획이 일치하지 않는 경우가 존재하는데 그 이유는,

 

  첫째, 통계정보 구성이 실제 데이터를 반영하지 못하거나 없는 경우

  둘째, 적절한 인덱스가 존재하지 않거나 부적절한 경우

  셋째, 쿼리가 최적화 돼 있지 않는 경우나 잘못 사용된 경우

  넷째, 오라클 옵티마이저의 알고리즘이 완벽하지 않다는 현실적인 문제

 

이다. 실행계획이 일치하지 않는 경우에는 통계정보를 재구성하거나 필요한 인덱스 생성 및 쿼리 최적화로 해결할 수 있다. 알고리즘 기술 문제가 있을 경우에는 힌트절을 추가해 실행계획을 변경해야 한다.

 

개발자의 실행계획은 다른 개발자가 처음 보더라도 쉽게 실행계획을 유추하고 테이블 접근 순서 및 인덱스 생성 위치를 알 수 있도록 공정쿼리 작성법을 이용하는 것이 좋다.

 

- 바인드 변수와 하드 파싱

 

보통 튜닝 시 실행계획을 상수값으로 테스트하지만, 실제로 바인드 변수로 운영되는 경우에는 실행계획이 다를 수 있다. 프로그램에 바인드 변수로 돼 있다면 반드시 바인드 변수로 실행계획을 확인해야 한다.

 

SELECT * FROM 고객 WHERE 고객명 = '홍길동'      --- 상수값

SELECT * FROM 고객 WHERE 고객명 = :NAME       --- 바인드 변수

 

위 쿼리들은 동일한 결과를 조회하지만 실행계획이 다를 수 있다. 쿼리가 어떤 방식으로 운영되는지에 따라서 실행계획을 구분,확인해야 한다.

 

바인드 변수를 사용하는 이유

 

바인드 변수는 하드 파싱을 줄이기 위한 수단으로 사용한다. 오라클 옵티마이저는 상수값이 다르면 서로 다른 쿼리로 인식해 파싱을 새로 하는데, 특히 실행 횟수가 많고 컬럼의 distinct 값이 크다면, 하드 파싱이 자주 발생해 시스템 전반적으로 많은 메모리를 사용할 것이고 CPU 부하도 높을 것이다. 반면 바인드 변수를 사용하면 동일 쿼리로 인식하기 땜누에 파싱을 매번 하지 않는다.