2.25 JESL (Join Elimination Using Self Join)

Self Join 사용시에도 JE가 가능하다.

 

JESL 이란?

Oracle11gR2부터 나온 신기능.

Self Join이 발생될 때 Join Elimination이 수행되는 것.

과거의 버전에서는 PK-FK 관계에 의해서만 JE가 발생하였다.

하지만 이제는 FK가 없어도 JE가 발생할 수 있다.

 

 

SELECT e1.employee_id, e1.salary, e1.manager_id

  FROM employee e1

 WHERE e1.salary > 10000

   AND e1.manager_id > 100

   AND e1.employee_id IN (SELECT e2.employee_id

                            FROM employee e2

                           WHERE e2.salary > 9000);

 

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

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

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

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

| 1   |  TABLE ACCESS FULL | EMPLOYEE|    64 |   768 |     3 |  00:00:01 |

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

Predicate Information:

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

1 - filter(("SALARY">10000 AND "MANAGER_ID">100))

 

Employee 테이블을 한번만 Scan 하였다. 뭔가 이상하지 않은가?

위의 SQL을 자세히 보면 메인쿼리의 Where 절에 AND e1.salary > 9000 조건을 추가하면 서브쿼리가 없어질 수 있다. 다시 말하면 SQL이 아래처럼 수정된 것이다.

 

select e2.employee_id, e2.salary, e2.manager_id

from employee e2

where e2.salary  > 10000 and e2.manager_id > 100;

 

메인쿼리 블록의 테이블인 E1이 없어지고 서브쿼리의 테이블인 E2로 대체되었고 E1의 Where 조건들이

추가되었다.

Salary > 9000 조건이 사라진 이유는 이미 salary > 10000 조건이 존재하므로 필요가 없어진 것이다.

 

<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$CAE83587")

      ELIMINATE_JOIN(@"SEL$5DA710D3" "E1"@"SEL$1")

      OUTLINE(@"SEL$5DA710D3")

      UNNEST(@"SEL$2")

      OUTLINE(@"SEL$1")

      OUTLINE(@"SEL$2")

      FULL(@"SEL$CAE83587" "E2"@"SEL$2")

    END_OUTLINE_DATA

*/

 

메이쿼리 블록의 테이블인 E1쪽을 JE 기능을 이용하여 제거한 것을 알 수 있다.

 

<10053 Trace 분석>

 

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

Cost-Based Subquery Unnesting

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

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

