지난시간의 DEUI라는 기능에 이어서 이번시간에는 그 사촌격인 DE에 대해서 논의해보자.

DE (Distinct Elimination)란 무엇인가
DE는 Unique한 집합일 경우 불필요한 Distinct를 제거하는 기능이다. 이렇게 함으로써 Sort와 중복제거 등의 부하가 많은 작업을 수행하지 않을 수 있다. 이 기능은 Oracle 11g에서 추가되었다. 이제 DE가 어떻게 수행되는지 알아보자.

SELECT distinct d.department_id, l.location_id

  FROM department d, location l

 WHERE d.location_id = l.location_id ;

 

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

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

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

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

| 1   |  NESTED LOOPS       |           |    27 |   270 |     3 |  00:00:01 |

| 2   |   TABLE ACCESS FULL | DEPARTMENT|    27 |   189 |     3 |  00:00:01 |

| 3   |   INDEX UNIQUE SCAN | LOC_ID_PK |     1 |     3 |     0 |           |

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

Predicate Information:

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

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

Unique를 보장하는 컬럼이 있으면 Distinct가 필요없다
실행계획에서 Distinct에 해당하는 Operation인 Sort Unique 혹은 Hash Unique가 사라졌다. 이유는 Transformer가 위의 SQL을 분석해서 Distinct가 없어도 Unique 함을 알았기 때문이다. Select 절에 있는 d.department_id와 l.location_id는 From 절에 있는 두 테이블의 PK 이다.  따라서 Distinct는 당연히 필요 없음으로 Logical Optimizer가 삭제한 것이다.

아래는 DE와 관련된 10053 Event의 Trace 내용이다.
 

