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사라졌다. 이유는 테이블 departmentlocation에서 모두 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에 대하여 논의하자.
이번 내용도 집필중인 책의 일부분이다. 이제 어떤 것을 주제로 책인지 감이 오지 않는가?

Posted by extremedb

댓글을 달아 주세요

  1. Eddy 2010.01.20 18:06  댓글주소  수정/삭제  댓글쓰기

    pk, uk 컬럼에 unique index를 미리 만들면 제약과 관련된 다음 기능을 사용할 수 없게 됩니다.

    - enable novalidate
    - deferrable 제약

    그래서 normal index를 생성하게 하는데, 그 경우 DEUI 기능을 놓칠 수 있겠군요.

  2. feelie 2010.01.20 18:37  댓글주소  수정/삭제  댓글쓰기

    알고 있는내용이라고 생각했는데 서버쿼리와 연결해서 생각하니 참...

    아직 기본도 부족하고, 운용도 부족한가 보네요..
    내용 잘봤습니다..

  3. 서상서 2010.02.10 17:41  댓글주소  수정/삭제  댓글쓰기

    11 R1에서는 Sort Unique같이 실행계획이 나타나지는 않지만 실행계획이 언급하신것과 같이는 풀리지 않는것 같읍니다.혹시 이전에 언급하신 히든 파리미터때문인가요?

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.02.10 17:59 신고  댓글주소  수정/삭제

      DE를 이야시하시는 건지 DEUI를 이야기 하시는 건지 알수가 없네요.
      DE든 DEUI든 Distinct(Sort Unique 혹은 Hash Unique)제거하는 기능입니다. 그외에 다른 테이블에 엑세스 하지 않는 기능은 JE(Join Elimination)라는 기능이 따로 존재합니다. 또한 인덱스를 타고 안타고는 JE 나 DE, DEUI 와는 상관없이 여러가지 통계정보나 인덱스의 여부에 따라 Cost Estimator의 Return 값에 따라 판단되는것 입니다.