• 조건절이행
  
• Transitive Predicate Generation, Transitive Closure
• (a=b) 이고 (b=c) 이면 (a=c) 이다 는 추론을 통해 새로운 조건절을 내부적으로 생성해주는 쿼리변환

SQL> select * from dept d, emp e
 2  where e.job='MANAGER'
 3  and e.deptno=10
 4  and d.deptno=e.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 568005898

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    58 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    58 |     5   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | EMP     |     1 |    38 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("D"."DEPTNO"=10)
  4 - filter("E"."JOB"='MANAGER' AND "E"."DEPTNO"=10)

• d.deptno=e.deptno 조인조건 제거되고 쿼리형태는 아래와같이 변환되어 수행

select * from dept d, emp e

where e.job=’MANAGER’
and e.deptno=10
and d.deptno=10

조건절 제거를 방지하려면 조건절을 가공


SQL> select * from dept d, emp e
 2  where e.job='MANAGER'
 3  and e.deptno=10
 4  and d.deptno=e.deptno+0;

Execution Plan
----------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    58 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |     1 |    58 |     5   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMP     |     1 |    38 |     4   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - filter("E"."JOB"='MANAGER' AND "E"."DEPTNO"=10)
  4 - access("D"."DEPTNO"="E"."DEPTNO"+0)

조건절 이행이 효과를 발휘하는 사례

drop table hwi_order;

create table hwi_order (
   pno    varchar2(2) not null,
   sdate    varchar2(8) not null
);

create index ix_hwi_order_1 on hwi_order (pno,sdate);

drop table hwi_phis;

create table hwi_phis (
   pno    varchar2(2) not null,
   sfdate    varchar2(8) not null,
   stdate    varchar2(8) not null
);

create index ix_hwi_phis_1 on hwi_phis (pno,sfdate,stdate)

상품주문 (hwi_order)과 선분이력형태의 상품이력(hwi_phis)을 가진 table

선분이력으로 설계된 상품이력(hwi_phis) 테이블을 between 조인으로 조인하는 쿼리

select /*+ use_hash (b) */ *
from hwi_phis a, hwi_order b
where b.sdate between '20090101' and '200090131'
and a.pno=b.pno
and b.sdate between a.sfdate and a.stdate;

∴ 판매일자( ‘20090101’ and ‘20090131’) 에 맞는

상품 이력(and b.sdate between a.sfdate and a.stdate) 을 보여줌

• 각 상품별로 등록시점(a.sfdate)이 다르고 속성이 바뀌어 이력 레코드가 쌓인 시점도 다름
• 이력의 경우 현사용자가 원하는 기간의 이력만 읽으면 되므로 공통점을 찾아보면
• 시작일자 (a.sfdate) 는 ‘20090131’ 보다 작고
  종료일자 (a.stdate) 는 ‘20090101’ 보다 큼
• 상품이력.시작일자<=’20090131’
  상품이력.종료일자>=’20090101’

Execution Plan
----------------------------------------------------------
Plan hash value: 523941226

--------------------------------------------------------------------------------------
| Id  | Operation           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                |     1 |    24 |     0   (0)|          |
|*  1 |  FILTER             |                |       |       |            |          |
|*  2 |   HASH JOIN         |                |     1 |    24 |     4  (25)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| HWI_PHIS       |     1 |    15 |     2   (0)| 00:00:01 |
|*  4 |    INDEX FULL SCAN  | IX_HWI_ORDER_1 |     1 |     9 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter(NULL IS NOT NULL)
  2 - access("A"."PNO"="B"."PNO")
      filter("B"."SDATE">="A"."SFDATE" AND "B"."SDATE"<="A"."STDATE")
  3 - filter("A"."STDATE">='20090101' AND "A"."SFDATE"<='200090131')
  4 - access("B"."SDATE">='20090101' AND "B"."SDATE"<='200090131')
      filter("B"."SDATE">='20090101' AND "B"."SDATE"<='200090131')

• 8i 이전의 경우 3과같은 쿼리 변환이 나타나지 않아 조건절을 삽입해야하며
• 9i 이상부터는 쿼리변환이 일어남

튜닝 사례1

drop table hwi_ipadd;

create table hwi_ipadd (
   ip    varchar2(20) not null,
   sip    varchar2(20) not null,
   eip    varchar2(20) not null
);

alter table hwi_ipadd add constraint pk_hwi_ipadd (ip);

create index ix_hwi_ipadd1 on hwi_ipadd (sip);

SQL> select * from hwi_ipadd
where sip>='192.168.000.001'
and eip<='192.168.000.255'
;
 2    3    4
no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2664741610

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    36 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| HWI_IPADD     |     1 |    36 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_HWI_IPADD1 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("EIP"<='192.168.000.255')
  2 - access("SIP">='192.168.000.001')

※ 자료수가 약 2000만개라고 가정하면 index range scan 후의 access가 대량 발생함
ix_hwi_ipadd1에 eip를 추가한다고 하여도 크기 증가로 인해 스캔량이 늘어날수있음
교제의 예의 경우 32분이상 걸림

튜닝)
ip주소목록의 경우 종료 IP주소(eip)가 시작 IP주소(sip)보다 크다 는 사실을 적용

1. 시작IP주소<=종료IP주소

->
2. :strIpAddr <=시작IP주소 <= 종료IP주소 <= :endIpAddr


