Index Unque Scan은 SQL을 변경시킨다

INDEX UNIQUE SCAN의 비밀
당신은 INDEX UNIQUE SCAN을 쉬운 Operation쯤으로 여길 것이다. 하지만 여기에는 숨겨진 기능이 있다. 대표적인 경우가 Unique 인덱스를 사용하여 INDEX UNIQUE SCAN Operation이 나오면 Distinct를 제거하는 기능이다. 이 기능의 이름이 없으므로 DEUI*(Distinct Elimination using Unique Index)라 부르기로 하자. 이제 아래의 SQL을 보자.

SELECT DISTINCT d.department_id, l.city, l.country_id

  FROM department d, location l

 WHERE d.location_id = l.location_id

   AND d.department_id = 10 ;

 

--------------------------------------------------+-----------------------------------+

| Id  | Operation                     | Name      | Rows  | Bytes | Cost  | Time      |

--------------------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT              |           |       |       |     3 |           |

| 1   |  NESTED LOOPS                 |           |     1 |    22 |     2 |  00:00:01 |

| 2   |   TABLE ACCESS BY INDEX ROWID | DEPARTMENT|     1 |     7 |     1 |  00:00:01 |

| 3   |    INDEX UNIQUE SCAN          | DEPT_ID_PK|     1 |       |     0 |           |

| 4   |   TABLE ACCESS BY INDEX ROWID | LOCATION  |    23 |   345 |     1 |  00:00:01 |

| 5   |    INDEX UNIQUE SCAN          | LOC_ID_PK |     1 |       |     0 |           |

--------------------------------------------------+-----------------------------------+

 

Predicate Information:

----------------------

3 - access("D"."DEPARTMENT_ID"=10)

5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

 

SQL이 변경되었다
Distinct에 의한 Sort Unique 혹은 Hash Unique가 사라졌다. 그 이유는 테이블 department와 location에서 모두 INDEX UNIQUE SCAN을 사용하였기 때문이다. 하지만 반대로 Unique 인덱스를 사용하지 않는다면 DEUI는 절대 수행되지 않는다. 아래의 SQL은 위의 SQL에서 힌트만 추가한 것이다.
 

SELECT /*+ FULL(d) */ DISTINCT d.department_id, l.city, l.country_id

  FROM department d, location l

 WHERE d.location_id = l.location_id

   AND d.department_id = 10 ;

 

---------------------------------------------------+-----------------------------------+

| Id  | Operation                      | Name      | Rows  | Bytes | Cost  | Time      |

---------------------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT               |           |       |       |     5 |           |

| 1   |  HASH UNIQUE                   |           |     1 |    22 |     5 |  00:00:01 |

| 2   |   NESTED LOOPS                 |           |       |       |       |           |

| 3   |    NESTED LOOPS                |           |     1 |    22 |     4 |  00:00:01 |

| 4   |     TABLE ACCESS FULL          | DEPARTMENT|     1 |     7 |     3 |  00:00:01 |

| 5   |     INDEX UNIQUE SCAN          | LOC_ID_PK |     1 |       |     0 |           |

| 6   |    TABLE ACCESS BY INDEX ROWID | LOCATION  |    23 |   345 |     1 |  00:00:01 |

---------------------------------------------------+-----------------------------------+

 

Predicate Information:

----------------------

4 - filter("D"."DEPARTMENT_ID"=10)

5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

 

 

순간의 실수로
Department 테이블을 Full Scan 하자 Plan 상에 HASH UNIQUE가 나타났다. 다시 말해서 옵티마이져의 실수로 다른 종류의 인덱스를 사용하거나 Full Scan이 된다면 Query Transformation은 발생되지 않는다.
 

결론

우리가 무심코 사용하는 Unique Index의 INDEX UNIQUE SCAN Operation은 DEUI라는 기능을 내장하고 있다. 당연한 기능이라고 여기겠지만 이것을 모르면 많은 것을 놓친다. 서브쿼리가 Unnesting되어 Driving 집합이 되면 메인쿼리의 집합을 보존하기 위하여 Default로 Distinct가 추가된다. 이제 INDEX UNIQUE SCAN을 사용하는 서브쿼리가 Unnesting되어 Driving 집합이 될 때 Distinct가 사라진 비밀을 이야기 할 수 있겠는가? Unique 한 값을 가지는 컬럼이나 컬럼조합을 Normal 인덱스가 아닌 Unique 인덱스로 만들어야 할 이유를 알겠는가? 단순한 Operation 하나라도 무시할 수 없는 이유가 된다.

PS
다음시간에는 DEUI의 사촌격인 DE에 대하여 논의하자.
이번 내용도 집필중인 책의 일부분이다. 이제 어떤 것을 주제로 한 책인지 감이 오지 않는가?