행위

"조건절 push"의 두 판 사이의 차이

DB CAFE
1번째 줄: 1번째 줄:
−
조건절 Pushing
+
= PUSH JOIN PREDICATE =
 +
== 관련파라미터 ==
 +
# _PUSH_JOIN_PREDICATE
 +
: Join Predicate를 View 안으로 push 하는 기능을 결정.
 +
# View 를 사용하는 쿼리에 우선적으로 Complex View Merging 시도
 +
# View Merging 실패시 Join Predicate Pushing 시도
 +
:# 뷰머징이 불가능한 뷰
 +
## SET Operation( UNION,UNION ALL,INTERSECT,MINUS)
 +
## CONNECT BY 절 , ROWNUM 가상컬럼
 +
## SELECT절에 있는 집합함수 (AVG,COUNT,MAX,MIN,SUM)
 +
<source lang=sql>
 +
-- system
 +
alter system set "_push_join_predicate" = true|false;
 +
-- session
 +
alter session set "_push_join_predicate" = true|false;
 +
</source>
 +
 
 +
== 조건절 Pushing ==
  
 
- 뷰를 참조하는 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 Pushing하는 기능
 
- 뷰를 참조하는 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 Pushing하는 기능
 +
(옵티마이저가 뷰 Merging에 실패했을 때 2차적으로 시도)
  
−
(옵티마이저가 어떤 이유에서 뷰 Merging에 실패했을 때 2차적으로 시도)
+
=== 조건절 (Predicate) Pushdown ===
−
 
 
−
 
 
−
 
 
−
조건절 (Predicate) Pushdown
 
−
 
 
 
- 쿼리 블록 밖에 있는 조건들을 쿼리 블록 안쪽으로 밀어 넣음
 
- 쿼리 블록 밖에 있는 조건들을 쿼리 블록 안쪽으로 밀어 넣음
−
 
+
<source lang=sql>
 
SELECT DEPTNO, AVG_SAL
 
SELECT DEPTNO, AVG_SAL
 
   FROM (SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO) A
 
   FROM (SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO) A
 
WHERE DETPNO = 30;
 
WHERE DETPNO = 30;
 
Execution Plan
 
Execution Plan
−
 
+
-------------------------------------------------------------------------------
−
--------------------------------------------------------------------------------
 
−
 
 
 
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=7)
 
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=7)
−
 
 
   1    0  SORT (GROUP BY NOSORT) (Cost=2 Card=1 Bytes=7)
 
   1    0  SORT (GROUP BY NOSORT) (Cost=2 Card=1 Bytes=7)
−
 
 
   2    1    TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
 
   2    1    TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
−
 
 
   3    2      INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
 
   3    2      INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
−
 
+
</source>
 
▶ DEPTNO = 30 조건이 EMP 테이블에도 적용
 
▶ DEPTNO = 30 조건이 EMP 테이블에도 적용
  
  
−
 
+
<source lang=sql>
 
SELECT /*+ no_merge (a) */
 
SELECT /*+ no_merge (a) */
 
  B.DEPTNO, B.DNAME, A.AVG_SAL
 
  B.DEPTNO, B.DNAME, A.AVG_SAL
36번째 줄: 44번째 줄:
 
     AND DETPNO = 30;
 
     AND DETPNO = 30;
 
Execution Plan
 
Execution Plan
−
 
 
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
−
 
 
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=28)
 
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=28)
−
 
 
   1    0  NESTED LOOPS (Cost=3 Card=1 Bytes=28)
 
   1    0  NESTED LOOPS (Cost=3 Card=1 Bytes=28)
−
 
 
   2    1    TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=1 Card=1 Bytes=13)
 
   2    1    TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=1 Card=1 Bytes=13)
−
 
 
   3    2      INDEX (UNIQUE SCAN) OF 'DEPT_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
 
   3    2      INDEX (UNIQUE SCAN) OF 'DEPT_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
−
 
 
   4    1    VIEW (Cost=2 Card=1 Bytes=15)
 
   4    1    VIEW (Cost=2 Card=1 Bytes=15)
−
 
 
   5    4      SORT (GROUP BY) (Cost=2 Card=1 Bytes=7)
 
   5    4      SORT (GROUP BY) (Cost=2 Card=1 Bytes=7)
−
 
 
   6    5        TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
 
   6    5        TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
−
 
 
   7    6          INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
 
   7    6          INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
 +
</source>
  
  
−
 
+
=== 조건절 (Predicate) Pullup ===
−
조건절 (Predicate) Pullup
 
−
 
 
 
- 쿼리 블록 안에 있는 조건들을 쿼리 블록 바깥 쪽으로 끄집어 냄
 
