[바미] 새로 쓴, 대용량 데이터 베이스 솔루션 Vol.1 - 4장 인덱스 수립 전략

새롭게 안 사실들 

인덱스의 선정 기준들

실제 사용하는 조인 쿼리를 분석해서 필요 인덱스만 생성했었는데 각 테이블 마다 그 기준점이 명확하게 있다는 점을 처음 알게 되었다.

공부한 내용들

4장 인덱스 수립 전략

 

아무리 뛰어난 전략이라 해도, 이를 실행할 수 있는 무기가 제공되지 않는다면 제 능력을 발휘할 수 없다. 하지만 능력을 갖춘 사람에게 최적의 도구가 주어진다면 그 잠재력을 극대화할 수 있다. 실행계획에서의 최적화란 바로 주어진 요소들로부터 가장 이상적인 실행 경로를 찾는 과정이며, 논리적으로 불가능한 것을 가능하게 만들 수도 있다.

 

우리는 옵티마이저가 최적의 경로를 선택할 수 있도록 그에 필요한 경로들을 제시해야 하며, 이미 제외된 대상에서는 선택이 불가능하다는 점을 이해해야 한다. 어떤 실행계획이 좋은지는 그 계획이 경쟁 속에서 얼마나 우수한 선택지를 제공할 수 있는지가 관건이며, 1등과 2등의 차이는 아주 작지만 결정적인 차이를 만들 수 있다.

 

특히 인덱스를 수립할 때에는 단순히 하나의 액세스 경로만을 만족시키는 것이 아니라, 가능한 한 다양한 형태의 액세스를 만족시킬 수 있어야 한다. 최소한의 인덱스로도 최대의 실행 가능성을 확보할 수 있도록 전략을 구성하는 것이 핵심이다.

 

이는 단순한 원리만이 아니라, 실행계획을 해석하고 인덱스의 특성, 사용 빈도, 시스템 환경 등을 종합적으로 고려해 실제 업무에 맞는 전략을 설계해야 한다는 것을 의미한다. 결과적으로는 가장 적은 인덱스로 가장 많은 액세스를 유도해낼 수 있어야 하며, 그것이 바로 좋은 전략이다.

4.1 인덱스의 선정 기준

옵티마이저가 최적화를 수행할 때 가장 먼저 고려하는 것은 액세스 형태의 논리적 조건이다. 인덱스 구조와 조건절에 사용된 컬럼은 이 액세스 형태의 선택에 직접적인 영향을 미친다. 새로운 인덱스가 생성되었다는 것은 옵티마이저가 이를 최적 경로로 사용할 수 있는 후보를 하나 더 확보한 셈이다.

 

하지만 이는 단순히 인덱스를 추가하는 데서 끝나지 않고, 실제로 어떤 컬럼이 어떤 쿼리에 자주 사용되며 얼마나 효율적으로 쓰일 수 있는지를 분석하는 것이 중요하다. 인덱스 구조는 쿼리의 유형과 빈도에 따라 상대적으로 유리하거나 불리할 수 있기 때문이다.

 

이러한 특성은 시스템의 운영 유형(예: OLTP, OLAP 등)과도 밀접한 관련이 있으며, 구성된 인덱스가 효율을 높이기보다는 오히려 문제를 유발할 수도 있음을 경계해야 한다. 실제로 인덱스가 많다고 해서 반드시 좋은 것도 아니며, 오히려 구조상의 문제로 인해 옵티마이저가 제대로 기능을 발휘하지 못하는 경우도 존재한다.

 

따라서 인덱스는 가능한 최소 개수로 구성하되, 최대한 다양한 액세스 경로를 만족시킬 수 있어야 하며, 그 판단은 실측 데이터와 쿼리 패턴에 대한 분석을 통해 이뤄져야 한다. 가능한 경우의 수를 충분히 고려한 뒤, 모든 액세스 방식에서 최적의 실행계획을 도출할 수 있도록 설계하는 것이 핵심이다.

 

이러한 전략은 단순한 추측이 아니라, 액세스 빈도, 조건절 컬럼의 특성, 처리 범위, 분포도, 테이블 크기, 쿼리 유형 등 다양한 요소들을 복합적으로 분석한 결과로 도출되어야 한다.

4.1.1. 테이블 형태별 적용 기준

가) 적은 데이터를 가진 소형 테이블

일반적으로 한 번의 I/O로 전체 데이터를 처리할 수 있는 크기이며, 인덱스를 생성하지 않아도 성능 차이가 크지 않다. 하지만 그렇다고 무조건 인덱스를 생략하는 것은 바람직하지 않다. 소형 테이블이라도 조인의 효율성 향상, 무결성 유지, 반복적인 참조가 예상되는 경우에는 인덱스가 반드시 필요하다. 특히 내측 루프에서 반복 조회되는 경우에는 인덱스가 큰 차이를 만들 수 있으며, 때로는 인덱스 일체형 테이블(IOT)로 구성하는 것도 고려할 수 있다.

나) 주로 참조되는 역할을 하는 중대형 테이블

일반적으로 Key Entity 역할을 하는 트랜잭션 성격의 테이블로, 고객 테이블처럼 주로 조인의 대상이 되는 테이블이다. 이런 테이블은 다양한 검색 조건을 만족해야 하므로 여러 개의 인덱스를 설정할 필요가 있으며, 초기에 다소 과투자처럼 보여도 장기적으로 큰 성능 개선 효과를 얻을 수 있다. 특히 이런 테이블에는 ‘PCTFREE’를 0에 가깝게 설정하여 블록을 최대한 활용하는 것이 좋고, 필요시에는 인덱스를 재생성(Rebuild)해주는 관리도 필요하다.

다) 업무의 구체적인 행위를 관리하는 중대형 테이블

시간이 지나며 데이터가 계속 누적되는 유형으로, 트랜잭션 기록이나 상세 로그 같은 테이블이 여기에 해당된다. 다양한 컬럼을 갖고 있어 인덱스를 다수 생성해야 하며, 효율적인 액세스를 위해 B-Tree 인덱스를 주로 사용하고, 경우에 따라 비트맵 인덱스를 병행 고려할 수도 있다. 이런 테이블은 분할(partition), 클러스터링 등도 함께 고려되며, 액세스 효율을 최대한으로 만족할 수 있는 최적의 인덱스 조합 전략이 중요하다. 또한 이러한 테이블에 대해서는 꾸준한 관리와 검토를 통해 인덱스 구조를 계속 점검해야 한다.

라) 저장용(Log 성) 대형 테이블

주로 로그 데이터를 저장하는 테이블로, 대량의 데이터가 지속적으로 입력된다. 다양한 액세스 경로를 가질 수 있으며, 때로는 소스(Source)로서 활용되기도 한다. 입력 시 부하를 줄이기 위해 PCTFREE 설정을 없애거나, ‘PRIMARY KEY’ 제약 조건 없이 인덱스를 구성하는 방식도 고려된다. 또한 파티션 단위로 로컬 인덱스를 ‘UNUSABLE’ 상태로 먼저 생성한 뒤, 필요한 경우에만 ‘USABLE’로 변경하여 사용하는 유연한 관리 전략이 추천된다. 이러한 테이블은 데이터 증가가 빠르기 때문에 인덱스 재생성이나 파티션 단위 관리가 필수적이다.

4.1.2 분포도와 손익분기점

인덱스를 생성하는 가장 큰 목적은 전체 테이블을 스캔하지 않고 특정 부분만 효율적으로 액세스하기 위함이다. 조건문은 단순히 검색 조건을 필터링하는 것이 아니라 실제로 처리 범위를 얼마나 줄일 수 있는지가 핵심이다. 즉, 인덱스를 어떤 컬럼으로 구성하느냐에 따라 처리 범위를 최소화할 수 있으며, 처리 효율에 결정적인 영향을 미친다.

 

이론적으로는 모든 컬럼을 조건으로 받는다면 모든 컬럼에 인덱스를 생성할 수도 있겠지만, 현실적으로는 다양한 액세스 형태를 모두 반영할 수 없기 때문에 어떤 인덱스를 만들지 신중히 선택해야 한다. 이때 고려할 중요한 요소가 컬럼의 분포도이며, 인덱스를 통해 얻을 수 있는 손익분기점이 기준이 된다.

 

