SQLP
[SQLP] 쿼리 변환2
songsua
2024. 8. 20. 00:31
1. 복합 View merging
조건절과 조인문만을 포함하는 단순 뷰는 항상 뷰 머징이 일어난다.
뷰 내에 group by 절이 사용되거나(집약하는 역할 등) 뷰 내에 select 절에 distinct 연산자를 사용한 경우,
뷰는 시스템 설정에 따라 뷰 머징이 일어나거나 일어나지 않는다.
※_complex_view_metging 파라미터의 기본 값이 true 이면 북합 뷰 머징이 일어난다.
※ merge 힌트 사용 시 복합 뷰 머징이 일어난다.
예)
create view emp_sum as select deptno, sum(sal) as sum
from emp group by deptno;
explain plan for
select d.deptno, e.sum
from emp_sum e, dept d
where e.deptno = d.deptno
and d.loc = 'DALLAS';
select * from table(dbms_xplan.display);
Plan hash value: 860764689
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 7 (29)| 00:00:01 |
| 1 | MERGE JOIN | | 1 | 37 | 7 (29)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 3 | 78 | 5 (40)| 00:00:01 |
| 5 | VIEW | EMP_SUM | 3 | 78 | 4 (25)| 00:00:01 |
| 6 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
##뷰머징이 안일어나고 최적화를 한다.
##group by 의 힘인가?
##merge 힌트 사용하기
create view emp_sum3 as
select /*+merge*/deptno, sum(sal) as sum
from emp group by deptno;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 81 | 7 (29)| 00:00:01 |
| 1 | HASH GROUP BY | | 3 | 81 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN | | 5 | 135 | 6 (17)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 14 | 98 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
2. 복합 View merging이 일어나지 않는 경우
- 집합 연산자 사용
- connect by 절
- rownum 가상 컬럼 사용
- select 에 집계함수의 사용
- 윈도우 함수 사용
예)
##기존 실행계획
create view cust_test as select /*+merge*/sum(s.quantity_sold)total,s.cust_id, s.prod_id
from sales s
group by s.cust_id, s.prod_id;
explain plan for
select c.cust_id, c.cust_first_name, c.cust_last_name, c.cust_email
from customers c, products p, cust_test t
where c.cust_id = t.cust_id
and t.total > 100
and t.prod_id = p.prod_id
and p.prod_name = 'External 6X CD-ROM';
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 648 | 68688 | 831 (1)| 00:00:10 | | |
|* 1 | FILTER | | | | | | | |
| 2 | HASH GROUP BY | | 648 | 68688 | 831 (1)| 00:00:10 | | |
|* 3 | HASH JOIN | | 12941 | 1339K| 830 (1)| 00:00:10 | | |
| 4 | NESTED LOOPS | | | | | | | |
| 5 | NESTED LOOPS | | 12941 | 682K| 422 (0)| 00:00:06 | | |
|* 6 | TABLE ACCESS FULL | PRODUCTS | 1 | 42 | 3 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE ALL | | | | | | 1 | 28 |
| 8 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 9 | BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX | | | | | 1 | 28 |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 12762 | 149K| 422 (0)| 00:00:06 | 1 | 1 |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 2818K| 407 (1)| 00:00:05 | | |
------------------------------------------------------------------------------------------------------------------------
nownum 을 추가하여 view을 생성한다면?
create view cust_test as select /*+merge*/sum(s.quantity_sold)total,s.cust_id, s.prod_id
from sales s
where rownum<4
group by s.cust_id, s.prod_id;
explain plan for
select c.cust_id, c.cust_first_name, c.cust_last_name, c.cust_email
from customers c, products p, cust_test t
where c.cust_id = t.cust_id
and t.total > 100
and t.prod_id = p.prod_id
and p.prod_name = 'External 6X CD-ROM';
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 | | 3084 (3)| 00:00:38 | | |
| 1 | NESTED LOOPS | | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 92 | | 3084 (3)| 00:00:38 | | |
| 3 | NESTED LOOPS | | 1 | 52 | | 3083 (3)| 00:00:37 | | |
|* 4 | VIEW | CUST_TEST | 3 | 66 | | 3080 (3)| 00:00:37 | | |
| 5 | HASH GROUP BY | | 3 | 36 | 21M| 3080 (3)| 00:00:37 | | |
|* 6 | COUNT STOPKEY | | | | | | | | |
| 7 | PARTITION RANGE ALL | | 918K| 10M| | 498 (4)| 00:00:06 | 1 | 28 |
| 8 | TABLE ACCESS FULL | SALES | 918K| 10M| | 498 (4)| 00:00:06 | 1 | 28 |
|* 9 | TABLE ACCESS BY INDEX ROWID| PRODUCTS | 1 | 30 | | 1 (0)| 00:00:01 | | |
|* 10 | INDEX UNIQUE SCAN | PRODUCTS_PK | 1 | | | 0 (0)| 00:00:01 | | |
|* 11 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | | 0 (0)| 00:00:01 | | |
| 12 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 40 | | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------view 가 사용된 것을 볼 수 있다.
-nownum으로 인한 view merging 이 사용이 안되고 있다.
3. 조건절 pushing
-조건절 pushdown : 뷰를 참조하는 쿼리블록의 조건절을 뷰 쿼리블록 안으로 pushing 하는 기능
-조건절 pullup : 쿼리 블럭안에 있는 조건들을 쿼리 블록 밖으로 꺼내온다
-조인조건 push down : NL조인 수행 중에 드라이빙 테이블에서 읽은 값을 inner 뷰 쿼리 블록 안으로 밀어 넣는 것을 의미한다.
push down 은 NL 조인을 전제로 하므로 성능이 더 나빠질 수 있는데, 이에 대한 힌트는
/*+push_pred*/ , /*+no_push_pred*/ 를 제공한다.
예)
조건절 push down 의 경우
create index idx_emp_deptno on emp(deptno);
explain plan for
select deptno, avg_sal
from (select /*+no_merge index(emp idx_emp_deptno)*/ deptno, avg(sal)avg_sal from emp group by deptno ) a
where deptno = 30;
select * from table(dbms_xplan.display)
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 2 | SORT GROUP BY NOSORT | | 1 | 7 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 35 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_EMP_DEPTNO | 5 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPTNO"=30)위의 xplan을 보면 view 을 위로 하고 index scan 이 하위에 있는 것을 알 수 있다.
이는 view 안에서 index tange scan 이 일어났다고 볼 수 있으며, 부의 쿼리 블럭안에서 행해졌다고 볼 수 있다.
--> 노머징일 때(거의 노머징일때만 발생한다), 옵티마이저는 이처럼 사용할 경우 성능상은 좋을 수 있다.
조인조건 push down
select * from table(dbms_xplan.display)
explain plan for
select /*+no_merge(e) push_pred(e) index(e inx_emp_deptno)*/*
from (select empno, ename,deptno from emp)e, dept d
where e.deptno(+) = d.deptno
and d.loc = 'chicago';
Plan hash value: 1099394308
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 212 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 4 | 212 | 5 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | DEPT | 1 | 20 | 3 (0)| 00:00:01 |
| 3 | VIEW PUSHED PREDICATE | | 1 | 33 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 65 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_EMP_DEPTNO | 5 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("D"."LOC"='chicago')
5 - access("DEPTNO"="D"."DEPTNO")
- DEPT 테이블을 읽고 (Id 2)
- 인덱스 스캔으로 EMP 테이블의 행을 찾고 (Id 5)
- 인덱스를 통해 실제 EMP 테이블의 행을 읽음 (Id 4)
- 조인 조건을 서브쿼리 수준에서 처리 (Id 3)
- DEPT와 EMP 데이터를 결합 (Id 1)
- 최종 결과 반환 (Id 0)
Id 3의 "VIEW PUSHED PREDICATE"는, 쿼리에서 dept.d 테이블의 LOC = 'chicago' 조건을 emp 테이블에 관련된 서브쿼리로 "밀어 넣은" 것
즉, 이 조건을 EMP 테이블을 조회하기 전에 미리 적용하여 데이터 양을 줄이려고 한 것일 수 도..