- 쿼리 블록 안에 있는 조건들을 쿼리 블록 바깥 쪽으로 끄집어 냄
−
 
 
(다시 다른 쿼리 블록 안으로 Pushdown 하는데 활용)
 
(다시 다른 쿼리 블록 안으로 Pushdown 하는데 활용)
−
 
+
<source lang=sql>
 
SELECT * FROM
 
SELECT * FROM
 
(SELECT DEPTNO, AVG(SAL) FROM EMP WHERE DEPTNO = 10 GROUP BY DEPTNO) E1,
 
(SELECT DEPTNO, AVG(SAL) FROM EMP WHERE DEPTNO = 10 GROUP BY DEPTNO) E1,
71번째 줄: 68번째 줄:
  
 
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
−
 
 
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1 Bytes=65)
 
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1 Bytes=65)
−
 
 
   1    0  HASH JOIN (Cost=5 Card=1 Bytes=65)
 
   1    0  HASH JOIN (Cost=5 Card=1 Bytes=65)
−
 
 
   2    1    VIEW (Cost=2 Card=1 Bytes=26)
 
   2    1    VIEW (Cost=2 Card=1 Bytes=26)
−
 
 
   3    2      HASH (GROUP BY) (Cost=2 Card=1 Bytes=7)
 
   3    2      HASH (GROUP BY) (Cost=2 Card=1 Bytes=7)
−
 
 
   4    3        TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
 
   4    3        TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
−
 
 
   5    4          INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
 
   5    4          INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
−
 
 
   6    1    VIEW (Cost=2 Card=1 Bytes=39)
 
   6    1    VIEW (Cost=2 Card=1 Bytes=39)
−
 
 
   7    6      HASH (GROUP BY) (Cost=2 Card=1 Bytes=7)
 
   7    6      HASH (GROUP BY) (Cost=2 Card=1 Bytes=7)
−
 
 
   8    7        TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
 
   8    7        TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
−
 
 
   9    8          INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
 
   9    8          INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
−
 
+
</source>
  
  
99번째 줄: 86번째 줄:
  
 
* 조건절 Pushdown의 일종이지만 Pushdown하는 조건절이 상수 조건이 아닌 동적이라는 점에서 다름
 
* 조건절 Pushdown의 일종이지만 Pushdown하는 조건절이 상수 조건이 아닌 동적이라는 점에서 다름
−
 
+
<source lang=sql>
 
SELECT /*+ no_merge(e) push_pred(e) */ *
 
SELECT /*+ no_merge(e) push_pred(e) */ *
 
   FROM DEPT D
 
   FROM DEPT D
−
          LEFT OUTER JOIN (SELECT EMPNO, ENAME, DEPTNO FROM EMP) E
+
  LEFT OUTER JOIN (SELECT EMPNO, ENAME, DEPTNO FROM EMP) E
−
ON E.DEPTNO = D.DEPTNO
+
              ON E.DEPTNO = D.DEPTNO
 
WHERE D.LOC = 'CHICAGO';
 
WHERE D.LOC = 'CHICAGO';
 
-- DEPT 테이블에 존재하는 레코드만 group by
 
-- DEPT 테이블에 존재하는 레코드만 group by
109번째 줄: 96번째 줄:
  
 
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
−
 
 
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=4 Bytes=220)
 
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=4 Bytes=220)
−
 
 
   1    0  NESTED LOOPS (OUTER) (Cost=5 Card=4 Bytes=220)
 
   1    0  NESTED LOOPS (OUTER) (Cost=5 Card=4 Bytes=220)
−
 
 
   2    1    TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Bytes=20)
 
   2    1    TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Bytes=20)
−
 
 
   3    1    VIEW PUSHED PREDICATE (Cost=2 Card=1 Bytes=35)
 
   3    1    VIEW PUSHED PREDICATE (Cost=2 Card=1 Bytes=35)
−
 
 
   4    3      TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=80)
 
   4    3      TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=80)
−
 
 
   5    4        INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
 
   5    4        INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
−
 
+
</source>
  
  
127번째 줄: 108번째 줄:
  
 
① push_pred : JOIN 조건 Pushdown을 유도
 
① push_pred : JOIN 조건 Pushdown을 유도
−
 
 
② no_push_pred : JOIN 조건 Pushdown을 방지
 
② no_push_pred : JOIN 조건 Pushdown을 방지
  
 
[[category:oracle]]
 
[[category:oracle]]

2021년 11월 12일 (금) 10:53 판

thumb_up 추천메뉴 바로가기

1 PUSH JOIN PREDICATE[편집]

1.1 관련파라미터[편집]

  1. _PUSH_JOIN_PREDICATE
