2.2.6 DE - 불필요한 Distinct를 제거하라

-- Select 절에 Unique한 컬럼이 포함되어 있는 경우
-- Sort와 중복제거 등의 부하가 많은 작업을 수행하지 않을 수 있다.
-- Oracle11g에서 추가

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    |           |       |       |     4 |           |
| 1   |  NESTED LOOPS       |           |    23 |   230 |     4 |  00:00:01 |
| 2   |   TABLE ACCESS FULL | DEPARTMENT|    27 |   189 |     4 |  00:00:01 |
| 3   |   INDEX UNIQUE SCAN | LOC_ID_PK |     1 |     3 |     0 |           |
----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
 
Content of other_xml column
===========================
  db_version     : 11.2.0.1
  parse_schema   : TLO
  plan_hash      : 1784976171
  plan_hash_2    : 118757124
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "D"@"SEL$1")
      INDEX(@"SEL$1" "L"@"SEL$1" ("LOCATION"."LOCATION_ID"))
      LEADING(@"SEL$1" "D"@"SEL$1" "L"@"SEL$1")
      USE_NL(@"SEL$1" "L"@"SEL$1")
    END_OUTLINE_DATA
  */

-- Distinct에 해당하는 Operation인 Sort Unique 혹은 Hash Unique가 사라졌다.
-- Distinct가 없어도 Unique 함을 알았기 때문
-- DEPARTMENT : DEPARTMENT_ID, LOCATION : LOCATION_ID 각각 두 테이블의 PK
-- Select절에 PK이 컬럼이 모두 포함되면 자체로 Unique 하므로 Distinct는 필요 없어지므로 삭제되도 무방하다.

***************************
Order-by elimination (OBYE)
***************************
OBYE:     OBYE bypassed: no order by to eliminate.               -- OBYE가 먼저 발생하고 지후 DE 수행
Eliminated SELECT DISTINCT from query block SEL$1 (#0)  -- Distinct가 삭제됨
JE:   Considering Join Elimination on query block SEL$1 (#0)

■ 함정에 주의할 것 : Unique 하다고 항상 DE가 발생될까?

SELECT DISTINCT D.DEPARTMENT_ID, D.LOCATION_ID -- D.LOCATION_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     |           |       |       |     5 |           |
| 1   |  HASH UNIQUE         |           |    23 |   230 |     5 |  00:00:01 |
| 2   |   NESTED LOOPS       |           |    23 |   230 |     4 |  00:00:01 |
| 3   |    TABLE ACCESS FULL | DEPARTMENT|    27 |   189 |     4 |  00:00:01 |
| 4   |    INDEX UNIQUE SCAN | LOC_ID_PK |     1 |     3 |     0 |           |
-----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
 
Content of other_xml column
===========================
  db_version     : 11.2.0.1
  parse_schema   : TLO
  plan_hash      : 4104807432
  plan_hash_2    : 2406888189
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "D"@"SEL$1")
      INDEX(@"SEL$1" "L"@"SEL$1" ("LOCATION"."LOCATION_ID"))
      LEADING(@"SEL$1" "D"@"SEL$1" "L"@"SEL$1")
      USE_NL(@"SEL$1" "L"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$1")
    END_OUTLINE_DATA
  */

■ 제약조건
-- From 절에 나열된 모든 테이블의 PK 컬럼 혹은 Unique 컬럼이 Select 절에 나와야 Distinct가 제거 된다.
   (아직 DE 기능이 완성되지 않은 것을 의미)
-- DEPARTMENT 쪽의 PK Constraint를 Drop하고 Unique인덱스만 존재한다면 Null 데이터가 중복될 수 있기 때문에

   DE가 발생되지 않는다.
-- DEPARTMENT_ID 컬럼에 Not Null 제약조건을 추가한다면 DE가 발생한다.

   즉, 1 : N의 관계에서는 N쪽은 Not Null 조건이 필요하다는 뜻

■ DE의 또 다른 유형 DEUI*(Distinct Elimination using Unique Index)
-- HQT 에서는 DE와 유사한 기능 : Unique 인덱스를 사용하여 INDEX UNIQUE SCAN Operation이 나오는 경우에

   Distinct를 제거하는 기능이다.

EXPLAIN PLAN FOR
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;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     1 |    22 |     3  (34)| 00:00:01 |
|   1 |  NESTED LOOPS                |            |     1 |    22 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT |     1 |     7 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | DEPT_ID_PK |     1 |       |     0   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| LOCATION   |    23 |   345 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | LOC_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("D"."DEPARTMENT_ID"=10)
   5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

-- Location 쪽의 Unique 컬럼이 Select절에 없음에도 불구하고 Sort Unique 혹은 Hash Unique가 사라짐
-- Id3과 Id5를 확인해보면 모두 INDEX UNIQUE SCAN을 사용하였기 때문에 결과는 한건 혹은 0건임을 보장한다.
-- 이기능은 DE가 아니다. DE의 파라미터로 Control 할 수 없고 수행로직이 다르기 때문이다.

EXPLAIN PLAN FOR
SELECT /*+ FULL(D) */  -- 힌트로 INDEX UNIQUE SCAN 아닌 TABLE ACCESS FULL 유도
       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;


SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 
--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |     1 |    22 |     6  (17)| 00:00:01 |
|   1 |  HASH UNIQUE                  |            |     1 |    22 |     6  (17)| 00:00:01 |
|   2 |   NESTED LOOPS                |            |       |       |            |          |
|   3 |    NESTED LOOPS               |            |     1 |    22 |     5   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL         | DEPARTMENT |     1 |     7 |     4   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | LOC_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| LOCATION   |    23 |   345 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter("D"."DEPARTMENT_ID"=10)
   5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

■ 제약사항
-- Unique Index를 사용해야한다. 즉, Unique 컬럼이 없더라도 Unique Scan의 여부에 따라 DE 와 DEUI로 구분된다.

■ 원인 없는 결과는 없다.
-- 서브쿼리가 Unnesting 되어 Driving 집합이 되면 메인쿼리의 집합을 보존하기 위해 Default로 Distinct가 추가된다.
   이때 DE나 DEUI 기능이 수행된다면 Distinct가 제거된다.
-- 서브쿼리 Unnesting : 서브쿼리를 인라인뷰로 바꾸어 정상적인 조인으로 만드는 기능이다.

■ Control 파라미터를 이용한 제어

ALTER SESSION SET "_optimizer_distinct_elimination" = TRUE;

-- DEUI 기능을 Control 할 수 없고 Oracle 10.2.0.4 에서도 수행되지만 Control 파라미터가 없다.

The Logical Optimizer  (bysql.net 2011년 2차 스터디)
작성자: 김범석 (darkbeom)
최초작성일: 2011년 9월 17일
본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^