2.24 EJE (Enhanced JE)

Semi / Anti 조인과 ANSI Style로 조인할 경우도 JE가 가능하다.

 

10g까지 semi/anti 조인과 ansi style join을 사용할 경우 JE 기능을 사용할 수 없었다.

하지만 11g부터는 JE가 사용가능 하다.

 

_always_semi_join 파라미터를 Default 값인 Choose로 한다.

ALTER SESSION SET "_always_semi_join" = CHOOSE;

 

아래의 SQL은 Semi Join이 수행되어야만 JE가 발생하기 때문이다.

 

SELECT e.employee_id, e.first_name, e.last_name, e.email, e.salary

  FROM employee e

 WHERE EXISTS (SELECT 1

                 FROM department d

                WHERE d.department_id = e.department_id)

   AND e.job_id = 'SH_CLERK';

 

 

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

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

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

| 0   | SELECT STATEMENT             |           |       |       |     2 |           |

| 1   |  TABLE ACCESS BY INDEX ROWID | EMPLOYEE  |    20 |  1040 |     2 |  00:00:01 |

| 2   |   INDEX RANGE SCAN           | EMP_JOB_IX|    20 |       |     1 |  00:00:01 |

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

 

Predicate Information:

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

1 - filter("E"."DEPARTMENT_ID" IS NOT NULL)

2 - access("E"."JOB_ID"='SH_CLERK')

 

/*+

    BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.1.0.6')

      DB_VERSION('11.1.0.6')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$BB28CCCD")

      ELIMINATE_JOIN(@"SEL$5DA710D3" "D"@"SEL$2")

      OUTLINE(@"SEL$5DA710D3")

      UNNEST(@"SEL$2")

      OUTLINE(@"SEL$1")

      OUTLINE(@"SEL$2")

      INDEX_RS_ASC(@"SEL$BB28CCCD" "E"@"SEL$1" ("EMPLOYEE"."JOB_ID"))

    END_OUTLINE_DATA

  */

 

Plan 상으로 보면 서브쿼리쪽의 Operation이 사라졌으므로 EJE가 정상적으로 수행되었다.

한가지 알아두어야 할 사항이 있다. JE 기능이 수행되려면 내부적으로 보면 중간단계가 하나 더 필요하다.

서브쿼리를 조인으로 바꾸어야만 JE가 수행될 수 있기 때문이다.

 

 

<10053 Trace 분석>

 

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

Cost-Based Subquery Unnesting

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

SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.

Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.

SU: Considering subquery unnest on query block SEL$1 (#1).

SU:   Checking validity of unnesting subquery SEL$2 (#2)

SU:   Passed validity checks.

SU:   Transforming EXISTS subquery to a join.

Registered qb: SEL$5DA710D3 0xd511e1c (SUBQUERY UNNEST SEL$1; SEL$2)

 

ð  Exists 서브쿼리가 조인으로 바뀌었음을 알 수 있다. 그 과정에서 쿼리블럭 SEL$5DA710D3가 생성 되었다.

 

 

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

Join Elimination (JE)   

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

JE:   cfro: EMPLOYEE objn:71188 col#:11 dfro:DEPARTMENT dcol#:11

Query block (07AE4924) before join elimination:

SQL:******* UNPARSED QUERY IS *******

SELECT "E"."EMPLOYEE_ID" "EMPLOYEE_ID","E"."FIRST_NAME" "FIRST_NAME","E"."LAST_NAME" "LAST_NAME","E"."EMAIL" "EMAIL","E"."SALARY" "SALARY" FROM "TLO"."DEPARTMENT" "D","TLO"."EMPLOYEE" "E" WHERE "E"."JOB_ID"='SH_CLERK' AND "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"

JE:   eliminate table: DEPARTMENT

Registered qb: SEL$BB28CCCD 0x7ae4924 (JOIN REMOVED FROM QUERY BLOCK SEL$5DA710D3; SEL$5DA710D3; "D"@"SEL$2")

 

ð   JE 과정에서 Department 테이블이 제거 되었음을 알 수 있다. 또한 원본 SQL에서 Exists를 Not Exists로 바꾸어 Anti Join이 수행되는 경우에도 JE가 똑같이 발생한다.

 

ANSI SQL 문법을 사용하는 경우

 

SELECT e.employee_id, e.email, e.salary

  FROM employee e JOIN department d

    ON (e.department_id = d.department_id)

 WHERE e.job_id = 'SH_CLERK';

 

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

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

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

| 0   | SELECT STATEMENT             |           |       |       |     2 |           |

| 1   |  TABLE ACCESS BY INDEX ROWID | EMPLOYEE  |    20 |  1040 |     2 |  00:00:01 |

| 2   |   INDEX RANGE SCAN           | EMP_JOB_IX|    20 |       |     1 |  00:00:01 |

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

 

Predicate Information:

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

1 - filter("E"."DEPARTMENT_ID" IS NOT NULL)

2 - access("E"."JOB_ID"='SH_CLERK')

 

Outline Data:

  /*+

    BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.1.0.6')

      DB_VERSION('11.1.0.6')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$BB28CCCD")

      ELIMINATE_JOIN(@"SEL$5DA710D3" "D"@"SEL$2")

      OUTLINE(@"SEL$5DA710D3")

      UNNEST(@"SEL$2")

      OUTLINE(@"SEL$1")

      OUTLINE(@"SEL$2")

      INDEX_RS_ASC(@"SEL$BB28CCCD" "E"@"SEL$1" ("EMPLOYEE"."JOB_ID"))

    END_OUTLINE_DATA

*/

 

Plan 정보와 Outline Data를 분석해보면 ANSI Style을 사용했음에도 Oracle Style Join을 사용한 경우처럼 ELIMINATE_JOIN 힌트를 사용했음을 알 수 있다.

 

 

<10053 Trace 분석>

 

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

Join Elimination (JE)   

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

JE:   cfro: EMPLOYEE objn:71188 col#:11 dfro:DEPARTMENT dcol#:11

Query block (07AE4924) before join elimination:

SQL:******* UNPARSED QUERY IS *******

SELECT "E"."EMPLOYEE_ID" "EMPLOYEE_ID","E"."FIRST_NAME" "FIRST_NAME","E"."LAST_NAME" "LAST_NAME","E"."EMAIL" "EMAIL","E"."SALARY" "SALARY" FROM "TLO"."DEPARTMENT" "D","TLO"."EMPLOYEE" "E" WHERE "E"."JOB_ID"='SH_CLERK' AND "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"

JE:   eliminate table: DEPARTMENT

Registered qb: SEL$BB28CCCD 0x7ae4924 (JOIN REMOVED FROM QUERY BLOCK SEL$5DA710D3; SEL$5DA710D3; "D"@"SEL$2")

 

ð  Trace 정보에도 정상적으로 JE 기능이 작동 했음을 알 수 있다.

JE 기능이 10g에서 처음 발생되었지만 사용 범위가 넓어지고 있음을 알 수 있다.