SQLP

[SQLP] 쿼리 변환

songsua 2024. 8. 19. 21:49

 

1. OR-Expansion 

or 조건을 분해해서 union-all 형태로 변환해주는 기능

/*+Use_concat*/ : or조건을 분해하여 union-all 로 변환시킨다

/*+No_expand*/ : or expansion을 방지한다.

##or  /*+No_expand*/ =  /*+INLIST ITERATOR*/ 를 사용한 예  
explain plan for
select * from orders where row_id = 31806 
or row_id = 34000;
select * from table(dbms_xplan.dispaly());

Plan hash value: 1010959076
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |     2 |   454 |     5   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR                     |                   |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS            |     2 |   454 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IDX_ORDERS_ROW_ID |     2 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("ROW_ID"=31806 OR "ROW_ID"=34000)


##union all /*+Use_concat*/ 을 사용한 예
explain plan for
select * from orders where row_id = 31806
union all
select * from orders where row_id = 34000;

Plan hash value: 757051961
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |     2 |   454 |     4   (0)| 00:00:01 |
|   1 |  UNION-ALL                           |                   |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS            |     1 |   227 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IDX_ORDERS_ROW_ID |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS            |     1 |   227 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | IDX_ORDERS_ROW_ID |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("ROW_ID"=31806)

or cost 는 5 

union all 은  4

union all 이 이득이다. 

row_id 가 등가로 인한 선택도가 매우 낮기 때문에, index 를 사용하여 합치는 것이 cost 가 적게 든다.

 

2. Subquery Unnesting

subquey unnesting 은 서브쿼리가 사용된 sql 명령문을 join 문법으로 변형시켜서 실행한다.

--sub unnesting 사용
explain plan for
select * from orders where order_id in
(select order_id from returns);
--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |  2404 |   568K|   486   (1)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI|         |  2404 |   568K|   486   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | RETURNS |  1173 | 17595 |     5   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL  | ORDERS  | 51290 |    11M|   480   (1)| 00:00:01 |
--------------------------------------------------------------------------------
##Hash join이 사용된 것을 알 수 있다.


--hash join을 사용하지 않게 변형
explain plan for
select * from orders where order_id in
(select /*+no_unnest*/order_id from returns);

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     2 |   454 |   236K  (1)| 00:00:10 |
|*  1 |  FILTER            |         |       |       |            |          |
|   2 |   TABLE ACCESS FULL| ORDERS  | 51290 |    11M|   480   (1)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| RETURNS |     1 |    15 |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------

 

3. View merging 

쿼리문을 블럭화 하기 위해 서브쿼리, 인라인 뷰 등을 자주 사용한다.

오라클 옵티마이저가 인라인 뷰, 서브쿼리를 해석할 때 독자적으로 실행하지 않고 메인쿼리와 함께 실행되는 경우,

즉 쿼리 블록을 풀어서 기존 쿼리와 함께 최적화를 수행하는 것을 뷰 머징(VIEW MERGING)이라 한다.

 

옵티마이저는 뷰 머징 전후, 두 개의 실행계획을 비교하여 비용이 적은 SQL 명령문을 실행하게 된다.

옵티마이저 입장에서는 뷰는 매우 불편한 sql 명령문을 만들어내기 때문에 되도록 뷰 머징을 수행할려고 한다.

뷰 머징을 수행해보고 안된다면 조건절 pushing 을 수행한다.

예시)

Postal_code 랑 last_name, first_name 을 주목

Employee 테이블에서는 Departments테이블과 Department_id 로 조인

Locations 테이블에서는  Departments테이블과 Location_id 로 조인을 수행할거다.

 

select E.first_name, E.last_name, dept_locs_v.street_address, dept_locs_v.postal_code
from employees E, ( select d.department_id, d.department_name, l.street_address, l.postal_code from departments d, locations l where d.location_id = l.location_id) dept_locs_v
where dept_locs_v.department_id = E.department_id and E.last_name = 'Smith';



==========================================================================================
Lindsey	Smith	Magdalen Centre, The Oxford Science Park	OX9 9ZB
William	Smith	Magdalen Centre, The Oxford Science Park	OX9 9ZB



explain plan for
select E.first_name, E.last_name, dept_locs_v.street_address, dept_locs_v.postal_code
from employees E, ( select d.department_id, d.department_name, l.street_address, l.postal_code from departments d, locations l where d.location_id = l.location_id) dept_locs_v
where dept_locs_v.department_id = E.department_id and E.last_name = 'Smith';