일반적으로 컬럼의 분포도가 전체 테이블의 10~15%를 넘지 않을 때 인덱스의 효과가 있다고 평가된다. 이는 인덱스를 이용한 액세스가 테이블 전체를 스캔하는 것보다 유리하다는 것을 의미한다. 이유는 인덱스를 통해 액세스할 때는 인덱스에서 ROWID를 참조해 테이블의 일부만 읽어오기 때문이며, 전 범위를 스캔하지 않으므로 비용이 더 적기 때문이다.

 

하지만 이 기준은 단순한 퍼센트일 뿐이다. 어떤 경우엔 1% 미만의 분포도에서도 인덱스를 쓰는 것이 손해가 될 수 있고, 어떤 경우엔 30%를 초과하더라도 실질 처리 비용을 고려해 인덱스를 사용하는 것이 유리할 수 있다. 결국, 중요한 것은 단순한 수치보다 실제 효율적인 액세스 범위이며, 이 판단은 실제 수행 계획을 통해 결정해야 한다.

 

인덱스를 여러 개 조합해 사용하거나 적절한 결합 인덱스를 사용하면 각 컬럼의 분포도가 낮더라도 높은 효율을 낼 수 있다. 따라서 인덱스를 전략적으로 조합하고, 개별 컬럼의 분포도는 낮더라도 결합을 통해 시너지 효과를 낼 수 있는 구조를 마련하는 것이 중요하다.

 

또한 손익분기점 이상이라 하더라도 인덱스를 생성해서는 안 된다는 오해도 피해야 한다. 예컨대 손익분기점 30%인 인덱스를 사용했더니 실질 처리 시간은 0.3%만 인덱스로 처리하고 나머지는 무시됐지만, 전체적으로는 효율을 높이는 경우도 있다. 이런 경우 일부 범위만 액세스하더라도 목적을 달성할 수 있다면 인덱스를 생성할 수 있다.

 

단, 이와 같이 넓은 범위를 처리하는 인덱스는 다양한 쿼리에서 예측 불가능한 방식으로 사용될 수 있으므로 명확한 목적을 가지고, 반드시 필요한 쿼리에서만 사용되도록 힌트를 지정해 사용하는 것이 바람직하다.

4.1.3 인덱스 머지와 결합 인덱스 비교

인덱스의 효율적인 활용을 위해서는 다양한 형태의 액세스를 최소한의 인덱스로 만족시킬 수 있어야 하며, 이를 위해 두 가지 측면에서 접근할 수 있다. 첫째는 좋은 분포도를 가진 컬럼을 독립적인 인덱스로 만들어 사용하는 방법이고, 둘째는 그렇지 못한 컬럼들을 유연성은 떨어지지만 결합해서 적절히 조합하는 것이다.

 

분포도가 좋은 컬럼으로 구성된 인덱스는 어떤 쿼리에서도 다방면으로 활용되며 유용성이 크지만, 역할이 제한되는 컬럼의 경우에는 조합된 결합 인덱스를 사용하여 보완할 수밖에 없다. 조직의 역할을 강화하고자 할 때에는 분포도가 낮은 컬럼이라도 적절한 인덱스 설계로 시너지를 기대할 수 있다.

 

단일 인덱스를 선택하는 것이 최우선이며, 결합 인덱스와의 병행보다는 분포도가 좋은 단일 인덱스를 사용하는 것이 더 유리하다. 하지만 어느 한 인덱스로 모든 조건을 처리할 수 없다면, 복수의 인덱스를 병합해 사용하는 인덱스 머지(Index Merge) 기법을 사용할 수 있다. 인덱스 머지는 여러 인덱스를 스캔하여 얻은 ROWID 값을 기준으로 교차 비교 후 공통된 결과를 도출하는 방식이다.

 

예를 들어 WHERE COL1 = 'ABC' AND COL2 = 123인 경우, COL1과 COL2에 각각 인덱스가 존재한다면, 각 인덱스에서 검색한 결과를 ROWID 기준으로 병합하여 테이블 액세스를 수행할 수 있다. 이는 랜덤 액세스를 줄이고, 불필요한 블록 접근을 줄여주는 이점이 있다.

 

그러나 인덱스 머지를 사용할 경우, 각 인덱스 간 병합과정에서의 스캔 비용과 병합 성공률의 차이에 따라 오히려 비효율이 발생할 수 있다. 특히 분포도가 유사한 컬럼끼리 인덱스를 병합하면 비효율이 크며, 이 경우에는 차라리 하나의 넓은 범위를 가진 인덱스를 단독으로 사용하는 것이 낫다.

 

또한 = 연산이 아닌 LIKE, IN과 같은 조건이나 컬럼 범위가 넓은 경우에도 인덱스 머지가 최적의 선택은 아니며, 이때는 결합 인덱스(Concatenated Index)가 더 유리할 수 있다. 결합 인덱스는 여러 컬럼을 결합하여 하나의 인덱스로 만들어, 각 조건이 동시에 만족되는 로우만을 정확히 찾아내므로 병합 과정이 생략되고 속도도 빠르다.

 

실제로도 인덱스 머지는 각 인덱스에서 검색된 ROWID를 비교하며 병합하지만, 결합 인덱스는 이미 그 결과가 결합된 인덱스 내에 존재하므로 별도 병합 과정이 필요 없다. 이에 따라 효율성과 성능면에서 결합 인덱스가 더욱 뛰어난 경우가 많다.

 

단, 결합 인덱스도 컬럼 순서나 조건 충족에 따라 제 성능을 발휘하지 못할 수 있으므로, 인덱스 구성 시 반드시 조건 컬럼의 위치와 사용 용도를 고려하여 설계해야 한다.

 

결론적으로, 분포도가 좋고 단독 사용이 가능한 컬럼은 단일 인덱스를, 그렇지 않은 경우에는 결합 인덱스를, 특수한 경우에는 인덱스 머지를 사용하는 방식으로 전략적으로 인덱스를 설계하는 것이 중요하다.

4.1.4. 결합 인덱스의 특징

결합 인덱스는 다중 컬럼을 조합하여 만든 인덱스로, 일반적인 단일 인덱스보다 유연한 검색 조건을 처리할 수 있지만 몇 가지 주의할 점이 있다. 첫째, 결합 인덱스의 선두 컬럼이 조건절에 포함되지 않으면 해당 인덱스는 사용되지 않거나 INDEX SKIP SCAN과 같은 비효율적인 스캔 방식으로 동작할 수 있다. 이는 특정 액세스 패턴에는 적합할 수 있지만, 다양한 액세스 형태에서는 활용성이 떨어질 수 있음을 의미한다.

 

둘째, 결합된 컬럼 중 하나라도 조건절에서 비교 연산자(=, >, < 등)를 비순차적으로 사용하면, 특히 =이 아닌 연산자가 선행 조건으로 사용될 경우, 전체 인덱스의 효율성이 급격히 떨어지게 된다. 따라서 어떤 컬럼을 인덱스에 포함시킬지뿐 아니라 어떤 순서로 결합할지도 전략적으로 결정해야 한다.

 

이러한 특성은 인덱스 생성 시 다루는 전략의 핵심 요소로, 특히 B-Tree 인덱스를 기준으로 설명되는 내용이다. 많은 사람들이 "분포도가 좋은 컬럼은 무조건 선두에 위치해야 한다"고 생각하지만, 이와 같은 단순한 접근은 실제 효율성과는 거리가 있을 수 있다. 따라서 결합 인덱스를 설계할 때는 분포도 외에도 컬럼 간 연산자의 종류, 결합 순서, 실제 쿼리에서의 활용 형태 등을 종합적으로 고려해야 한다.

가) 분포도와 결합 순서의 상관관계

많은 사람들이 인덱스를 설계할 때 분포도가 좋은 컬럼을 결합 인덱스의 선두에 배치하는 것이 항상 최선이라고 믿는다. 하지만 실제로 결합 인덱스의 컬럼 순서가 처리 성능에 어떤 영향을 미치는지는 단순한 분포도 외에도 조건절의 연산자 종류와 실제 액세스 방식에 따라 달라진다.

 

예를 들어, 다음과 같은 SQL이 있다고 가정하자.

SELECT *
  FROM TAB1
 WHERE COL1 = 'A'
   AND COL2 = 123;

이때 결합 인덱스를 INDEX1 (COL1 + COL2) 또는 INDEX2 (COL2 + COL1)으로 생성한 경우, 아래와 같은 차이가 나타난다.

