메뉴 건너뛰기

bysql.net

2.2_JE(Join_Elimination)

2011.09.21 02:35

AskZZang 조회 수:1570

2.2. JE (Join Elimination)

직접 사용하지 않는 테이블은 SQL에서 삭제하라!

 

잘못된 상식)

           FK를 사용하면 느려진다.

           JE 기능이 나오기 전까지는 그러했다. 하지만 이제는 FK 적용만으로 속도가 항상 느려지는 것이 아니다.

           오히려 상황에 따라 성능이 향상 되는 경우도 많이 있다.

 

 

오라클 옵티마이저는 부모와 자식테이블을 조인하고 SELECT 절에 자식 쪽 컬럼만 나영하는 경우 부모와 자식간에 FK를 만들어 놓으면 부모 테이블을 액세스 하지 않는다. 왜냐하면 FK가 자식 테이블에 존재하는 값이 100% 부모 테이블에도 존재한다는 것을 보장하기 때문이다.

 

따라서 부모 쪽 테이블과 조인을 제거하여 I/O 와 조인부하를 획기적으로 감소시킬 수 있다.

이때 옵티마이저가 SQL을 새로 작성(FROM 절의 부모 쪽 테이블을 제거, WHERE 절의 부모와 자식간 조인절 제거) 하는데 이 기능을 JE 기능이라 한다. (Oracle 10gR2에서 소개)

 

JE가 발생하는 경우.

 

SELECT /*+ GATHER_PLAN_STATISTICS LEADING(e) USE_NL(d) */

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

  FROM employee e, department d

 WHERE e.department_id = d.department_id

   AND e.job_id = 'SH_CLERK';

 

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

| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |   A-Time | Buffers |

|   0 | SELECT STATEMENT   |          |      1 |       |     20 |00:00:00.01 |      68 |

|   1 |  NESTED LOOPS     |           |      1 |     20 |    20 |00:00:00.01 |      68 |

|*  2 |   TABLE ACCESS FULL| EMPLOYEE |      1 |     20 |    20 |00:00:00.01 |      6 |

|*  3 |   TABLE ACCESS FULL| DEPARTMENT |     20 |   1 |     20 |00:00:00.01 |      62 |

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

Predicate Information (identified by operation id):

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

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

   3 - filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

 

위에서 보는 것처럼 JE가 발생하지 않았다.

 

FK를 만들고 다시 수행

 

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

| Id  | Operation        | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT  |        |      1 |        |       |     4 (100)|        |     20 |00:00:00.01 |      6 |

|*  1 |  TABLE ACCESS FULL| EMPLOYEE |           1 |     20 |  1720 |     4 (0)| 00:00:01 |     20 |00:00:00.01 |      6 |

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

 

 

예상한대로 JE 기능이 훌륭히 수행되어 DEPARTMENT 쪽을 액세스 하는 Operation이 사라졌다.

 

여기서 한가지 흥미로운 것은 오라클이 JE 기능을 사용하기 위해 ELIMINATE_JOIN 힌트를 사용한다는 것.

 

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

      ELIMINATE_JOIN(@"SEL$1" "D"@"SEL$1")

      OUTLINE(@"SEL$1")

      FULL(@"SEL$F7859CDE" "E"@"SEL$1")

      END_OUTLINE_DATA

*/

 

FK 가 존재하더라도 NO_ELIMINATE_JOIN 힌트를 사용하거나 _optimizer_join_elimination_enabled 파라미터를

False로 적용할 경우 JE 기능을 사용할 수 없다.

 

JE 기능의 제약사항)

첫번째, FK Multi Column일 경우 기능이 수행되지 않는다.

두번째, Equal 조인 이외에 Range Join 등을 조인절에 사용하면 JE 기능이 수행되지 않는다.

세번째, Department 테이블의 PK를 제외한 컬럼을 Select 절에서 사용하면 JE 기능을 사용할 수 없다.

 

주의사항)

Oracle10g 에서는 JE가 발생할 수 있는 상황에서 ANSI SQL을 사용하면 안된다.

ANSI SQL에서의 JE기능은 Oracle11g 부터 가능하기 때문이다.