Plan hash value: 1618608772
 
----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |     1 |    56 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |             |       |       |            |          |
|   2 |   NESTED LOOPS                 |             |     1 |    56 |     4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |             |     1 |    25 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     1 |    18 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | EMP_NAME_IX |     1 |       |     1   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |     7 |     1   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|*  8 |    INDEX UNIQUE SCAN           | LOC_ID_PK   |     1 |       |     0   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY INDEX ROWID  | LOCATIONS   |     1 |    31 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

3번의 조인이 발생했다.

위는 인라인 뷰를 사용한 조인문이며, 만약 인라인뷰 없이 쿼리를 작성한다면 cost는 동일하게 나올 까?

 

위의 쿼리와는 다르고 값을 동일하게 추출해 봤을 때,

explain plan for
select E.first_name, E.last_name, l.street_address, l.postal_code
from employees E, departments d, locations l
where d.department_id = E.department_id and d.location_id = l.location_id
and E.last_name = 'Smith';


| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |     1 |    56 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |             |       |       |            |          |
|   2 |   NESTED LOOPS                 |             |     1 |    56 |     4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |             |     1 |    25 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     1 |    18 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | EMP_NAME_IX |     1 |       |     1   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |     7 |     1   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|*  8 |    INDEX UNIQUE SCAN           | LOC_ID_PK   |     1 |       |     0   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY INDEX ROWID  | LOCATIONS   |     1 |    31 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

코스트는 동일하게 나오나, 사람 관점으로 봤을 때는 인라인뷰가 더 보기 편할 수도 있다.

뷰머징도 동일한 관점으로 보는것...

 

 

-- 뷰머징이 없을 경우는 어떻게 될 것인가?

** from 인라인뷰 절에 넣는다

explain plan for
select E.first_name, E.last_name, dept_locs_v.street_address, dept_locs_v.postal_code
from employees E, ( select /*+no_merge*/ d.department_id, d.department_name, l.street_address, l.postal_code from departments d, locations l where d.location_id = l.location_id) dept_locs_v
where dept_locs_v.department_id = E.department_id and E.last_name = 'Smith';

---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |     1 |    61 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN                     |                  |     1 |    61 |     8  (25)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | EMPLOYEES        |     1 |    18 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN            | EMP_NAME_IX      |     1 |       |     1   (0)| 00:00:01 |
|   4 |   VIEW                         |                  |    27 |  1161 |     6  (34)| 00:00:01 |
|   5 |    MERGE JOIN                  |                  |    27 |  1026 |     6  (34)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| LOCATIONS        |    23 |   713 |     2   (0)| 00:00:01 |
|   7 |      INDEX FULL SCAN           | LOC_ID_PK        |    23 |       |     1   (0)| 00:00:01 |
|*  8 |     SORT JOIN                  |                  |    27 |   189 |     4  (50)| 00:00:01 |
|   9 |      VIEW                      | index$_join$_003 |    27 |   189 |     3  (34)| 00:00:01 |
|* 10 |       HASH JOIN                |                  |       |       |            |          |
|  11 |        INDEX FAST FULL SCAN    | DEPT_ID_PK       |    27 |   189 |     1   (0)| 00:00:01 |
|  12 |        INDEX FAST FULL SCAN    | DEPT_LOCATION_IX |    27 |   189 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

 

cost 값이 뷰머징을 사용했을 경우 4 이며, 사용안할 경우 8 이 나온 것을 알 수 있다.

뷰머징을 사용하는 것이 이득이다.

 

뷰머지가 불가능한 조건

  • 집합 연산자(union, union all, intersect, minus)
  • connect by 절
  • RowNum pseudo 컬럼
  • select-list에 집계함수 사용 (group by없이 전체를 집계하는 경우를 말함)
  • 분석 함수

'SQLP' 카테고리의 다른 글

[SQLP] 서브쿼리와 성능  (0) 2024.08.21
[SQLP] 쿼리 변환2  (0) 2024.08.20
[SQLP] 데이터베이스 call 과 네트워크 부하 ★★★★★  (0) 2024.08.17
[SQLP] 테이블 스페이스  (0) 2024.08.15
[SQLP] 커서 공유  (0) 2024.08.15

'SQLP'의 다른글

  • 현재글[SQLP] 쿼리 변환

관련글

티스토리툴바