• INDEX1 (COL1 → COL2): B-Tree 인덱스 구조상 COL1 = 'A'를 기준으로 리프 블록에서 빠르게 탐색해 첫 번째 ROWID를 찾고,이후 COL2 조건을 검사한다. 이 과정은 효율적인 액세스를 제공하며, 불필요한 스캔 없이 빠르게 종료될 수 있다.
• INDEX2 (COL2 → COL1): COL2 = 123을 기준으로 먼저 탐색한 후 COL1 = 'A' 조건을 확인해야 하므로, 조건을 만족하지 않는 로우를 스캔하며 비교하는 방식이 되어 처리 효율이 다소 낮을 수 있다. 특히 랜덤 I/O 횟수가 증가할 수 있다.

그러나 중요한 점은, 모든 조건이 '=' 연산자로 주어지는 경우, 분포도가 높은 컬럼이 인덱스의 선두에 있든 후위에 있든 실제 처리량에는 큰 차이가 없다는 것이다. 이 경우에는 인덱스의 리프 블록 접근이 빠르고, ROWID를 통해 빠르게 테이블 로우에 접근해 조건 검사를 수행하기 때문이다.

 

실제 실험에서도 두 컬럼의 순서를 바꾸어 인덱스를 구성했을 때, 처리 순서만 달라질 뿐 처리량은 유사하게 나타났다. 따라서 = 연산자만 사용하는 경우에는 **분포도보다 더 중요한 요소는 '연산자의 종류'**이며, BETWEEN, LIKE, >, < 같은 비등호 연산자가 포함되거나 컬럼 중 일부만 조건절에 사용되는 경우에는 분포도가 높은 컬럼을 선두에 위치시키는 것이 성능 향상에 유리하다.

 

결론적으로, 단순히 분포도만을 기준으로 인덱스 결합 순서를 정하는 것은 위험하며, 실제 사용되는 쿼리에서의 조건절 구성과 연산자 형태까지 고려해 종합적으로 인덱스 설계를 해야 한다. 잘못된 순서는 인덱스의 활용도를 떨어뜨리고 실행계획의 효율성도 저하시킬 수 있다.

나) 이퀄(=)이 결합순서에 미치는 영향

결합 인덱스를 설계할 때 '=' 연산자가 결합 컬럼에 어떻게 사용되는지에 따라 컬럼의 결합 순서를 결정하는 것이 중요하다. 많은 경우 사람들이 무조건 분포도가 좋은 컬럼을 인덱스의 선두에 배치하는 것이 가장 효율적이라고 믿지만, 실무에서는 모든 조건이 '=' 연산자로 비교되지는 않는다.

 

실제 SQL 실행 시에는 다양한 비교 연산자들(예: LIKE, BETWEEN, <, >)이 사용되며, 이러한 연산자는 단순한 '='와는 다르게 작동한다. 때문에 '=' 외의 연산자가 사용되는 경우 인덱스 선두 컬럼에 어떤 연산자가 적용되는지에 따라 성능 차이가 크게 날 수 있다.

 

이를 실험적으로 확인하기 위해 다음 SQL 문을 두 가지 결합 인덱스 구조(INDEX1: COL1 + COL2, INDEX2: COL2 + COL1)에서 테스트하였다.

SELECT *
  FROM TAB1
 WHERE COL1 = 'A'
   AND COL2 BETWEEN 113 AND 115;

NDEX1 (COL1 → COL2)

• COL1이 '=' 조건이므로 먼저 COL1 = 'A'인 범위로 인덱스에서 탐색한다.
• 이후 COL2가 BETWEEN 113 AND 115 조건을 만족하는지 검사하면서 테이블에 접근한다.
• 인덱스는 COL1 기준으로 정렬되어 있어 COL2는 정렬되지 않았지만, COL1 조건이 '='이므로 스캔 대상이 좁아져 성능상 유리하다.
  COL2가 115보다 큰 순간 탐색을 종료하므로 효율적이다.

INDEX2 (COL2 → COL1)

• COL2가 BETWEEN 113 AND 115 조건으로 검색되며 첫 번째 ROWID를 찾는다.
  이후 COL1 = 'A'인지 조건 확인을 위해 테이블에 액세스한다.
• COL2 값이 범위 조건을 만족해도 COL1이 'A'가 아니라면 조건을 만족하지 않으므로, 테이블에 반복적으로 접근해야 하며 처리량이 늘어난다.
• 전체 조건을 충족하는 ROWID를 찾기 위해 더 많은 스캔이 필요하다.

첫 번째 컬럼이 '=' 연산자일 경우, 그 범위 안에서는 두 번째 컬럼의 정렬이 유지되기 때문에 추가적인 스캔이 최소화된다. 반면 첫 번째 컬럼이 '=' 연산자가 아니면, 두 번째 컬럼이 '='이거나 범위 조건이더라도 전체 범위를 스캔해야 하는 문제가 발생한다.

 

따라서 결합 인덱스를 설계할 때는 단순히 분포도가 좋은 컬럼을 앞세우는 것이 아니라, 사용되는 연산자의 종류와 위치, 정렬 상태를 고려해 설계하는 것이 매우 중요하다. 즉, '=' 조건이 사용되는 컬럼이 앞에 오도록 하고, 비교 연산이 자주 사용되는 컬럼이 후행하도록 구성하는 것이 실행 계획 수립과 성능 향상에 유리하다.

다) IN 연산자를  이용한 징검다리 효과

결합 인덱스에서 첫 번째 컬럼이 '=' 연산자가 아닌 LIKE, BETWEEN 등의 범위 연산자로 사용되면, 뒤에 위치한 컬럼에 '=' 조건이 있어도 인덱스를 통한 효율적인 액세스가 어렵다. 이는 전체 범위를 스캔한 후 조건을 체크해야 하기 때문이다.

 

예를 들어, COL2 + COL1 구조의 인덱스에서 COL2 BETWEEN 113 AND 115 조건과 함께 COL1 = 'A' 조건이 사용되면, 인덱스는 COL2 범위를 전부 스캔한 후에야 COL1 조건을 확인할 수밖에 없어 비효율적이다.

하지만 이와 같은 문제는 BETWEEN 대신 IN 연산자를 사용하면 해결 가능하다. IN 조건은 각각의 값에 대해 인덱스 조건을 나누어 처리하기 때문에, 옵티마이저가 INLIST ITERATOR 방식으로 처리 범위를 분리해 각 조건을 독립적으로 적용할 수 있다. 이 방식은 마치 필요한 지점만 건너뛰는 ‘징검다리’처럼 동작하며, 불필요한 범위 스캔을 피할 수 있게 도와준다.

예를 들어 COL2 BETWEEN 111 AND 112 대신 COL2 IN (111, 112)로 작성하고 COL1 = 'A' 조건을 함께 사용하면, 각각의 값에 대해 독립적인 범위 탐색이 가능해져 전체 처리 효율이 향상된다.

추가적으로, OR 조건을 활용하여 SQL을 다음과 같이 분해하는 방식도 같은 효과를 낸다.

WHERE (COL2 = 111 AND COL1 = 'A')
   OR (COL2 = 112 AND COL1 = 'A')

이처럼 IN 연산자는 단순 범위 조건을 논리적으로 분리하여 각 조건마다 최적의 인덱스 경로를 유도할 수 있게 하는 ‘징검다리 효과’를 제공하며, 인덱스 스캔 스킵이나 서브쿼리 방식보다 효율적일 수 있다.

 

단, 서브쿼리를 이용할 경우 미리 결과를 산출하지 못하면 오히려 실행 부하가 커질 수 있으므로 주의가 필요하며, 인덱스 중간 컬럼에 대한 조건이 누락되는 경우도 반드시 조심해야 한다.

라) 처리범위에 직접적인 영향을 주지 못하는 컬럼의 추가 기준

결합 인덱스를 설계할 때, 일부 컬럼들은 직접적으로 처리범위를 줄이는 데 영향을 주지 못하더라도 인덱스에 포함할 필요가 있다. 

 

예를 들어, 인덱스가 COL1 =, COL2 LIKE, COL3, COL4 BETWEEN과 같은 조건으로 구성되었을 때, COL1과 COL2는 처리범위를 줄이는 데 직접적 영향을 주지만, COL3, COL4는 앞에 위치한 컬럼이 = 조건이 아닌 경우 직접적인 영향력을 가지지 못한다.

 

하지만 이러한 컬럼들 역시 ‘존재가치’를 가진다. 이는 조건에는 직접 사용되지 않더라도 결과의 필터링 정밀도를 높여줄 수 있기 때문이다. 예를 들어, COL3가 인덱스에 포함되지 않으면 해당 조건을 확인하기 위해 테이블에 액세스해야 하지만, COL3가 인덱스에 포함되어 있다면 인덱스 레벨에서 조건 체크가 가능하여 불필요한 테이블 액세스를 줄일 수 있다.