시작IP주소와 종료IP주소 컬럼 기준으로 분해하면
->

3. WHERE 시작ip주소 BETWEEEN :strtIpaddr and :endIpAddr
     and 종료IP주소 BETWEEN :strtIpAddr and :endIpAddr

결론

select * from hwi_ipadd
where sip>='192.168.000.001'
and eip<='192.168.000.255'
and sip<=eip;SQL>   2    3    4

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2664741610

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    36 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| HWI_IPADD     |     1 |    36 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_HWI_IPADD1 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("EIP"<='192.168.000.255' AND "SIP"<="EIP" AND "EIP">='192.168.000.001')
  2 - access("SIP">='192.168.000.001' AND "SIP"<='192.168.000.255')


※ 직접 모두 풀어준후 where절에 기술하거나 종료IP주소가 시작IP주소보다 크다는 사실만 기술하여도 됨
교제의 예의 경우 튜닝후 0.01초만에 수행

튜닝 사례2

drop table hwi_cus;

create table hwi_cus (
   cno    varchar2(6) not null
);

alter table hwi_cus add constraint pk_hwi_cus primary key (cno);

drop table hwi_order;

create table hwi_order (
   odate    varchar2(8)    not null,
   ono    varchar2(4)    not null,
   cno    varchar2(6) not null,
   addr    varchar2(100)
);

alter table hwi_order add constraint pk_hwi_order primary key (odate,ono,cno);

drop table hwi_order_d;

create table hwi_order_d (
   odate    varchar2(8)    not null,
   ono    varchar2(4)    not null,
   cno    varchar2(6)    not null,
   pno    varchar2(2)    not null,
   pamt    varchar2(10)    not null
);


alter table hwi_order_d add constraint pk_hwi_order_d primary key (odate,ono,cno,pno);

조건절이행 발생?

select /*+ ordered use_nl(o) use_nl(d) index(o) index(d) */ *
from hwi_cus c, hwi_order o, hwi_order_d d
where d.cno=c.cno
and   d.cno=o.cno
and   d.odate=o.odate
and   d.ono=o.ono
and   d.odate=to_char(sysdate,'yyyymmdd')
;
SQL>   2    3    4    5    6    7    8
no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2196757687

-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |     1 |    97 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |                |       |       |            |          |
|   2 |   NESTED LOOPS                 |                |     1 |    97 |     3   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |                |     1 |    72 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN            | PK_HWI_CUS     |     1 |     5 |     1   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID| HWI_ORDER      |     1 |    67 |     1   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN          | PK_HWI_ORDER   |     1 |       |     1   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN            | PK_HWI_ORDER_D |     1 |       |     1   (0)| 00:00:01 |
|   8 |   TABLE ACCESS BY INDEX ROWID  | HWI_ORDER_D    |     1 |    25 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  6 - access("O"."ODATE"=TO_CHAR(SYSDATE@!,'yyyymmdd'))
  7 - access("D"."ODATE"=TO_CHAR(SYSDATE@!,'yyyymmdd') AND "D"."ONO"="O"."ONO" AND
             "D"."CNO"="O"."CNO")
      filter("D"."CNO"="C"."CNO")

※ 주문일자에 대한 조건이 전이되었음을 확인하였지만
ordered 힌트에 따라 고객 c->주문 o->주문상세순 d 으로 NL조인 되어지어
고객번호를 연결조건으로 활용하지 못함( d.cno=c.cno and   d.cno=o.cno)
으로 조인문 자체가 전이되지 않음을 보여줌

select /*+ ordered use_nl(o) use_nl(d) index(o) index(d) */ *
from hwi_cus c, hwi_order o, hwi_order_d d
where o.cno=c.cno
and   d.cno=o.cno
and   d.odate=o.odate
and   d.ono=o.ono
and   d.odate=to_char(sysdate,'yyyymmdd')
;
SQL>   2    3    4    5    6    7    8
no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2196757687

-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |     1 |    97 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |                |       |       |            |          |
|   2 |   NESTED LOOPS                 |                |     1 |    97 |     3   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |                |     1 |    72 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN            | PK_HWI_CUS     |     1 |     5 |     1   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID| HWI_ORDER      |     1 |    67 |     1   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN          | PK_HWI_ORDER   |     1 |       |     1   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN            | PK_HWI_ORDER_D |     1 |       |     1   (0)| 00:00:01 |
|   8 |   TABLE ACCESS BY INDEX ROWID  | HWI_ORDER_D    |     1 |    25 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  6 - access("O"."ODATE"=TO_CHAR(SYSDATE@!,'yyyymmdd') AND "O"."CNO"="C"."CNO")
      filter("O"."CNO"="C"."CNO")
  7 - access("D"."ODATE"=TO_CHAR(SYSDATE@!,'yyyymmdd') AND "D"."ONO"="O"."ONO" AND
             "D"."CNO"="O"."CNO")



※ o.cno=c.cno and d.cno=o.cno 로 기술하여 조인순서를 반영하여 줌으로써 cno를 활용

• 결론
• 조인조건은 상수와 변수처럼 전이되지 않음
• 최적의 조인순서를 결정하고 그 순서에 따라 조인문을 기술해 주는 것이 중요

• 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
• 작성자: 남송휘 (tofriend)
• 최초작성일: 2011년 5월 29일
• 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
• 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^