DataBaser.Net: 상황에따른SQL

데이터베이스에는 답이 없다. 어떤 책에는 '한 가지 문제에는 한 가지 솔루션만이 존재한다' 는 말이 나온다. 이것은 어떤 솔루션이든지 '상황'이라는 요소가 존재함으로써 그때 그때마다 답이 틀려질 수 있다는 것이다. 1 + 1 = 2 처럼 딱 답이 떨어진다는 것이 아니다. 만약 두개의 1이 모두 숫자라면 2일 것은 확실하다. 그러나 문자형 숫자라면 이야기는 틀려진다. 예를 들면 MSSQL Server 에서는 select '1' + '1' 은 11로 나온다. 그러나 오라클에서는 select '1' + '1' from dual; 는 2로 나온다. 물론 DBMS종속적이어서 그렇다. 물론 이러한 DBMS제품의 종류에 따른 결과도 상황이 낳은 것이다. 다음 두 SQL문을 보자.

--1번 쿼리
select * from svc_code Like '01%' 

--2번 쿼리
select * from substring(svc_code, 1, 2) = '01'

여러분은 어떤 쿼리가 더 효율적이라고 생각하는가? 아마도 데이터베이스를 했다 싶은 사람은 1번 쿼리가 더 효율적이라고 할 것이다. 그러나 이것은 솜이 무겁냐 쇠덩어리가 무겁냐를 묻는 것이나 똑같다. 쇠 덩어리라고 답하는 것은 이미 머리 속에서 ‘솜과 쇠 덩어리가 같은 부피일 때’ 라는 가정을 했기 때문에 그런 답이 나온 것이다. 1번 쿼리라고 답 한것도 마찬가지다 아마도 svc_code라는 컬럼에 인덱스가 잡혀있다고 생각했기 때문이라는 예상을 해본다. 뭐 여러 가지 답을 했다면 필자는 할 말이 없다. 필자가 상황을 주지도 않고 어떤 쿼리가 더 효율적인지를 물었기 때문이다. 이제 상황에 따라 어떤 쿼리가 더 효울적인지 살펴보도록 하자.

Svr_code에 인덱스가 잡혀 있다면 당연히 1번 쿼리가 유리하다는 판단을 내릴 독자들이 많을 것이다. 그러나 이것은 DBMS의 통계정보에 따라서 틀려진다. 또한 규칙기반의 옵티마이저인가 아니면 비용기반의 옵티마이저인가에 따라서도 틀려진다.

규칙기반의 옵티마이저라면 1번 쿼리가 더 유리하다고 판단한 할 것이다. 그렇다고 1번 쿼리의 성능이 좋다는 것은 아니다. 여기서 판단되어야 할 성능의 요소는 svr_code에 인덱스가 잡혀 있는가와 선택도의 문제이다. 1억건 중에 10건이 where절의 조건에 맞는다면 1번쿼리의 승리로 끝난다. 그러나 1억건 중에 1000만건이라면 인덱스를 사용할 일반적인 선택도인 전체의 10% 정도라고 해도 풀스캔이 더 유리할 것이다. 10% 정도라고 해도 옛날 말이다. 요즘은 데이터의 양이 워낙 많아서 3% ~ 5%도 인덱스를 제대로 사용할까 말까이다. 어쨌든 Index Seek와 Scan중 어떤 것이 유리한가는 상황에 따라서 틀려진다. 아마도 MSSQL Server Enterprise Edition의 경우는 ‘미리읽기’를 수행할 것이다. 이런 경우는 최소 입/출력 단위가 페이지가 아니라 Extent가 된다. 즉, 64KB 단위로 퍼올릴 것이다. 그러므로 매우 빠를 것이다.

비용기반의 옵티마이저라면 1번 쿼리로 실행계획을 세웠다 하더래도 실제적으로 어떻게 될지는 모른다. 바로 통계정보가 있냐 없냐에 따라서와 통계정보가 최신것이냐에 따라서 실행계획이 어떻게 잡힐지 모르기 때문이다. 일반적으로 svr_code에 인덱스가 잡혀있고, 통계정보가 최신 것이며, 선택도가 양호하고 매번 SQL문을 컴파일 한다고 가정하면 1번쿼리가 유리할 것이 유리할 것이다. 선택도는 아래와 같은 히스토그램에 의해서 판단된다. 아래 그림에서 보면 60 ~ 70점을 맞은 학생이 제일 많다.

전체 학생수가 48명인데 학점이란 테이블에 학생번호, 점수라는 항목이 있다고 하자. 그럼 다음과 같이 SQL문을 작성했다. 점수에 인덱스가 잡혀 있다고 가정하자.

SELECT 학생번호, 점수 FROM 학점
WHERE 점수 BETWEEN 90 AND 100

그러면 DBMS의 옵티마이저는 어떤 실행 계획을 세우겠는가? 아마도 인덱스를 사용하는 실행계획을 세울 것이다. 그러나 오라클에서 한 블록이 2KB이건 MSSQL SERVER의 한 페이지가 8KB이건 48명에 대한 정보는 하나의 최소 입/출력 단위에 모두 채워질 수 있는 상태이다. 그러므로 당연히 이 상황에서는 인덱스는 필요없이 그냥 풀스캔하면 된다. 그러나 한가지 더 생각해 보아야 할 것이 있다. 그것은 클러스터링 팩터이다. 전체가 48건이란 것은 이미 눈치 챘을 것이다. 하나의 최소 입/출력 단위마다 하나의 행만 있다면 전체 48페이지 또는 48블록을 읽어야 하는 사태가 발생할 수도 있음이다. 이런 경우라면 인덱스를 이용하는 것이 더 유리할 것이다. 만약 최소 입/출력 단위 안에 모두 저장되어 있다면 최소 입/출력 단위만 읽으면 된다. 답은 없다. 다만 상황과 상황에 따른 현상만이 존재할 뿐이다.

Contents