실제 예시를 살펴보자. 다음은 인덱스 컬럼 구성에 따른 처리 방식의 차이를 보여주는 쿼리이다.

SELECT *
  FROM TAB1
 WHERE C1 = 2
   AND C3 = '400';

이때 인덱스가 C1+C2로 구성되어 있고, C3는 인덱스에 포함되어 있지 않다면 C1 = 2인 인덱스 범위를 모두 스캔한 후 테이블 액세스를 통해 C3 = 400 조건을 확인해야 한다. 반면, 인덱스가 C1+C2+C3로 구성되어 있다면 C3 조건도 인덱스 레벨에서 처리할 수 있으므로 테이블 액세스 없이 결과를 도출할 수 있다.

 

이는 아래의 인덱스 구성과 비교된다.

• INDEX1: C1 + C2 → C3 조건 확인을 위해 테이블 액세스가 필요
• INDEX2: C1 + C2 + C3 → C3 조건까지 인덱스 레벨에서 처리 가능

이처럼 비록 C3가 처리범위에 직접적으로 관여하지 않더라도 인덱스 내부에 포함됨으로써 조건 검증 기능을 수행할 수 있어 필터링에 큰 도움이 된다.

 

따라서, 인덱스에 컬럼을 추가할 때는 단순히 처리범위만을 기준으로 판단해서는 안 된다. 추가 컬럼이 조건 검사에 유용하게 작용할 수 있는지, 존재값이 명확한지, 인덱스 조인이나 실행 계획 내 활용 가능성이 있는지를 함께 고려해야 한다.

 

결론적으로, 무분별한 컬럼 추가는 인덱스 크기 증가라는 부작용을 초래할 수 있지만, 실행 계획을 정교하게 개선하고 테이블 액세스를 줄일 수 있는 전략적 추가는 매우 유효하다. 다만 이러한 컬럼 추가는 반드시 실행계획을 통해 효과를 검증해야 하며, 단순히 “넣어두면 좋겠지”라는 식의 결정은 오히려 시스템 전반에 부하를 줄 수 있으므로 신중한 설계가 필요하다.

4.1.5 결합 인덱스의 컬럼순서 결정 기준

결합 인덱스를 설계할 때는 "어떤 컬럼들을 결합할 것인가?"와 함께 "어떤 순서로 컬럼을 배치할 것인가?"도 매우 중요한 고려 사항이다. 컬럼의 순서에 따라 인덱스가 필터링할 수 있는 범위가 달라지며, 효율적인 실행계획 수립에 큰 영향을 준다. 이 절에서는 결합 인덱스의 컬럼 순서를 결정할 때 고려해야 할 5단계 기준을 설명한다.

1단계: 항상 사용되는가?

선행 컬럼은 인덱스의 시작점이 되기 때문에 조건절에서 거의 항상 사용되는 컬럼이 선두에 위치해야 한다. 마치 팀의 리더처럼, 그 컬럼이 인덱스를 사용할 수 있게 만들어 주기 때문이다. 사용 빈도가 낮은 컬럼이 선두에 위치하면 인덱스 자체가 활용되지 못하고, 인덱스 스킵 스캔 같은 보조 전략이 필요해진다. 따라서 첫 번째 기준은 쿼리 조건에 반복적으로 등장하는 컬럼을 먼저 고려하는 것이다.

2단계: 항상 '='로 사용되는가?

두 번째로 중요한 기준은 해당 컬럼이 '=' 조건으로 사용되는가이다. '=' 연산자는 인덱스 필터링에 가장 강력한 효과를 주며, 인덱스의 범위를 극단적으로 줄여줄 수 있다. '='가 아닌 BETWEEN, LIKE, >, < 등의 조건은 상대적으로 필터링 범위를 넓게 잡게 되어 인덱스 효율이 떨어질 수 있다. 따라서 자주 사용되면서 '=' 조건이 결합된 컬럼이 선두에 오는 것이 유리하다.

3단계: 어느 것이 더 좋은 분포도를 가지는가?

동일하게 '=' 조건으로 사용되는 컬럼 중에서는 분포도가 높은 컬럼이 우선이다. 분포도가 좋다는 것은 다양한 값들이 존재하여 특정 조건에서 좁은 범위를 빠르게 찾을 수 있음을 의미한다. 예를 들어, C1(=), C2(BETWEEN), C3(=)이라는 조건에서 C1이 분포도가 낮고 C3가 분포도가 높다면, C3를 더 앞쪽에 배치하는 것이 효율적이다. 다만 현실에서는 업무 특성상 컬럼 조합이 고정되는 경우도 있으므로, 예측 가능한 형태에 따라 조정이 필요하다.

4단계: 자주 사용되는 정렬의 순서는 무엇인가?

정렬이 자주 요구되는 쿼리라면, 그 정렬 조건에 맞춘 컬럼 순서를 인덱스에 반영하는 것이 유리하다. ORDER BY 절이나 GROUP BY 절에서 자주 사용되는 컬럼 순서를 인덱스에 반영하면 정렬 연산을 생략하고 인덱스 스캔으로 처리할 수 있어 성능이 향상된다. 특히 '부분범위 처리(partial range access)'가 필요한 경우, 정렬순서 일치 여부는 성능에 결정적 영향을 미친다.

5단계: 부가적으로 추가시킬 컬럼은 어떤 것으로 할 것인가?

앞의 기준들을 모두 적용한 후에도 인덱스에 여유가 있다면, 추가적인 컬럼을 넣을 수 있다. 이들은 직접적인 필터링 효과는 없지만, 인덱스만으로 쿼리를 해결할 수 있게 해주어 테이블 액세스를 줄이는 데 기여한다. 예를 들어, SELECT 리스트에만 등장하는 컬럼을 인덱스에 포함하면 커버링 인덱스(covering index)로 활용할 수 있다. 그러나 인덱스 크기만 증가시키고 실제 효과는 없는 경우도 있으므로, 항상 실행계획과 조회 빈도를 기준으로 판단해야 한다.

4.1.6 인덱스 선정 절차

관계형 데이터베이스의 애플리케이션 성능 향상을 위해 인덱스를 최적으로 구성하는 것이 매우 중요하다. 과거에는 인덱스를 통해 특정 액세스 경로를 빠르게 하기 위한 목적이 컸지만, 현재는 옵티마이저가 실행계획을 수립하는 데 큰 역할을 하므로 인덱스 전략이 전체 실행 성능에 직접적으로 영향을 미친다.

 

실무에서는 단일 쿼리 성능 개선을 넘어서, 전체적인 액세스 패턴과 분포도를 고려해 인덱스를 구성해야 한다. 이때 단순히 조건에 ‘=’이 포함된 컬럼만을 고려하는 것이 아니라, 자주 사용되는 액세스 형태나 조합, 정렬 조건 등을 종합적으로 판단하여 인덱스를 구성해야 한다.

 

분포도는 중요한 기준이지만, 단순 비율이 아닌 실제 로우 수로 액세스 효율을 판단해야 한다. 예를 들어 전체 테이블의 1/100 로우를 조회하는 경우 H/W 성능 상 큰 부담이 없을 수 있다. 그러나 범위를 넘어서면 실행계획에 부담을 줄 수 있으므로, 이를 기준으로 인덱스를 다수 설계할 필요가 있다.

 

또한 인덱스는 전체 범위 처리가 아닌 부분 범위 처리를 전제로 하며, 필요 시 클러스터링을 함께 고려해야 한다. 나아가 동일한 인덱스를 여러 액세스에서 활용할 수 있도록 독립적이면서도 공통적으로 쓰이는 컬럼을 기준으로 선정하는 전략이 필요하다.

가) 테이블의 액세스 형태를 최대한으로 수집

인덱스를 설계하기 위해 가장 먼저 수행해야 할 작업은 SQL에서 실제 사용되는 모든 액세스 형태를 최대한 수집하는 것이다. 이는 마치 건물을 지을 때 기초공사를 하는 것과 같은 필수 작업이며, 누락 시 잘못된 인덱스 구조로 인해 부실한 성능을 초래할 수 있다.

 

