2.B Part 2를 마무리 하며

Early Filter Subquery(SubQuery Pushing

Early Filter Subquery는 SQL이 바뀐 것이 아니기 때문에 엄밀히 쿼리변환이라고 볼 수 없다.
SubQuery Pushing에 의해 조인순서만 바뀐 것.

Part 2의 핵심

1. 검증된 RULE을 적용하여 SQL을 재작성하는 것. 그리고 그결과 성능이 향상된다.
2. 1항을 적용하되 하드파싱을 최소화하는 것.

모든 변환에 대해서 코스트를 구하는 것은 오히려 비용이 더 들 수 있다.
(Logical Optimizer의 60% Heuristic이다. Physical Optimizer는 대부분 Cost Base이다.)
3. SVM과 LV, Subquery와 관련된 HQT는 part2의 핵심이다.

JE의 기능강화

No	약어	발생상황	관련장	사용가능버전
1	JE	Primary Key-Foreign Key 관계를 이용한 Join시 발생함	2.2장	10gR2 이후
2	EJE	Primary Key-Foreign Key 관계를 이용한 ANSI Join시 발생함	2.24장 이후	11gR1 이후
3	EJE	Primary Key-Foreign Key 관계를 이용한 (Semi/Anti) Join시 발생함	2.24장	11gR1 이후
4	JESU	Primary Key-Foreign Key 관계를 이용한 Simple Self Join시 발생함	2.25장	11GR2 이후
5	JESU	Primary Key-Foreign Key 관계를 이용한 Self Join에서 양쪽 집합의 조건절이 병합이 가능할 때 발생됨	2.25장	11GR2 이후
6	OE	Unique Index를 이용한 Outer Join시 발생함	2.3장	11GR1 이후
7	JBE	Bitmap Join Index를 이용하여 테이블 액세스를 방지할 수 있을 경우 발생됨	3.2장	9iR1 이후

1~6번은 HQT이지만 7번은 CBQT이다.

Part 2가 중요한 이유

1. Part 2는 Part 3의 기초가 된다.
2. Part 2를 모르면 SQL과 Plan을 보고서도 왜 그런 Plan이 나왔는지 이해할 수 없다.

추가주문

HQT의 원리와 한계를 인식하고 응용하여 튜닝에 적용시킨다.
HQT가 마땅히 수행되어야 함에도 그렇지 못할 때 10053 Trace등을 이용하여 원인을 밝힌다.

문제SQL


select /*+ GATHER_PLAN_STATISTICS  */ j.*
  from job_history j
 where (j.employee_id, j.department_id) in (select e.employee_id, e.department_id
                                              from employees e, departments d
                                             where e.department_id = d.department_id
                                               and d.location_id = 1800);
=============
Plan
=============
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Starts | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|   1 |  VIEW                            | VM_NWVW_2         |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|   2 |   HASH UNIQUE                    |                   |      1 |      1 |00:00:00.01 |       7 |   747K|   747K|  401K (0)|
|   3 |    NESTED LOOPS                  |                   |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|   4 |     NESTED LOOPS                 |                   |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|   5 |      NESTED LOOPS                |                   |      1 |      2 |00:00:00.01 |       4 |       |       |          |
|   6 |       TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  7 |        INDEX RANGE SCAN          | DEPT_LOCATION_IX  |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|   8 |       TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |      1 |      2 |00:00:00.01 |       2 |       |       |          |
|*  9 |        INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |      1 |      2 |00:00:00.01 |       1 |       |       |          |
|* 10 |      INDEX RANGE SCAN            | JHIST_EMPLOYEE_IX |      2 |      1 |00:00:00.01 |       2 |       |       |          |
|* 11 |     TABLE ACCESS BY INDEX ROWID  | JOB_HISTORY       |      1 |      1 |00:00:00.01 |       1 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------

select /*+ GATHER_PLAN_STATISTICS / j.
from job_history j
where (j.employee_id, j.department_id) in (select e.employee_id, d.department_id
from employees e, departments d
where e.department_id = d.department_id
and d.location_id = 1800);

답안 Plan


----------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Starts | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                     |      1 |      1 |00:00:00.01 |      10 |      1 |
|   1 |  NESTED LOOPS                  |                     |      1 |      1 |00:00:00.01 |      10 |      1 |
|   2 |   NESTED LOOPS                 |                     |      1 |      2 |00:00:00.01 |       8 |      1 |
|   3 |    NESTED LOOPS                |                     |      1 |      1 |00:00:00.01 |       6 |      1 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENTS         |      1 |      1 |00:00:00.01 |       3 |      0 |
|*  5 |      INDEX RANGE SCAN          | DEPT_LOCATION_IX    |      1 |      1 |00:00:00.01 |       2 |      0 |
|   6 |     TABLE ACCESS BY INDEX ROWID| JOB_HISTORY         |      1 |      1 |00:00:00.01 |       3 |      1 |
|*  7 |      INDEX RANGE SCAN          | JHIST_DEPARTMENT_IX |      1 |      1 |00:00:00.01 |       2 |      1 |
|*  8 |    INDEX RANGE SCAN            | EMP_DEPARTMENT_IX   |      1 |      2 |00:00:00.01 |       2 |      0 |
|*  9 |   TABLE ACCESS BY INDEX ROWID  | EMPLOYEES           |      2 |      1 |00:00:00.01 |       2 |      0 |
----------------------------------------------------------------------------------------------------------------

무노동 무비용의 원칙

부하를 없앨수 있는 가장 좋은 방법은 부하를 일으키는 작업을 수행하지 않는 것이다.
또다른 예제를 확인해 보자

환경 구성 및 문제 SQL



-- 직원구분코드 생성
alter table employees add (emp_kind varchar2(1) default 1 not null);

-- 직원구분코드 update
update employees
   set emp_kind = case when mod(employee_id,2) = 1 then 1 else 2 end;
   
commit;

-- 정규직 테이블 연락처 생성
create table employee_kind1 as 
select employee_id, phone_number || to_char(rownum) home_phone_number
  from employees
 where emp_kind='1';
 
-- 정규직 테이블 pk생성
alter table employee_kind1 add constraints pk_employee_kind1 primary key (employee_id) using index;

exec dbms_stats.gather_table_stats('hr', 'employee_kind1');

-- 직원 / 정규직 조회 뷰 생성
create view v_emp as 
select e.employee_id, e.first_name, e.last_name, e.emp_kind
     , k1.home_phone_number
  from employees e, employee_kind1 k1
 where e.employee_id = k1.employee_id;

select /*+ GATHER_PLAN_STATISTICS  */ e.home_phone_number
  from v_emp e
 where e.employee_id = '121';

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |      1 |      1 |00:00:00.01 |       3 |
|   1 |  NESTED LOOPS                |                   |      1 |      1 |00:00:00.01 |       3 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEE_KIND1    |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX UNIQUE SCAN         | PK_EMPLOYEE_KIND1 |      1 |      1 |00:00:00.01 |       1 |
|*  4 |   INDEX UNIQUE SCAN          | EMP_EMP_ID_PK     |      1 |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$F5BB74E1
   2 - SEL$F5BB74E1 / K1@SEL$2
   3 - SEL$F5BB74E1 / K1@SEL$2
   4 - SEL$F5BB74E1 / E@SEL$2
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("K1"."EMPLOYEE_ID"=121)
   4 - access("E"."EMPLOYEE_ID"=121)

-- 직원 / 정규직 조회 뷰 변경
create or replace view v_emp as
select k1.employee_id, e.first_name, e.last_name, e.emp_kind
, k1.home_phone_number
from employees e, employee_kind1 k1
where e.employee_id = k1.employee_id;

alter table employee_kind1 add constraint fk_employee_kind1 foreign key (employee_id) references hr.employees(employee_id);

답안 Plan


select /*+ GATHER_PLAN_STATISTICS  */ e.home_phone_number
  from v_emp e
 where e.employee_id = '121';

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |      1 |      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE_KIND1    |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMPLOYEE_KIND1 |      1 |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$3F757C8F / K1@SEL$2
   2 - SEL$3F757C8F / K1@SEL$2
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("K1"."EMPLOYEE_ID"=121)

FK는 성는을 저하시킨다?

명확하게 JE가 발생시킬 수 있도록 유도하여 성능을 향상시킬 방안을 찾아야 한다.

공통코드 마스터와 디테일로 구성된 공통코드 조인뷰 제공 시
고객 및 고객연락처로 나누어진 테이블들을 이용하여 고객 연락처 뷰를 제공 시
기타 무궁무진..