Join Predicate를 View 안으로 push 하는 기능을 결정.
  1. View 를 사용하는 쿼리에 우선적으로 Complex View Merging 시도
  2. View Merging 실패시 Join Predicate Pushing 시도
  1. 뷰머징이 불가능한 뷰
    1. SET Operation( UNION,UNION ALL,INTERSECT,MINUS)
    2. CONNECT BY 절 , ROWNUM 가상컬럼
    3. SELECT절에 있는 집합함수 (AVG,COUNT,MAX,MIN,SUM)
-- system
alter system set "_push_join_predicate" = true|false;
-- session
alter session set "_push_join_predicate" = true|false;

1.2 조건절 Pushing[편집]

- 뷰를 참조하는 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 Pushing하는 기능 (옵티마이저가 뷰 Merging에 실패했을 때 2차적으로 시도)

1.2.1 조건절 (Predicate) Pushdown[편집]

- 쿼리 블록 밖에 있는 조건들을 쿼리 블록 안쪽으로 밀어 넣음 

SELECT DEPTNO, AVG_SAL
  FROM (SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO) A
WHERE DETPNO = 30;
Execution Plan
-------------------------------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=7)
  1    0   SORT (GROUP BY NOSORT) (Cost=2 Card=1 Bytes=7)
  2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
  3    2       INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)

▶ DEPTNO = 30 조건이 EMP 테이블에도 적용 


SELECT /*+ no_merge (a) */
 B.DEPTNO, B.DNAME, A.AVG_SAL
  FROM (SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO) A
WHERE A.DEPTNO = B.DETPNO 
    AND DETPNO = 30;
Execution Plan
--------------------------------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=28)
  1    0   NESTED LOOPS (Cost=3 Card=1 Bytes=28)
  2    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=1 Card=1 Bytes=13)
  3    2       INDEX (UNIQUE SCAN) OF 'DEPT_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
  4    1     VIEW (Cost=2 Card=1 Bytes=15)
  5    4       SORT (GROUP BY) (Cost=2 Card=1 Bytes=7)
  6    5         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
  7    6           INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)


1.2.2 조건절 (Predicate) Pullup[편집]

- 쿼리 블록 안에 있는 조건들을 쿼리 블록 바깥 쪽으로 끄집어 냄 (다시 다른 쿼리 블록 안으로 Pushdown 하는데 활용) 

SELECT * FROM
(SELECT DEPTNO, AVG(SAL) FROM EMP WHERE DEPTNO = 10 GROUP BY DEPTNO) E1,
(SELECT DEPTNO, MIN(SAL) FROM EMP GROUP BY DEPTNO) E2
WHERE E1.DEPTNO = E2.DEPTNO;
-- DEPTNO = 10 조건을 Pullup 한 다음 E2 블록 안으로 다시 Pushdown
Execution Plan

--------------------------------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1 Bytes=65)
  1    0   HASH JOIN (Cost=5 Card=1 Bytes=65)
  2    1     VIEW (Cost=2 Card=1 Bytes=26)
  3    2       HASH (GROUP BY) (Cost=2 Card=1 Bytes=7)
  4    3         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
  5    4           INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
  6    1     VIEW (Cost=2 Card=1 Bytes=39)
  7    6       HASH (GROUP BY) (Cost=2 Card=1 Bytes=7)
  8    7         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
  9    8           INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)


JOIN 조건 (Predicate) Pushdown

- JOIN 조건절을 뷰 쿼리 블록 안으로 밀어 넣음, NL JOIN 수행 중에 드라이빙 테이블에서 읽은 JOIN 컬럼 값을 Inner 쪽 뷰 쿼리 블록 내에서 참조할 수 있도록 하는 기능

  • 조건절 Pushdown의 일종이지만 Pushdown하는 조건절이 상수 조건이 아닌 동적이라는 점에서 다름
SELECT /*+ no_merge(e) push_pred(e) */ *
  FROM DEPT D
  LEFT OUTER JOIN (SELECT EMPNO, ENAME, DEPTNO FROM EMP) E
               ON E.DEPTNO = D.DEPTNO
WHERE D.LOC = 'CHICAGO';
-- DEPT 테이블에 존재하는 레코드만 group by
Execution Plan

--------------------------------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=4 Bytes=220)
  1    0   NESTED LOOPS (OUTER) (Cost=5 Card=4 Bytes=220)
  2    1     TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Bytes=20)
  3    1     VIEW PUSHED PREDICATE (Cost=2 Card=1 Bytes=35)
  4    3       TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=80)
  5    4         INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)


  • 관련 힌트

① push_pred : JOIN 조건 Pushdown을 유도 ② no_push_pred : JOIN 조건 Pushdown을 방지

원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=조건절_push&oldid=6416"