운영 중인 시스템이라면 실행된 SQL 로그를 통해 형태를 분석할 수 있고, 개발 중인 시스템이라면 예상 액세스 형태를 최대한 포괄적으로 예측해 수집해야 한다. 이 과정을 통해 조건절, 정렬, 그룹핑 등 다양한 액세스 유형을 분석하여 인덱스에 반영할 수 있다.

 

특히 반복적으로 수행되거나, 분포도가 편중된 컬럼, 넓은 범위를 다루는 조건, 자주 결합되는 컬럼, 통계 처리 등을 모두 고려해 엑세스 유형을 분류해야 한다. 예를 들어 다음과 같은 항목을 조사한다.

• 반복 수행되는 액세스
• 분포도가 양호한 컬럼을 사용하는 액세스
• 자주 결합되는 컬럼 조합
• 자주 사용하는 정렬 순서
• 역순 정렬이 필요한 경우
• 통계자료 추출용 액세스

이를 기반으로 SQL별 예상 건수, 증가량, 컬럼별 분포도 등을 기록해 정리하며 인덱스 설계에 반영한다

개발단계의 액세스 형태 수집

개발 단계에서는 아직 모든 액세스가 확정된 상태가 아니기 때문에 예측 기반의 액세스 수집이 필요하다. 이때는 예상되는 형태를 기반으로 7~80% 수준의 인덱스를 미리 구성해두고, 실제 운영 중 보정하는 방식이 현실적인 대응 방법이다.

 

즉, 예상 가능한 범위 내에서 자주 사용될 조건, 넓은 범위 조건, 정렬 조건, 통계 조건 등을 선별하고, 이들을 기반으로 우선적인 인덱스를 구성해두는 것이다. 필요 시 ‘패치’나 ‘서브쿼리’로 반복 액세스를 유도하는 경우도 고려된다.

 

개발단계에서 반드시 인덱스를 완성할 필요는 없지만, 잘못된 구조를 가지고 개발이 진행되면 추후 튜닝이 매우 어렵고 비효율적이 될 수 있다. 따라서 예상되는 액세스를 충분히 고려해 기초 구조를 준비하는 것이 핵심이다.

운영단계의 엑세스 형태 수집

운영단계에서의 엑세스 형태 수집은 애플리케이션이 대부분 작성된 상태에서 해당 애플리케이션이 사용하는 SQL을 기반으로 엑세스 유형을 분석하는 것을 의미한다. 이 단계는 개발이 거의 완료되어 테스트 중이거나, 실제 시스템이 정상 운영 중인 상태에서 인덱스를 보완하거나 교체할 때 진행된다.

 

SQL은 다양한 프로그래밍 언어에서 사용되지만, 언어마다 구문이나 작성 방식이 달라 SQL만을 따로 추출하는 작업은 쉽지 않다. 가장 효율적인 방법은 애플리케이션의 소스코드에서 SQL을 자동 추출하거나, 또는 공유된 SQL 영역에서 직접 추출하는 방식이다. 이 또한 어려운 경우에는 직접 애플리케이션을 분석하여 SQL을 수집해야 한다.

1) 애플리케이션 소스코드에서 SQL을 추출하여 분석용 테이블에 보관한다.

최근에는 시스템 통제를 위한 거버넌스(Governance) 시스템이 발전하면서, 메타 데이터를 저장하는 리포지터리(Repository) 시스템과 함께 SQL을 자동으로 추출하는 소프트웨어들이 도입되고 있다. 대부분의 소프트웨어는 프로그램 소스를 컴파일 수준으로 파싱할 수 있기 때문에, 거의 모든 언어에서 사용된 SQL을 자동 추출할 수 있다.

 

SQL을 추출한 후에는 문자열 내의 토큰(Token)을 분석하여 어떤 테이블, 컬럼을 사용하는지, 어떤 연산자나 조건이 쓰였는지 등을 분석하게 되며, 이를 통해 엑세스 형태를 유추할 수 있다. 이때 SQL과 관련된 다양한 정보—예: 애플리케이션 명칭, SQL 위치, SQL 종류(SELECT/INSERT/UPDATE/DELETE), 사용 테이블, 사용 컬럼, 정렬 조건, 반복 여부 등—를 함께 수집해 분석 테이블에 보관하면 매우 효과적이다.

 

단, 일부 언어에서는 SQL을 문자열로만 선언하거나, 동적으로 생성되기 때문에 정확한 SQL 구조를 파악하기 어려운 경우도 있다. 이를 해결하기 위해서는 반복적인 실측 자료와 함께 SQL 실행 구문을 보완적으로 수집하고 해석할 필요가 있다.

2) SQL-Trace 파일을 파싱하여 SQL 문장 뿐만 아니라 실행계획, 현재 적용되고 있는 인덱스, 실행횟수, 처리범위 등의 매우 상세한 정보를 취득할 수 있다.

SQL-Trace 파일을 활용하면 단순히 SQL 문장뿐만 아니라 해당 SQL의 실행 계획, 사용 중인 인덱스, 실행 횟수, 처리 범위 등 매우 구체적인 실행 정보까지 확인할 수 있어 매우 효과적인 수단이다. Trace 파일은 실제 실행된 SQL을 기록하기 때문에 현재 운영 중인 시스템에서 실질적으로 어떤 SQL이 자주 실행되는지, 어떤 방식으로 인덱스를 활용하고 있는지를 객관적으로 파악할 수 있다.

 

특히, 애플리케이션에서 작성된 SQL과 실제 실행되는 SQL 사이에 차이가 있을 수 있는데, Trace 파일을 통해 이 차이를 명확히 알 수 있다. 이를 통해 인덱스가 실제로 사용되고 있는지, 어떤 SQL이 성능 병목을 유발하는지 분석할 수 있으며, 실질적인 튜닝 대상 SQL을 식별할 수 있다.

 

Trace 파일을 수집하는 방법은 비교적 간단하며, 대부분의 DBMS에서 제공하는 기능을 활용할 수 있다. 이 방식은 사용 언어에 의존하지 않고 SQL의 실제 실행 이력을 기반으로 분석하기 때문에 범용성과 신뢰성이 높다.

 

다만, Trace 방식은 시스템 자원을 많이 소모할 수 있고, 장시간 추적 시 성능 저하를 일으킬 수 있으므로 주의가 필요하다. 이러한 단점을 보완하고 더 장기간 안정적인 수집을 위해 다음 항목에서 설명되는 공용 SQL 영역 추출 등의 보완 기법과 함께 사용하는 것이 좋다.

 

결론적으로, SQL-Trace 파일 분석은 엑세스 패턴과 인덱스 활용도를 정확하게 파악하고, 실질적인 성능 향상 전략을 수립하는 데 필수적인 정보 수집 방법이다.

수집된 SQL을 테이블 별로 출력하여 액세스 형태 기록

앞서 소개된 방법들(애플리케이션 소스코드, SQL-Trace, 공유 SQL 등)을 통해 수집한 SQL은 액세스 형태를 식별하기 쉽도록 형태별로 출력해야 한다. 이때 SQL이 사용된 테이블별로 정렬하여 출력하면 분석 및 정리에 효율적이며, 조건절에 사용된 컬럼 기준으로 정렬하는 것이 바람직하다.

 

하나의 SQL은 여러 개의 테이블을 액세스할 수 있기 때문에, 어떤 SQL이 어떤 테이블에 영향을 주는지를 명확히 파악할 수 있도록 관련 테이블을 중심으로 집계해야 한다. 특히 조건절 기준으로 정렬된 SQL 형태는 인덱스 설계 시 혼란을 줄이고, 빠르게 조건을 파악하는 데 유리하다.

출력된 데이터를 기반으로 액세스 형태를 정리하면 다음과 같은 방식으로 양식에 기재할 수 있다.

SELECT ...
  FROM TAB1 x, TAB2 y
 WHERE x.KEY1 = y.KEY2
   AND x.COL1 = :b1
   AND x.COL2 LIKE '123%';

 

• TAB1의 Access Pattern: KEY1(JOIN), COL1(=), COL2(LIKE)
• TAB2의 Access Pattern: KEY2(JOIN)

이와 같은 방식으로 각 테이블별로 어떤 조건이 사용되고, 조인 조건인지, 비교 연산인지, 범위 조건인지를 모두 기록해야 한다.

 

액세스 형태를 조사할 때는 SQL의 조건절에 사용된 모든 컬럼과 연산자를 빠짐없이 정리해야 한다. 단순히 SQL 전체 문장을 나열하기보다는, 각 조건 항목을 개별적으로 분리하여 정리하는 방식이 보다 분석에 효과적이다.

 