OBYE:   Considering Order-by Elimination from view SEL$1 (#0)
***************************

Order-by elimination (OBYE)

***************************

OBYE:     OBYE bypassed: no order by to eliminate.

Eliminated SELECT DISTINCT from query block SEL$1 (#0)

이후생략


10053 Trace 상에서는 DE가 OBYE 자리에서 발생하였다. 이것은 OBYE 수행여부를 체크할 때 DE의 수행여부를 같이 체크하므로 DE가 OBYE 자리에서 발생되는 것 같다.

함정에 주의할 것
Unique 하다고 해서 항상 DE가 발생될까? 그렇지 않다. 아래의 SQL을 보자.

SELECT distinct d.department_id, d.location_id

  FROM department d, location l

 WHERE d.location_id = l.location_id ;


위의 SQL은 location_id를 department 테이블의 컬럼으로 대체하였다는 점을 제외하면 최초의 SQL과 완전히 같다.
-----------------------------------------+-----------------------------------+

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

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

| 0   | SELECT STATEMENT     |           |       |       |     4 |           |

| 1   |  HASH UNIQUE         |           |    27 |   270 |     4 |  00:00:01 |

| 2   |   NESTED LOOPS       |           |    27 |   270 |     3 |  00:00:01 |

| 3   |    TABLE ACCESS FULL | DEPARTMENT|    27 |   189 |     3 |  00:00:01 |

| 4   |    INDEX UNIQUE SCAN | LOC_ID_PK |     1 |     3 |     0 |           |

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

Predicate Information:

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

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

 

Unique를 보장하는 모든 테이블의 컬럼이 Select 절에 사용되어야
컬럼 하나만이 바뀌었을 뿐인데 실행계획에 Hash Unique가 생긴 것이다. 여기서 알 수 있는 것은 From 절에 나열된 모든 테이블의 PK 컬럼 혹은 Unique 컬럼이 Select 절에 나와야 Distinct가 제거된다는 사실이다. 이것은 아직 DE 기능이 완성되지 않은 것을 의미한다. 논리적으로는 Select 절에 d.location_id를 사용하거나 l.location_id를 사용해도 같기 때문에 DE가 발생 해야 하지만 아직 이런 기능이 없다는 것이 아쉽다
.

DE 기능을 Control 하는 파라미터는 _optimizer_distinct_elimination이며 Default로 True 이다. 하지만 이 파라미터로는 DEUI 기능을 Control 할수 없다. 한가지 주의사항은 DE가 버전 10gR2(10.2.0.4)에서도 수행된다는 점이다. 다만 _optimizer_distinct_elimination 파라미터가 없다는 것이 11g와 다른 점이다.

결론
만약 이런 일이 대용량 테이블에서 발생한다면 결과는 심각한 성능저하로 나타날 수 있으므로 조인된 컬럼을 사용할 것인지 아니면 참조되는 컬럼을 사용할 것인지 아주 신중히 결정해야 한다.

성능저하가 예상되는 부분
예를들면 서브쿼리가 Unnesting되어 Distinct가 자동으로 추가된 인라인 뷰에 CVM이 발생하면 인라인뷰가 해체되므로 전체집합에 대해서 Sort Unique 혹은 Hash Unique가 발생된다. 전체집합이 대용량이라면 성능은 심각하게 저하될 것이다. 이 사실은 어떤 컬럼을 Select 절에서 사용할 것인지 아주 신중히 결정해야 하며 Merge 힌트를 얼마나 조심스럽게 사용해야 하는지를 잘 나타내 주고 있다.

PS
이렇게 나가다간 아마 책을 출판할 필요가 없을듯 하다.^^ 하지만 책보다는 블로그가 우선이다.

Posted by extremedb

댓글을 달아 주세요

  1. feelie 2010.01.25 09:38  댓글주소  수정/삭제  댓글쓰기

    select list에 기술되는 컬럼명 사용에 이런 분명한 차이도 있었군요.
    사소한 쿼리라도 실행계획을 꼭확인하는 습관을 들이라는 말씀이 이런경우일까요?
    내용잘봤습니다..

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.01.25 10:53 신고  댓글주소  수정/삭제

      핵심을 잘 파악하셨습니다.
      실행계획을 잘 살펴보면 비효율 적인 부분이 보이지요. 이때 Query Transformation이 하는 일을 알고 본다면 문제의 해결이 가능 합니다.

  2. Eddy 2010.01.26 19:19  댓글주소  수정/삭제  댓글쓰기

    "책보다는 블로그가 우선이다."라는 말씀에 감사드립니다.
    책내용의 90%가 블로그에 있어도 아마도 독자의 대부분은 책을 구입할 것입니다.

    좋은 책을 세상에 내어 놓았지만 블로그가 없는 경우가 있어 섭섭함이 있었는데,
    extremedb님의 결정은 참 좋아보입니다. 참 감사한 결정이라고 생각합니다. ㅎㅎ

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

      방형욱님 반갑습니다.
      저와 블로그 구독자에게는 좋은 결정이지만 회사에서 뭐라고 할지 걱정이 되네요...
      감사합니다.

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

    버전에 따라 약간의 차이가 있는것 같은 생각이 듭니다. 버전 11.1.0.7.0입니다.
    제가 테스트한 버전에서는 이렇게 풀리면서 더욱 효율적으로 옵티마이저가 생각을 하는것 같은데요.
    옵티마이저가 부서테이블에 DEPT_LOCATION_IX 아마도 이것이 결합인덱스인것 같읍니다.
    인덱스만 가지고 끝내는것 같군요.(location_id가 not null제약조건이 있는것으로 판단)

    location테이블은 엑세스를 하지 않네요.비용도 제시한 것보다 위의것보다 작은것 같구요.
    한번 돌려보았읍니다. 제 로컬PC에서 말이죠.
    SQL> EXPLAIN PLAN FOR
    2 SELECT distinct d.department_id,l.location_id
    3 FROM departments d,locations l
    4 WHERE d.location_id = l.location_id;

    해석되었습니다.

    SQL> SELECT * FROM table(dbms_xplan.display(null,null,'all'));

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 3680985111

    ----------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
    -----------------------------------------------------------------------------------------------
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 27 | 270 | 2 (0)| 00:00:01 |
    | 1 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 270 | 2 (0)| 00:00:01 |
    |* 2 | INDEX FULL SCAN | DEPT_LOCATION_IX | 27 | | 1 (0)| 00:00:01 |

    -----------------------------------------------------------------------------------------------
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
    1 - SEL$120E9FF1 / D@SEL$1
    2 - SEL$120E9FF1 / D@SEL$1

    Predicate Information (identified by operation id):
    ---------------------------------------------------
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    2 - filter("D"."LOCATION_ID" IS NOT NULL)

    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
    1 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."LOCATION_ID"[NUMBER,22]
    2 - "D".ROWID[ROWID,10], "D"."LOCATION_ID"[NUMBER,22]

    26 개의 행이 선택되었습니다.

    결국은 실제로 돌려보지 않고 이런것이 있구나 이렇게 넘어가면 안되겠구나 하는 그런 생각을 갖게 됩니다.
    고맙읍니다.

    버전은 혹시 어떻게 되시나요? 11R1버전에서는 이렇게 실행되었읍니다.
    그리고 많이 감사합니다. 좋은글이요.

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

      반갑습니다.
      통계정보의 상태에 따라 인덱스만 탈 수도 있고 FULL TABLE SCAN을 할 수도 있지만 Distinct Elimination이 테이블을 제거할 수는 없습니다. 테이블을 제거하는 기능은 JE(Join Elimination) 이라고 하며 7가지로 분류할 수 있습니다. 자세한 내용은 3월달에 나올 책을 참조 하시기 바랍니다.

      서상서님이 보여주신 기능은 Primary Key-Foreign Key 관계를 이용한 JE 입니다. FK를 삭제하시면 location을 access 할것 입니다.
      감사합니다.