칼럼의 위치가 성능에 미치는 영향

이번 시간에는 논리적 설계 단계에서 물리적 설계로 넘어갈 때 고려해야 할 문제 중 하나를 짚어보려고 합니다. 일반적으로 테이블을 생성할 때 칼럼의 물리적 위치까지 신경 써서 생성하는 경우는 거의 보지 못했습니다. 그러나 테이블이 아주 많은 칼럼으로 이루어졌을 때 앞쪽 부분에 위치하느냐 아니면 뒤쪽 부분에 위치하느냐에 따라 SQL의 성능이 달라질 수 있습니다.

먼저 로우(row)가 실제로 어떻게 저장되는지 그 구조를 살펴보겠습니다. 다음과 같이 로우 헤더와 칼럼의 길이, 실제 데이터로 이루어지게 됩니다.

+----------------------------------------------------------------------------------------------------+
| 블록 헤더 | 칼럼 길이 | 데이터 | 칼럼 길이 | 데이터 | ... 칼럼 길이(n) | 데이터(n) |
+----------------------------------------------------------------------------------------------------+

데이터베이스 엔진은 로우 안에서 칼럼의 위치를 모릅니다. 예를 들어, 칼럼 3의 위치를 찾아가려면, 먼저 칼럼 1의 위치를 파악한 후 칼럼 1의 길이를 사용하여 칼럼 2의 위치를 찾고, 다시 한번 칼럼의 2의 길이를 이용하여 마지막으로 칼럼 3의 위치를 찾는 식으로 작동하게 됩니다. 즉, 고정된 offset 정보를 가지고 있는 것이 아니라 칼럼마다 길이가 다르므로 ‘칼럼 길이’ 정보를 이용하여 매번 위치를 계산해야 합니다. 따라서 앞쪽에 위치한 칼럼일수록 위치를 빨리 찾을 수 있으므로 SQL의 성능 또한 우수할 것이라고 짐작할 수 있습니다. 물론 테이블을 이루는 칼럼의 개수가 아주 적다면 큰 문제가 안될 수도 있습니다.

다음과 같이 테스트를 해보겠습니다.

1. 먼저 250개의 칼럼으로 이루어진 테이블을 생성합니다.

DECLARE
l_sql VARCHAR2 (4000);
BEGIN
l_sql := 'CREATE TABLE my_t ('; FOR i IN 1 .. 249
LOOP
l_sql := l_sql || 'n' || i || ' NUMBER,';
END LOOP; l_sql := l_sql || ' n250 NUMBER)'; DBMS_OUTPUT.put_line (l_sql); EXECUTE IMMEDIATE l_sql;
END;
/

2. 다음과 같이 1만건의 테스트 데이터를 INSERT 합니다.

DECLARE
l_sql VARCHAR2 (4000);
BEGIN
l_sql := 'INSERT INTO my_t SELECT '; FOR i IN 1 .. 249
LOOP
l_sql := l_sql || '0,';
END LOOP; l_sql := l_sql || '0 FROM dual CONNECT BY level < = 10000'; EXECUTE IMMEDIATE l_sql; COMMIT;
END;
/

3. 위 테이블의 각 칼럼에 대해 count 쿼리를 수행하여 경과 시간을 측정합니다. 각 count 쿼리는 칼럼 1~250에 대해 1000번씩 반복 수행하였습니다.

DECLARE
l_dummy PLS_INTEGER;
l_start PLS_INTEGER;
l_stop PLS_INTEGER;
l_sql VARCHAR2(100);
BEGIN

-- 칼럼 n1~n250까지 각 칼럼 별로 1000번씩 반복 수행하여 걸린 시간 측정
FOR i IN 1..250
LOOP
l_sql := 'SELECT count(n' || i || ') FROM my_t';
l_start := dbms_utility.get_time;
FOR j IN 1..1000
LOOP
EXECUTE IMMEDIATE l_sql INTO l_dummy;
END LOOP;
l_stop := dbms_utility.get_time;
dbms_output.put_line((l_stop-l_start)/100);
END LOOP;
END;
/

수행 결과를 보면 뒤쪽에 있는 칼럼일수록 비례해서 시간이 오래 걸린다는 사실을 알 수 있습니다.

결론

위의 결과를 보면 다수의 칼럼으로 구성된 테이블 경우 가장 빈번하게 액세스되는 칼럼일수록 앞쪽에 두는 것이 좋다는 사실을 알 수 있습니다. 참고로 NULL 허용 칼럼들을 따로 뒤쪽에 두는 것이 좋습니다. 왜냐하면 NULL값이 들어올 경우 물리적으로 저장되지 않기 때문에 평균 로우의 크기(ave_row_len)가 줄어드는 효과가 있습니다.

출처 : 한국데이터베이스진흥원

제공 : 데이터 전문가 지식포털 DBguide.net