또한, 분석 과정에서는 중복되거나 핵심적인 역할을 하지 않는 조건 컬럼은 과감히 제거하여 정보의 명확성을 높이는 것이 좋다.

 

이렇게 출력된 액세스 형태는 사람이 직관적으로 이해할 수 있어야 하며, 인덱스 전략을 수립하는 데 있어서 핵심적인 참고 자료로 활용된다.

 

특히 자주 반복해서 사용되는 조인 조건 컬럼, 필터링 조건 컬럼, 정렬 조건 컬럼은 향후 결합 인덱스를 구성할 때 매우 중요한 기준이 되므로 반드시 주의 깊게 파악해야 한다.

 

이러한 정리는 인덱스 전략 수립의 최종 단계에서 아주 구체적인 실행계획 수립으로 이어지며, 실전에서 바로 적용 가능한 핵심 데이터를 제공하게 된다.

나) 인덱스 대상 컬럼의 선정 및 분포도 조사

엑세스 유형 조사가 완료되면, 그 결과를 바탕으로 인덱스 대상 컬럼을 선정하고 각 컬럼의 분포도를 분석해야 한다. 주요 기준은 다음과 같다.

• 엑세스 형태에 자주 등장하는 컬럼
• 인덱스의 앞부분에 위치해야 할 컬럼
• 쿼리 성능에 영향을 줄 가능성이 있는 컬럼

조건절에 사용된 모든 컬럼이 반드시 인덱스 구성에 포함되어야 하는 것은 아니다. 실질적으로 성능에 기여하지 않거나 불필요한 컬럼은 과감히 제외하고, 각 엑세스 형태에서 중요한 역할을 하는 컬럼만 추려야 한다.

특히 결합 인덱스 구성을 고려할 때는 단순히 컬럼 하나씩이 아닌, 결합된 형태로 자주 나타나는 패턴을 중심으로 분석한다. 결합 조건에서 함께 자주 사용되는 컬럼들은 함께 고려하여 분포도와 결합 빈도를 평가한다.

 

예컨대, 단일 컬럼의 분포도가 낮아도 결합된 형태로 빈번히 등장하고 전체 성능에 기여한다면 인덱스 후보로 고려될 수 있다. 반대로 분포도는 좋지만 엑세스에 기여하지 않는다면 제외될 수 있다.

인덱스 대상 컬럼을 선정할 때는 단순히 엑세스 빈도만 고려할 것이 아니라, 각 컬럼의 데이터 분포도를 정밀하게 분석하는 것이 중요하다. 이를 위해 각 컬럼에 대해 다음과 같은 방식으로 접근한다.

 

먼저, SALENO(매출번호)의 경우 약 3년간의 데이터가 존재한다고 가정하면, 월별 발생 건수는 총 36개 구간으로 나뉘어 예측할 수 있다. 이를 기반으로 월 단위 평균값을 산정하여 분포도를 평가한다.

 

SALEDATE(판매일자)는 일반적으로 월말에 거래가 집중되는 특성을 갖기 때문에, 데이터가 고르게 분포되어 있지 않다. 이에 따라 평균 약 5,000건 정도가 발생한다고 보고, 해당 값에 기반해 분포를 분석한다.
SALEDEPT(판매부서)는 부서 수에 따라 최대·최소 발생량의 편차가 크게 벌어질 수 있다. 이 경우 단순 평균보다는 각 부서별 분포의 최댓값과 최솟값을 고려하여 평균값을 산출하는 방식으로 편차를 보정한다.
STATUS(진행상태)는 몇 가지 한정된 값으로 구분되는데, 예를 들어 전체 데이터의 70% 이상이 특정 상태 코드(예: ‘완료’)로 몰릴 경우 해당 값이 대부분의 처리 범위를 차지하므로, 상태별 비율에 따른 세분화가 필요하다.
CUSTNO(고객번호)와 AGENTNO(담당자번호)와 같이 사용자 기반 컬럼은 각 고유값의 수와 각 값이 차지하는 데이터 비중을 통해 밀도를 분석해야 한다. 이때, 일부 사용자나 담당자에게 데이터가 과도하게 집중될 수 있으므로, 분산도까지 함께 분석하는 것이 효과적이다.

결론적으로, 각 컬럼에 대해서는 컬럼의 종류 수(고유값 수), 평균 건수, 최대값 및 최소값 등을 기준으로 정량적 분석을 실시해야 하며, 이 분석 결과는 인덱스 전략 수립의 핵심 도구인 ‘특기사항’ 란에 반드시 기록되어야 한다. 이를 통해 보다 과학적이고 신뢰성 있는 인덱스 설계가 가능해진다.

다) 특수한 액세스 형태에 대한 인덱스 선정

특수한 액세스 형태에 대한 인덱스 선정은 반복적으로 액세스되는 쿼리 형태(Critical Access Path)를 우선적으로 고려하여 처리 성능을 향상시키는 것을 목표로 한다. 반복 액세스는 수행 속도 × 반복 횟수에 따라 전체 시스템에 큰 영향을 주므로, 이 형태에 적합한 인덱스를 구성하는 것이 효율적이다. 예를 들어 0.02초 걸리는 쿼리를 0.01초로 줄이는 것만으로도 수백만 번 반복되는 경우 총 처리 시간은 대폭 단축될 수 있다.

 

이 때 인덱스 컬럼을 선정할 때는 ‘항상 사용되는가’, ‘항상 = 연산자인가’, ‘분포도가 좋은가’ 등의 기준을 종합적으로 고려해야 한다. 예를 들어 SALENO와 ITEM 컬럼이 자주 사용된다면, SALENO는 현재 일부 액세스 유형에서만 쓰이지만, ITEM은 분포도가 좋고 다양한 액세스에 포함될 가능성이 크므로 SALENO + ITEM 순으로 인덱스를 구성하는 것이 유리하다.

 

반면, 독립적으로 자주 쓰이는 CUSTNO 컬럼은 범위 처리(BETWEEN, LIKE 등)가 자주 적용되거나 조건으로 사용되지 않는 경우가 많아 단독 인덱스보다는 결합 인덱스에 포함 여부를 신중히 검토해야 한다. CUSTNO LIKE '123%'처럼 문자열 일부가 고정된 조건은 전방 일치가 아니라면 효율성이 떨어질 수 있다.

 

SALEDATE 컬럼은 BETWEEN 조건이 자주 사용되며, 이 경우 논리적으로 최대 범위를 갖기 때문에 실행 계획상 비효율로 이어질 수 있다. 예를 들어 사용자 입력이 자유로운 형태라면 인덱스가 비효율적일 수 있으며, 실행 계획에서 SALEDATE의 범위를 제한하는 것이 필요할 수 있다. 예컨대 “최근 3개월 이내” 혹은 “12개월까지만 허용”과 같은 규약을 두면 인덱스의 활용성이 높아진다.

 

결론적으로, 특수한 액세스 형태에 대한 인덱스 설계는 다음과 같은 원칙을 따라야 한다.

• 반복 사용되는 조건에 최적화된 인덱스 구성
• 분포도와 독립 사용 가능성을 함께 고려
• BETWEEN과 LIKE 등 범위 조건에 대한 처리 범위 제한 여부 검토
• 실제 현업과 협의하여 액세스 범위와 조건을 명확히 설정

이러한 기준을 종합적으로 반영하여, 현황에서 누락된 인덱스와 성능 병목이 발생하는 액세스 유형에 대해 적절한 결합 인덱스를 추가로 정의하는 것이 최종적인 전략 수립의 목적이다.

마) 결합 인덱스 구성 및 순서의 결정

결합 인덱스를 구성하고 순서를 결정하는 과정은, 남은 액세스 형태들을 효율적으로 처리하기 위해 각 컬럼의 조합을 통해 시너지 효과를 극대화하는 전략을 세우는 것이다. 액세스 형태들을 그룹화하여 그에 적절한 결합 인덱스를 구성하는 것이 핵심이다.

 

예를 들어, 그룹 1(예: SALEDATE(like), STATUS(=), CUSTNO(like), SALEDATE(=), STATUS(=), group by CUSTNO, STATUS(in), AGENTNO(like))에서는 우선순위가 높은 컬럼으로 항상 사용되는 STATUS가 핵심이 된다. 그러나 STATUS 하나만으로는 필터링의 효과가 떨어지기 때문에, SALEDATE나 AGENTNO와 같은 컬럼과 결합하여 보완한다. 결합 방식으로는 두 가지가 있으며, 첫째는 SALEDATE+AGENTNO로 두 개의 인덱스를 구성하는 방법이고, 둘째는 STATUS+SALEDATE+AGENTNO 하나로 묶는 방식이다. 전자는 인덱스 수가 많아지지만 입력 시 부담이 적고, 후자는 인덱스를 줄일 수 있는 장점이 있다.

 

