출처: http://hyeonstorage.tistory.com/23
결합인덱스와 컬럼 순서
- 결합인덱스란 복수 개의 컬럼으로 구성된 인덱스로서 각각의 컬럼의 분포도는 나쁘지만 컬럼 몇 개가 결합되면 분포도가 양호해지는 경우에 사용된다.
- 결합인덱스를 구성하는 대부분의 컬럼들이 Where 조건절에 사용되었어도 첫 번째 컬럼(Leading Column)이 조건절에 쓰이지 않으면 옵티마이저는 인덱스를 Access할 수 없다.
- 즉, 결합인덱스를 구성하는 첫 번째 컬럼은 반드시 조건절에 쓰여야 그 인덱스가 옵티마이저에 의해 Access 하게 된다.
- 결합인덱스의 컬럼의 순서에 따라서 Access하는 범위가 결정된다.
- 즉, Where 조건절에서 비교연산자 EQUAL(=) 상수의 조건이 AND의 조건으로 여러 개의 컬럼이 열거되어 인덱스 컬럼의 매칭율을 높일수록 Access의 범위는 줄어들게 되며 Access하는 양이 줄어들게 되므로 수행속도는 빠르게 된다.
1. 첫번째 컬럼 연산자가 EQUAL(=) 이 아닐 경우
2. 결합인덱스의 일부 컬럼이 조건절에 없는 경우
3. 정리
결합인덱스와 컬럼 순서
- 결합인덱스란 복수 개의 컬럼으로 구성된 인덱스로서 각각의 컬럼의 분포도는 나쁘지만 컬럼 몇 개가 결합되면 분포도가 양호해지는 경우에 사용된다.
- 결합인덱스를 구성하는 대부분의 컬럼들이 Where 조건절에 사용되었어도 첫 번째 컬럼(Leading Column)이 조건절에 쓰이지 않으면 옵티마이저는 인덱스를 Access할 수 없다.
- 즉, 결합인덱스를 구성하는 첫 번째 컬럼은 반드시 조건절에 쓰여야 그 인덱스가 옵티마이저에 의해 Access 하게 된다.
- 결합인덱스의 컬럼의 순서에 따라서 Access하는 범위가 결정된다.
- 즉, Where 조건절에서 비교연산자 EQUAL(=) 상수의 조건이 AND의 조건으로 여러 개의 컬럼이 열거되어 인덱스 컬럼의 매칭율을 높일수록 Access의 범위는 줄어들게 되며 Access하는 양이 줄어들게 되므로 수행속도는 빠르게 된다.
1. 첫번째 컬럼 연산자가 EQUAL(=) 이 아닐 경우
- SQL
SELECT A.ORDCD, A.ORDDESC1, A.ORDDESC2
FROM MDTRTORT A
WHERE A.MEDDATE LIKE :5||'%'
AND A.MEDDEPT = :B3
AND A.SLIPCD = 'M12'
AND ORDGRP = 'D2'
- Index
MDTRTORT : MDTRTORT_IDX1 : MEDDATE + MEDDEPT + SLIPCD
- Execution Plan
> 우리가 얻은 결과는 2220건 이다. 그러나 테이블 MDTRTORT의 Index MDTRTORT_IDX1 에서 48276건을 Access 하고 있다. 얼마나 비효율적이라는 것을 알 수 있다.
인덱스를 RANGE SCAN 후 테이블을 ACCESS 한 결과 5%도 안 되는 건수를 가져오고 있다.
- 문제점
> 사용된
Index MDTRTORT_IDX1의 첫번째 컬럼인 MEDDATE가 Where 조건절에서 비교연산자가 EQUAL(=)이 아니라
범위형태인 LIKE로 비교되어 나머지 MEDDEPT = 과 SLIPCD = 이 범위를 줄이지 못한다.
> Access 하는 Data 중 약 95%를 버리게 되는 비효율적인 Access 형태임.
- 해결 방법
> 하나의 해결 방법은 인덱스 컬럼의 매칭율을 높여주는 방법이다.
> 현재의 SQL문에서 인덱스 컬럼의 매칭율은 첫 번째 컬럼의 비교연산자가 = (EQUAL) 이 아니기 때문에 0% 이다. 따라서 인덱스 컬럼의 매칭율을 높여서 Access의 범위를 줄여야 한다.
> 그러면 인덱스 컬럼의 매칭율을 높여 주는 방법으로서는 인덱스 컬럼순서를 변경하거나 신규로 인덱스를 생성하는 방법이 있다.
> 인덱스를 변경ㅎ아여 새로 생성하거나 추가적으로 생성할 경우에 다른 SQL문의 실행계획에 영향을 주어서 수행속도에 영향을 주는가에 대한 조사를 반드시 해야 한다.
- Index 순서 변경
MDTRTORT : MDTRTORT_IDX1 : MEDDEPT + SLIPCD + MEDDATE
- Index 순서 변경 후 Execution Plan
> 인덱스의 결합 순서를 바꿈으로써 훨씬 효율적인 Access가 되었다.
> 위 사례는
결합인덱스의 첫 번째 컬럼이 EQUAL(=)인 비교연산자를 사용하느냐 아니면 인덱스를 ACCESS할 수 있는 LIKE나
BETWEEN 같은 연산자를 사용하느냐에 따라서 ACCESS 횟수가 많이 줄어들 수 있는가를 보여준다.
> 반드시 결합인덱스를 생성할 경우에는 WHERE 조건절에서 비교연산자가 주로 EQUAL(=)을 사용하는 컬럼을 앞쪽에 위치하게 하여 생성해야 된다.
2. 결합인덱스의 일부 컬럼이 조건절에 없는 경우
- 결합인덱스에서 범위를 줄일 수 있는 것은 결합인덱스를 구성하는 컬럼들이 모두 Where 조건문에 비교연산자 EQUAL(=) 상수로 사용되는 경우이고 전부는 아니더라도 많을수록 인덱스를 Access하는 범위가 좁아진다.
- 결합인덱스를 여러 컬럼이 구성하고 있다고 하더라도 인덱스를 구성한 컬럼들이 순서대로 Where 조건문에 있어야 하며, 구성 컬럼 중 하나라도 Where 조건절에서 제외되면 인덱스의 컬럼 매칭율은 현저히 떨어지게 된다.
- 결합인덱스를 구성하는 컬럼 중 일부가 Where 조건문에서 빠져 Access 범위가 넓을 경우의 해결책
1) 인덱스 컬럼의 순서를 변경 생성한다.
2) 인덱스를 신규로 생성 한다.
3) 인덱스의 중간컬럼을 사용한다.
결합인덱스를 구성하는 컬럼 중 일부가 Where 조건문에서 빠졌을 경우의 예제
- SQL
SELECT *
FROM CUSTOMER A
WHERE CUST_ID = '0001'
AND TR_DATE >= '20020101'
- Index
CUSTOMER : CUSTOMER_PK : CUST_ID + TYPE + TR_DATE
- Execution Plan
> 여기서 얻은
결과는 40건이다. 그러나 테이블 CUSTOMER의 Index CUSTOMER_PK에서 401건을 ACCESS 하고 있다.
Primary 인덱스를 이용하면서도, 인덱스를 SCAN한 양의 약 10%를 최종적으로 가져 온다.
- 문제점
> 여기서 인덱스 컬럼 매칭율을 살펴보면 66%으로 두 개의 컬럼으로 인덱스를 Search 하는 것처럼 보인다. 하지만 자세히 보면 인덱스의 두 번째 컬럼이 Where 조건절에 없음을 알 수 있다.
> 여기서는 인덱스 컬럼 매칭율이 33%이고 단지 첫번째 컬럼인 CUST_ID만 인덱스를 Access하고, TR_DATE 컬럼은 인덱스의 구성컬럼이면서도 CHECK 조건으로 밖에 사용되지 않았다.
(1) 인덱스 컬럼 순서 변경
- 먼저, 인덱스 컬럼의 매칭율을 높이기 위해서 인덱스를 재구성하는 방법이 있다.
- 그럼 인덱스를 재구성해서 컬럼 TR_DATE와 컬럼 TYPE을 바꾸어서 두 번째 컬럼까지 인덱스를 Access 할 수 있도록 SQL 문을 실행한다.
- Index 순서 변경 후
CUSTOMER : CUSTOMER_PK : CUST_ID + TR_DATE +TYPE
- 변경 후 Execution Plan
> 인덱스를 Access 하는 범위도 줄었고 최종적으로 Index에서 Access한 41row중 대부분의 row를 결과로 가져오면서 아주 효율적이 되었다.
> 해당 SQL 문의 문제를 해결하기 위한 변경사항으로 인하여, 다른 SQL문에 문제를 일으킬 수 있다.
( CUST_ID 와 TYPE 만을 Where 조건에 사용하는 SQL )
(2) 인덱스를 신규로 생성
- Index 의 순서를 변경하였을 때, 다른 SQL 문에 악영향을 미친다면, 다른 해결 방법으로 신규 인덱스를 생성한다.
- 테이블 CUSTOMER에 컬럼 CUST_ID와 컬럼 TR_DATE로 구성된 결합인덱스 Non Unique Index 인 CUSTOMER_IX1을 생성하겠다.
- Index 신규 생성 후
CUSTOMER : CUSTOMER_PK : CUST_ID + TYPE + TR_DATE
CUSTOMER : CUSTOMER_IX1 : CUST_ID + TR_DATE
- Index 신규 생성 후 Execution Plan
> 인덱스의 Access 범위도 줄었고 최종적으로 Index에서 Access한 41row 중 대부분의 row를 결과로 가져오면서 아주 효율적으로 되었다.
> 하지만, Index 신규 생성 역시 다른 SQL문에 문제를 일으킬 수 있다.
(
CUST_ID, TYPE, TR_DATE 를 Where 조건에 사용하는 SQL문에는 2개의 Index 모두 매칭율이
100%이다. 그러면 나중에 생성된 CUSTOMER_IX1 이 Index로 Access 되며, CUSTOMER_PK Index를
사용할 때보다 효율이 떨어진다.) -> HINT를 주어서 옵티마이저가 PK UNIQUE 인덱스를 이용하게 할 수 있다.
(3) 중간 컬럼을 SQL문에 사용
- CUSTOMER_PK 의 중간 컬럼인 TYPE을 SQL에 강제로 넣어주는 방법입니다.
- 업무적으로 분석할 경우에 TYPE 에는 'A'값과 'B' 값 중 한 값만 들어간다면, 아래와 같이 Where 절에 Type 을 넣어줄 수 있습니다.
- SQL 문에 중간 컬럼 TYPE 추가
SELECT *
FROM CUSTOMER A
WHERE CUST_ID = '0001'
AND TR_DATE >= '20020101'
AND TYPE IN ('A', 'B')
- Index는 변동이 없다.
CUSTOMER : CUSTOMER_PK : CUST_ID + TYPE + TR_DATE
- Execution Plan
> 위 실행계획은 인덱스와 테이블을 한번 더 Access한 것 말고는 별로 문제될 것 없이 잘 수행되고 또한 인덱스의 변경 및 신규생성이 없으므로 기존 SQL문에 영향도 없음을 알 수 있다.
> 물론 위와
같이 TYPE이 두 개의 값만을 가질 경우는 문제 없겠지만 그렇지 않을 경우는 또 다른 방법을 찾아봐야 한다. 만약 위에서
제기되었던 인덱스 생성 또는 인덱스 컬럼의 재구성으로 변경이 되었을 경우에 문제가 없을 경우는 인덱스 생성 및 변경으로도 해결 할
수 있다.
3. 정리
- 결합인덱스 : 두 개 이상의 컬럼으로 만든 인덱스로 각각의 컬럼이 분포도가 나쁘다 하더라도 결합인덱스를 생성할 경우에는 분포도가 좋을 수 있다.
- 결합인덱스에 대한 인덱스 컬럼의 매칭율이 높을 수록 Access 속도가 향상되는 것을 알고 결합인덱스의 컬럼순서를 신중하게 고려해야 한다.
- 중간 컬럼이 Where 조건에 없으면 인덱스 컬럼의 매칭율이 낮으므로 중간컬럼에 대한 업무적 특성을 파악하여 Where 조건문에 사용할 수 잇으면 사용하도록 조치 해야 한다.
댓글
댓글 쓰기