1. 개념 ● Join Elimination(조인 제거)은 실제로 사용되지 않는 테이블에 대한 조인을 자동으로 제거 하여 SQL 성능을 향상시키는 기능.
● 주로 부모-자식 관계(FK)가 있는 테이블에서 사용되며, 자식 테이블에만 접근해도 충분할 경우 부모 테이블 액세스를 제거 함.
2. 적용 예시 ● 아래처럼 부모 테이블 PARENT와 자식 테이블 CHILD이 조인되어 있으나, 실제 조회는 CHILD만 사용한다면:
SQL Copy
SELECT CHILD . COL1 , CHILD . COL2
FROM PARENT , CHILD
WHERE PARENT . COL1 = CHILD . COL1 ;
SELECT CHILD . COL1 , CHILD . COL2
FROM CHILD
WHERE CHILD . COL1 IS NOT NULL ; 3. 조건 JE 기능이 정상 작동하려면 다음 조건들을 만족해야 함:
— FK 존재 : 자식 테이블에 부모를 참조하는 외래키(FK)가 정의되어 있어야 함
○ Range, LIKE, BETWEEN, IN 등이 있을 경우 JE 미적용
— PK 외 컬럼 사용 제한 : 부모 테이블의 PK 외의 컬럼을 SELECT 하면 JE 적용 불가
— Multi-Column FK : FK가 복합 컬럼일 경우에도 JE 미적용
19c의 경우 멀티 컬럼 fk 여도 je 동작함
_optimizer_multicol_join_elimination = true 파라미터 영향, false로 변경시 10g와 동일하게 동작
SQL Copy
CREATE TABLE tab_parent (
col1 NUMBER ,
col2 NUMBER ,
col3 VARCHAR2 ( 20 ),
CONSTRAINT tab_parent_pk PRIMARY KEY ( col1 , col2 )
);
CREATE TABLE tab_child (
col1 NUMBER ,
col2 NUMBER ,
col4 VARCHAR2 ( 20 ),
CONSTRAINT tab_child_fk FOREIGN KEY ( col1 , col2 ) REFERENCES tab_parent ( col1 , col2 )
);
INSERT INTO tab_parent VALUES ( 1 , 10 , 'parent_1' );
INSERT INTO tab_parent VALUES ( 2 , 20 , 'parent_2' );
INSERT INTO tab_child VALUES ( 1 , 10 , 'child_1' );
INSERT INTO tab_child VALUES ( 2 , 20 , 'child_2' );
COMMIT ;
SELECT
c . col1 , c . col4
FROM tab_child c , tab_parent p
WHERE c . col1 = p . col1
AND c . col2 = p . col2 ;
JE :[ V2 ] Considering Join Elimination on query block SEL$1 (# 0 )
*************************
Join Elimination [ JE : R 12.2 ]
*************************
SQL :******* UNPARSED QUERY IS *******
SELECT "C" . "COL1" "COL1" , "C" . "COL4" "COL4" FROM "IMSI" . "TAB_CHILD" "C" , "IMSI" . "TAB_PARENT" "P" WHERE "C" . "COL1" = "P" . "COL1" AND "C" . "COL2" = "P" . "COL2"
JE :[ V2 ] Query block (0x7f5da61a8f60) before join elimination :
SQL :******* UNPARSED QUERY IS *******
SELECT "C" . "COL1" "COL1" , "C" . "COL4" "COL4" FROM "IMSI" . "TAB_CHILD" "C" , "IMSI" . "TAB_PARENT" "P" WHERE "C" . "COL1" = "P" . "COL1" AND "C" . "COL2" = "P" . "COL2"
JE :[ V2 ]: Try to eliminate P by ref . join elim using PRIMARY ( COL1 , COL2 ) <- FOREIGN ( COL1 , COL2 )
JE :[ V2 ]: Can eliminate P by ref . join elim using PRIMARY ( COL1 , COL2 ) <- FOREIGN ( COL1 , COL2 )
JE :[ V2 ] Eliminate table : TAB_PARENT ( P )
JE :[ V2 ] Query block (0x7f5da61a8f60) after join elimination :
SQL :******* UNPARSED QUERY IS *******
SELECT "C" . "COL1" "COL1" , "C" . "COL4" "COL4" FROM "IMSI" . "TAB_CHILD" "C" WHERE "C" . "COL1" IS NOT NULL AND "C" . "COL2" IS NOT NULL
Registered qb : SEL$A43D1678 0xa61a8f60 ( JOIN REMOVED FROM QUERY BLOCK SEL$1 ; SEL$1 ; "P" @ "SEL$1" )
QUERY BLOCK SIGNATURE
signature (): qb_name = SEL$A43D1678 nbfros = 1 flg = 0
fro ( 0 ): flg = 0 objn = 75035 hint_alias = "C" @ "SEL$1"
============
Plan Table
============
| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | | | | 3 | |
| 1 | TABLE ACCESS FULL | TAB_CHILD | 2 | 76 | 3 | 00 : 00 : 01 |
alter session set "_optimizer_multicol_join_elimination" = false ;
alter session set statistics_level = all ;
SELECT c . col1 , c . col4
FROM tab_child c , tab_parent p
WHERE c . col1 = p . col1
and c . col2 = p . col2 ;
SELECT * FROM DBMS_XPLAN . DISPLAY_CURSOR ( NULL , NULL , 'ADVANCED ALLSTATS LAST' );
| Id | Operation | Name | Starts | E - Rows | E - Bytes | Cost (% CPU )| E - Time | A - Rows | A - Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | | 3 ( 100 )| | 2 | 00 : 00 : 00.01 | 9 |
| 1 | NESTED LOOPS | | 1 | 2 | 128 | 3 ( 0 )| 00 : 00 : 01 | 2 | 00 : 00 : 00.01 | 9 |
| 2 | TABLE ACCESS FULL | TAB_CHILD | 1 | 2 | 76 | 3 ( 0 )| 00 : 00 : 01 | 2 | 00 : 00 : 00.01 | 7 |
|* 3 | INDEX UNIQUE SCAN | TAB_PARENT_PK | 2 | 1 | 26 | 0 ( 0 )| | 2 | 00 : 00 : 00.01 | 2 |
Outline Data
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE ( '19.1.0' )
DB_VERSION ( '19.1.0' )
OPT_PARAM ( '_optimizer_multicol_join_elimination' ' fa — ANSI JOIN 불가 (Oracle 10g) : Oracle 10g에서는 ANSI JOIN 구문( JOIN ... ON)을 사용하면 JE 미적용
○ ANSI JOIN 사용 시 JE는 Oracle 11g부터 가능
4. 옵티마이저 힌트 관련 ● Oracle은 내부적으로 ELIMINATE_JOIN 힌트 를 통해 JE를 수행함
● NO_ELIMINATE_JOIN 힌트를 사용하면 JE 기능 비활성화
● _optimizer_join_elimination_enabled = FALSE 파라미터로도 JE 기능 끌 수 있음
5. 결론 ● 사용하지 않는 테이블은 제거하거나, FK를 정의하여 옵티마이저가 자동으로 제거하도록 유도 하는 것이 성능 향상의 핵심
● JE 조건을 만족하지 못하는 경우, SQL이나 데이터 모델을 개선할 필요가 있음
● 불필요한 조인 제거 = 불필요한 I/O 제거 = 성능 향상
아직 댓글이 없습니다. 이 글에서 얻은 생각이나 질문을 남겨보세요.