그룹 2(예: ITEM(=), SALEDATE(like), SALEDEPT(like), AGENTNO(=), SALEDATE(between), ITEM(like))에서는 ITEM과 SALEDATE가 항상 '=' 조건으로 사용되므로 인덱스 구성의 선행 위치에 놓는 것이 적절하다. 하지만 ITEM이 LIKE 조건으로 사용된 경우 범위가 넓어져 인덱스 효과가 제한되므로, 그 경우에는 SALEDATE가 앞에 오도록 조정하는 방식도 고려해야 한다.

 

특히 AGENTNO는 '=' 조건으로 사용되며 분포도도 양호하기 때문에, 단독으로도 강력한 필터링 역할이 가능하다. 추후 다양한 액세스 형태에 대응할 수 있도록 이 컬럼에 대한 인덱스를 새롭게 정의하는 것도 바람직한 방향이다.

 

결론적으로, 결합 인덱스의 순서는 '=' 조건으로 자주 사용되며 분포도가 좋은 컬럼을 앞세우는 것이 기본 원칙이다. 이후 나머지 조건들을 고려하여, 처리범위를 얼마나 줄일 수 있는지, 인덱스 수를 몇 개로 줄일 수 있는지 등을 종합적으로 판단하여 결합 순서를 최종 결정해야 한다.

 

바) 시험생성 및 테스트

인덱스 전략을 변경하면 기존 실행계획에 핵심적인 영향을 미치기 때문에 운영 시스템에 바로 적용하지 말고 반드시 테스트 과정을 거쳐야 한다. 일반적으로는 복제한 테스트 환경에서 시험을 수행하는 것이 이상적이다.

 

이를 위해 하나의 테이블을 복제해 동일한 구조로 인덱스를 테스트하거나, 클러스터링된 새로운 테이블을 생성해 시험할 수도 있다. 이 때 I/O 분산과 성능 영향을 고려하여 STORAGE나 PCTFREE, TABLESPACE 등의 파라미터 설정도 유의해야 한다.

 

시험은 단순 실행 시간 비교뿐만 아니라 실행계획 자체도 함께 분석하여 인덱스 적용의 효과를 확인하는 것이 중요하다. 예기치 못한 실행계획이 나올 수 있으므로 테스트 결과는 향후 운영 환경의 인덱스 전략을 더욱 현실적으로 결정하는 데 기여하게 된다.

사) 수정이 필요한 애플리케이션 조사 및 수정

아직 개발 중이거나 일부만 작성된 애플리케이션의 경우, 기존 SQL이 없거나 불완전하므로 새로운 인덱스 전략을 위해 소스 수준에서 SQL의 사용 위치를 정확히 파악해야 한다. 필요시 해당 SQL을 다시 작성하거나, 기존 SQL을 수정해 인덱스 전략을 적용할 수 있도록 해야 한다.

 

특정 엑세스 형태를 지원하기 위한 인덱스 사용 여부는 실제 SQL이 어떻게 작성되어 있는지를 기준으로 판단해야 한다. SQL 추출이 어려운 경우라도 힌트(HINT)를 사용하거나, 추후 수정 작업이 필요함을 명시적으로 기록하여 대응해야 한다.

아) 일괄적용

관계형 데이터베이스는 일반적으로 애플리케이션을 컴파일할 때 SQL을 컴파일하지 않으며, 실행 시점에 파싱(Runtime Parsing)을 통해 실행계획을 결정한다. 이로 인해 인덱스를 새로 구성하더라도 애플리케이션의 재컴파일 없이 새로운 실행계획이 자동 적용된다.

 

다만, 기존에 문제가 많은 인덱스 구조는 지나치게 많은 힌트와 불필요한 컬럼이 포함되어 있는 경우가 많다. 인덱스 설계자는 새로운 인덱스 구조에 맞게 기존 SQL을 적절히 수정하고, 필요시 힌트를 제거하거나 수정 사항을 명확히 기록해야 한다.
SQL과 인덱스 변경이 동시에 적용되어야 원하는 실행계획이 나오므로, 변경 시점에 일괄 적용이 매우 중요하다. 이는 실행계획을 정확히 유지하고 프로그램 성능을 안정적으로 확보하는 데 결정적인 역할을 한다.

4.2. 클러스터링 형태의 결정 기준

클러스터링 전략을 수립할 때 단일 테이블과 다중 테이블 클러스터링을 구분하여 접근해야 한다. 단일 테이블 클러스터링은 넓은 범위의 데이터를 효율적으로 스캔하기 위한 방법으로 활용되고, 다중 테이블 클러스터링은 여러 테이블 간의 조인을 효과적으로 처리하기 위해 사용된다. 그러나 실제 환경에서 테이블들은 용도와 데이터 특성에 따라 매우 다양하게 활용되므로, 하나의 방식만을 고정적으로 적용하는 것은 적절하지 않다.

 

예시로 제시된 TAB1부터 TAB4까지의 테이블은 모두 COL1이라는 공통 컬럼을 통해 긴밀한 관계를 형성하고 있다. 이 중 TAB3과 TAB4는 대량의 데이터를 보유하고 있으며, 특히 TAB3은 C0L3 조건을 통해 TAB2 및 TAB1과 자주 조인되는 형태이고, TAB4는 C0L4를 기준으로 넓은 범위 처리가 이루어진다.

 

이러한 구조에서 클러스터링 전략을 수립하는 방법은 다양하다. 예를 들어 TAB3+TAB2+TAB1을 함께 클러스터링하거나, TAB1과 TAB2만을 단독으로 클러스터링하는 방식을 택할 수도 있다. 경우에 따라서는 TAB3이나 TAB4를 단일 테이블로 클러스터링하는 것도 고려될 수 있다. 이처럼 클러스터링 대상 테이블 및 범위는 데이터의 활용 목적과 관계 형태, 처리 범위 등을 전반적으로 고려하여 결정해야 한다.

 

결론적으로, 클러스터링 형태를 결정할 때는 일률적인 방식보다는 실제 데이터 처리 상황과 업무 목적에 기반하여 유연하고 현실적인 기준을 적용해야 하며, 인덱스로만 해결하기 어려운 상황에서 클러스터링이 더 효과적인 대안이 될 수 있다.

4.2.1. 포괄적인 클러스터링

포괄적인 클러스터링은 관련된 다수의 테이블을 한꺼번에 클러스터링하는 방식으로, 처음부터 다수의 테이블을 함께 묶는 결정은 조심스럽게 접근해야 한다. 예를 들어 TAB3와 TAB4가 부모-자식 관계를 갖고 있어 밀접한 관계를 이루고 있고, TAB2와 TAB1까지 포함하여 클러스터링을 고려할 수 있지만, 이들을 무조건 함께 묶는 것은 부작용을 초래할 수 있다.

 

클러스터링을 통해 하나의 클러스터 테이블 안에 TAB1의 로우를 기준으로 TAB2, TAB3의 다수 로우가 함께 저장되는 구조가 되며, 이 경우 블록 단위 저장 시 밀도가 높아지고 블록이 여러 개로 분산 저장될 가능성이 커진다. 특히 TAB3와 같이 데이터량이 많고 밀도가 높은 테이블은 I/O 성능 저하를 야기할 수 있다. 반면 TAB1은 밀도가 낮고 독립적 액세스가 가능한 경우가 많아 클러스터링으로 인한 이득이 크지 않다.

 

이처럼, 다수 테이블을 한꺼번에 클러스터링할 경우 미래의 데이터 증가나 사용 패턴 변화에 민감해질 수 있으므로, 반드시 현재뿐 아니라 향후 예상되는 사용 형태까지 고려해야 하며, 가능하면 최소한의 테이블만 클러스터링하고 나머지는 대안을 함께 검토하는 전략이 바람직하다.

 

결론적으로, 포괄적인 클러스터링은 결합의 유연성이 떨어지고, 독립성과 밀도 문제로 인해 잘못된 판단이 될 수 있으므로, 기술적 결합 관계뿐 아니라 현실적인 운영환경과 업무 특성을 종합적으로 고려한 신중한 결정이 요구된다.