Subquery removal for query block SEL$2 (#2)

RSW: Not valid for subquery removal SEL$2 (#2)

Subquery unchanged.

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 ANY subquery to a join.

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

 

먼저 서브쿼리 Unnesting이 발생하였다.

JESJ 기능을 사용하려면 서브쿼리가 반드시 Unnesting 되어야 한다.

서브쿼리가 조인으로 바뀐 이후에 JE (Join Elimination) 기능이 수행되기 때문이다.

 

<JESJ에 대한 Trace 내용>

 

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

Join Elimination (JE)   

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

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

SELECT "E1"."EMPLOYEE_ID" "EMPLOYEE_ID","E1"."SALARY" "SALARY","E1"."MANAGER_ID" "MANAGER_ID" FROM "TLO"."EMPLOYEE" "E2","TLO"."EMPLOYEE" "E1" WHERE "E1"."SALARY">10000 AND "E1"."MANAGER_ID">100 AND "E1"."EMPLOYEE_ID"="E2"."EMPLOYEE_ID" AND "E2"."SALARY">9000

JE:   cfro: EMPLOYEE objn:73953 col#:1 dfro:EMPLOYEE dcol#:1

JE:   cfro: EMPLOYEE objn:73953 col#:1 dfro:EMPLOYEE dcol#:1

JE:   cfro: EMPLOYEE objn:73953 col#:1 dfro:EMPLOYEE dcol#:1

JE:   cfro: EMPLOYEE objn:73953 col#:1 dfro:EMPLOYEE dcol#:1

Query block (0x2b9788a90ea8) before join elimination:

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

SELECT "E1"."EMPLOYEE_ID" "EMPLOYEE_ID","E1"."SALARY" "SALARY","E1"."MANAGER_ID" "MANAGER_ID" FROM "TLO"."EMPLOYEE" "E2","TLO"."EMPLOYEE" "E1" WHERE "E1"."SALARY">10000 AND "E1"."MANAGER_ID">100 AND "E2"."EMPLOYEE_ID"="E1"."EMPLOYEE_ID" AND "E2"."SALARY">9000

JE:   eliminate table: EMPLOYEE (E1)

JE:   Replaced column: E1.MANAGER_ID with column: E2.MANAGER_ID

JE:   Replaced column: E1.SALARY with column: E2.SALARY

JE:   Replaced column: E1.EMPLOYEE_ID with column: E2.EMPLOYEE_ID

JE:   Replaced column: E1.SALARY with column: E2.SALARY

JE:   Replaced column: E1.MANAGER_ID with column: E2.MANAGER_ID

Registered qb: SEL$CAE83587 0x88a90ea8 (JOIN REMOVED FROM QUERY BLOCK SEL$5DA710D3; SEL$5DA710D3; "E1"@"SEL$1")

 

JE가 발생하여 E1이 제거

E1쪽 컬럼인 Manager_id가 E2.Manager_id로 대체

Salary와 Employee_id도 대체

E1에 대하여 JESS가 수행되어 새로운 쿼리블럭명 SEL$CAE83587이 생성

 

 

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

SELECT "E2"."EMPLOYEE_ID" "EMPLOYEE_ID","E2"."SALARY" "SALARY","E2"."MANAGER_ID" "MANAGER_ID" FROM "TLO"."EMPLOYEE" "E2" WHERE "E2"."SALARY">10000 AND "E2"."MANAGER_ID">100 AND "E2"."SALARY">9000

Query block SEL$CAE83587 (#1) simplified

 

위의 10053 Trace는 변환이 완료된 SQL를 보여준다. 하지만 아직 불필요한 조건인 E2.SALARY > 9000 이 남아

있다.

 

10053 Trace의 Predicate Information을 보면 E2.SALARY > 9000 조건이 삭제된 것으로 확인.

 

Predicate Information:

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

1 - filter(("SALARY">10000 AND "MANAGER_ID">100))

 

Salary > 9000 and salary > 10000 조건에서 and 때문에 salary > 9000 조건은 필요없게 된다.

이때 불필요한 조건은 삭제되는데 이 기능을 Filter Subsumption이라 한다.

 

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

Number of join permutations tried: 1

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

Trying or-Expansion on query block SEL$CAE83587 (#1)

Transfer Optimizer annotations for query block SEL$CAE83587 (#1)

id=0 frofand predicate="E2"."SALARY">10000 AND "E2"."MANAGER_ID">100

Final cost for query block SEL$CAE83587 (#1) - All Rows Plan:

 

Frofand 부분을 보면 Filter Subsumption이 발생된 것을 알 수 있다.

이 기능은 특이하겠도 Physical Optimization 과정에서 진행된다.

 

 

JESJ를 컨트롤 하는 파라미터 : _optimizer_join_elimination_enabled

 

<주의사항>

JESJ는 메인쿼리와 서브쿼리 관계에서만 동작하는 것이 아니다.

단순 셀프조인에서도 수행된다. 또한 조회 조건이 전혀 없는 경우에도 같은 테이블끼리 조인이 발생된다면 이 기능은 수행된다. 이것을 Simple JESJ라 한다.

 

이번 장에서 소개한 예제에 대하여 정확히 말하면 JESJ using Filter Subsumption이다.

그 이유는 테이블은 같지만 서로 다른 집합에 대하여 조회 조건을 통합하였기 때문이다.