4.2.2. 부분적인 클러스터링

부분적인 클러스터링은 자주 조인이 발생한다고 해서 무조건 적용해야 하는 것이 아니라, 독립성을 고려해 판단하는 것이 바람직하다. 많은 경우, 효율적인 조인을 위해 클러스터링이 고려되지만, 무조건적 접근보다는 다양한 대안 중 하나로써 신중하게 결정해야 한다.

 

예를 들어, TAB1과 TAB2는 공통 컬럼 COL1을 기준으로 결합도가 높아 하나의 블록에 여러 개의 로우가 클러스터링되도록 하면 높은 밀도의 효율적인 저장이 가능하다. 반면, TAB3은 독립적이고 범위 스캔이 잦은 테이블이므로, 단일 테이블로 클러스터링하는 것이 적절할 수 있다.

 

이처럼 결합도와 범위 처리 특성을 고려하면, 업무적인 결합도는 높지만 범위 조건이 자주 붙는 테이블을 독립적으로 클러스터링하는 것이 오히려 효과적인 경우도 있다. 특히, TAB3은 COL3 기준으로 범위 스캔이 빈번하게 일어나기 때문에 단독 클러스터링이 더 나은 선택이 될 수 있다.

 

결국, 클러스터링 여부는 특정 테이블 간 조인의 빈도와 조인 조건의 분포, 각 테이블의 독립성과 크기, 범위 처리 여부를 모두 고려하여 상황에 맞게 결정되어야 하며, 특정 형태로 일괄적으로 적용해서는 안 된다.

4.2.3. 단일테이블 클러스터링

단일테이블 클러스터링은 다중테이블 클러스터링과 비교해 언제 적용되어야 하는지를 보여주는 기준을 제시하기 위해 설명되며, 실제 현실에서는 다양한 테이블이 함께 클러스터링되는 경우는 드물다. 이는 특별한 상황에서만 적용되는 경우가 많으며, 주로 조인의 효율성을 극대화하기 위한 다중 클러스터링이 명확히 필요할 때를 제외하고는 가급적 피하는 것이 좋다.

 

일반적으로 여러 개의 테이블을 조인하는 것보다 하나의 테이블을 범위 조건으로 처리하는 것이 실행 속도 면에서 더 빠르다고 간주된다. 그러나 이는 반드시 그렇다는 보장은 없으며, 랜덤 액세스를 줄이기 위한 클러스터링의 본질은 오히려 단일 테이블에서 명확하게 드러날 수 있다.

 

한 테이블의 로우를 찾기 위해 랜덤 액세스가 발생하고, 그 과정에서 다른 테이블을 조인하게 되면 랜덤한 입출력도 함께 발생한다. 따라서 조인으로 인해 액세스 경로가 비효율적이 되거나 처리 효율이 떨어질 경우, 단일 테이블을 중심으로 클러스터링하는 방식이 더 나은 성능을 제공할 수 있다.

 

여러 개의 테이블을 조인할 때도 그 수가 많지 않다면, 조인의 액세스 경로만 최적화해도 충분히 해결할 수 있으며, 불필요한 클러스터링으로 인한 부작용을 줄일 수 있다. 또한, 여러 테이블을 동시에 클러스터링해도 성능 향상이 없다면, 클러스터링 자체의 목적과 효과를 다시 점검해야 한다.

 

결국, 단일테이블 클러스터링은 단순히 저장공간을 줄이기 위한 수단이 아닌, 넓은 범위의 조건을 스캔 방식으로 효율적으로 처리하기 위한 용도로 사용되어야 한다. 실제로는 대용량 범위를 자주 처리하는 테이블만을 클러스터링하고, 그 외의 테이블은 따로 관리하는 것이 더 좋은 방법인 경우가 많다. 설령 조인이 많더라도 범위 조건으로 처리될 수 있다면, 클러스터링된 테이블 하나만으로도 충분한 처리 성능을 확보할 수 있다.

4.2.4 단위 클러스터의 크기 결정

단위 클러스터링을 수행할 때 가장 중요한 요소 중 하나는 어떤 컬럼으로 클러스터링할 것인지 결정하는 것과 함께, 단위 클러스터의 크기(Size) 를 적절하게 설정하는 것인데 단위 클러스터의 크기는 클러스터링 효율에 매우 큰 영향을 주기 때문에 지나치게 작거나 큰 크기는 피해야 하며, 로우의 길이와 저장할 로우 수를 바탕으로 적절히 산정해야 한다.

 

예를 들어, 로우의 길이가 짧고 많이 저장되는 구조는 A형, 로우가 길고 적은 수만 저장되는 구조는 B형입이다.

일반적으로 범위를 자주 액세스하는 경우에는 A형 또는 C형이 더 효율적이고, D형과 같이 지나치게 작은 단위 클러스터는 액세스 효율을 저하시키므로 피하는 것이 좋다.

 

단위 클러스터의 크기를 산정할 때는 다음의 계산 단계를 따르게 되는데

 

1. 블록당 유효 저장 공간 계산

먼저 블록당 유효 저장 공간을 계산한다. 유효 저장공간 = (블록 크기 - 블록 헤더 크기) × (100 - PCTFREE) / 100
예를 들어 블록 크기가 8,000바이트이고, PCTFREE가 10%일 경우

유효 저장공간 = (8000 - 100) × 0.9 = 7110 바이트

2. 로우의 평균 길이 산정

그 다음 통계 정보를 사용하거나 아래 SQL로 평균 로우 길이를 확인할 수 있다.

ANALYZE TABLE table_name COMPUTE STATISTICS;

SELECT AVG_ROW_LEN 
FROM USER_TABLES 
WHERE TABLE_NAME = 'table_name';

3. 클러스터 키 별 로우 수를 계산

그 후 평균적으로 각 클러스터 키에 몇 개의 로우가 존재하는지 확인한다.

SELECT AVG(CASE WHEN row_cnt > 71 THEN 71 ELSE row_cnt END) average_row_count
FROM (
  SELECT cluster_key_columns, COUNT(*) row_cnt
  FROM table_name
  WHERE sampling_conditions...
  GROUP BY cluster_key_columns
);

4. 단위 클러스터 크기 산정

마지막으로 단위 클러스터 크기 산정하는데 다음과 같은 계산식을 사용한다.

평균 로우 길이 × average_row_count = 단위 클러스터 크기

예를 들어 평균 로우 길이가 100바이트이고, average_row_count가 20이라면 아래와 같은 계산식으로 계산된다.

단위 클러스터 크기 = 100 × 20 = 2,000 바이트

만약 블록의 유효 저장공간이 7110이라면 블록 하나당 약 3개의 클러스터가 저장될 수 있다.

클러스터링 생성 예시 절차

아래는 SALE_DATE 컬럼을 기준으로 클러스터링을 수행하는 예시이다.

1. 클러스터 생성

먼저 클러스터를 생성 시킨다.

CREATE CLUSTER sales_cluster (
  sale_date VARCHAR2(8)
)
STORAGE (
  PCTFREE 10
  PCTUSED 60
)
SIZE 2000;

2. 클러스터 인덱스 생성
그 다음 클러스터 인덱스를 생성해준다.

CREATE INDEX sales_cluster_idx 
ON CLUSTER sales_cluster 
PCTFREE 2 
STORAGE (INITIAL 20K NEXT 10K);

 

3. 기존 테이블 명 변경

그 후 테이블 이름을 변경해준다.

RENAME sales TO sales_copy;

4. 클러스터 내 테이블 재생성

테이블 이름이 변경됐다면 클러스터 내 테이블을 재생성 해준다.

CREATE TABLE sales (
  saleno VARCHAR2(6) NOT NULL,
  sale_dept CHAR(4),
  sale_date VARCHAR2(8) NOT NULL,
  ...
)
CLUSTER sales_cluster (sale_date);

5. 데이터 이관
그 후 데이터를 이관시켜준다.

INSERT INTO sales 
SELECT * FROM sales_copy 
WHERE sale_date >= '20110101';

6. 기존 테이블 삭제 및 인덱스 재생성
마지막으로 기존 테이블 삭제 및 인덱스 재생성 시켜준다.

DROP TABLE sales_copy;
CREATE INDEX ... ;

이러한 절차와 기준을 통해, 클러스터링 효율을 최대화하고 저장 공간 및 액세스 속도 측면에서 최적의 성능을 확보할 수 있다.

 

Start  : 2025. 04. 21

End : 2025. 05. 13