9. 쿼리 변환

2009.12.28 01:18

헌쓰 조회 수:15080

CHAPTER 9. 쿼리 변환

? 오라클은 내부적으로 더효율적인 방법으로 질의를 변환한다.

- 질의변환은 사용자 질의를 비용이 감소되는 같은 의미의 질의로 바꾸는 작업이다.

- 질의변환은 10가지 정도가 있다(Oracle White Paper, Query Optimization in Oracle Database 10g, 2005)

- 질의변환은 규칙기반(rule based, heuristically driven) 방식이다.

- 10g 부터는 Subquery Unnesting과 View Merging이 비용기반으로 바뀌었다.

시작하면서 254

/박우창

? 실행계획의 FILTER 계획에 대하여 학습한다.

- WHERE 절에 나타나는 Subquery는 FILTER 계획으로 처리한다.

? 예제 - (filter_cost_01.sql)

- 실험테이블 emp - 20,000 튜플가정 : 8KB db_block_size, 1MB extent의 LMT, 수동 segment, 시스템 통계 비활성화

Column	type	값	비고
dept_no	number	1,2,3,4,5,0 반복	6개 부서
sal	number	1..20000
emp_no	number	1..20000
padding	varchar2(60)	x

? 질의 실험 및 관찰

- 예제 질의 /*+ no_unnest*/

select outer.*

from emp outer

where outer.sal > (

                   select /*+ no_unnest */ avg(inner.sal)

                   from emp inner

                   where inner.dept_no = outer.dept_no

)

- 실행계획(10g에서 실험)

------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   167 | 12024 |   168 |
|* 1 | FILTER             |      |       |       |       |
|   2 |   TABLE ACCESS FULL | EMP | 20000 | 1406K|    24 |
|   3 |   SORT AGGREGATE    |      |     1 |     8 |       |
|* 4 |    TABLE ACCESS FULL| EMP | 3333 | 26664 |    24 |
------------------------------------------------------------

- 비용(카디널리티) 비교

오라클 버전

1단계(내부테이블)

ACCESS(FULL)->

SORT(AGGREGATE)

2단계(외부테이블)

ACCESS(FULL)

3단계(SELECT)

FILTER

설명

8.1.7.4

33(3334)

33(1000)

33(1000)

(주1)

9.2.0.6

35(3333)

35(1000)

35035(1000)

(주2),(주3)

10.1.0.4

35(3333)

35(20000)

245(167)

(주4),(주5)

(주1) 8i는 부속질의 수행 비용을 고려하지 않음(같은 테이블이라서 그런지?)

(주2) 9i 이후 _tablescan_cost_plus_one과 INITRANS 설정으로 값 33->35로 증가

(주3) 9i의 35,035=(1,000*35 + 35)로 계산 추정, 1,000은 20,000의 5%로 추정.

(주4) 10g는 245=35+(35*6), 6개 부서를 예측

(주5) 10g는 카디널리티 3,333개 예측(전체의 1/6), 167=20,000의 5% * 1/6

- 모든 버전에서 비용과 카디널리티 예측이 맞지 않다. 평균의 의미를 생각해보면 카디널리티의 경우 1/2로 예측해야한다. 8i는 필터링 비용 예측이 틀리고, 9i는 서브쿼리를 변수 개념으로 처리하고 있으며, 10g는 내부 작동 원리를 감안하지만 부정확한 카디널리티를 계산한다.

진화(Evolution) 258

/박우창

? 질의변환은 버전이 올라갈수록 진화하고 있다.

             - 8i는 필터링 비용 계산 방법을 갖고있지 못한다.
             - 9i는 순환방식으로 서브쿼리에 변수를 제공하는 처리 로직으로 계산한다.
             - 10g는 내부작동 원리를 감안하여 계산한다. 특정 사례에서는 부정확한 사례도 있다.

필터링 258

/박우창

? 질의변환을 통하여 서브쿼리를 제거하는 쪽으로 가기 때문에 필터 연산은 사용하지 않는 추세이다.

- subquery에 대하여 unnest, no_unnest 및 push_subquery, no push_subquery 실험.

? 예제 - (push_subq.sql)

- 실험테이블 스키마

parent(id1,small_vc1,small_vc2,padding)

child(id1,id2,small_vc1,small_vc2,padding)

subtest(id1,small_vc1,small_vc2,padding)

? 질의 실험 및 관찰

- push_subq 힌트는 옵티마이저가 가장 빨리 서브쿼리 조건을 체크하는 실행 계획을 수립한다.

- 예제 질의

select /*+ push_subq */ par.small_vc1, chi.small_vc1

from parent par, child chi

where par.id1 between 100 and 200 and chi.id1 = par.id1

        and exists (

                           select /*+ no_unnest */ null

                           from subtest sub

                           where sub.small_vc1 = par.small_vc1

                           and sub.id1 = par.id1

                           and sub.small_vc2 >= '2')

- 비용(카디널리티) 비교(10g에서 실험, 교과서와 다르게 no_unnest 힌트에 따라 다른 듯!!)

push subquery

/*+ push_subq */

nesting

/*+ no_unnest */

실행계획

consistent gets(cost)

설명

no_unnest

(parent join child) FILTER subtest

829(163)

(주1)

push_subq

no_unnest

(parent join child) FILTER subtest

829(163)

push_subq

(parent join subtest) join child

22(11)

(주2)

(parent join subtest) join child

22(11)

(주1) FILTER를 나중에 하면서 EXIST 조건을 체크

(주2) parent와 subtest를 조인할 때 튜플수를 줄인다. 질의변환 발생

(주3) consistent get = 메모리에서 읽은 block 수

필터 최적화(Filter Optimization) 263

/박우창

? subquery 처리 알고리즘을 살펴본다. 9i부터는 statistics_level=ALL로 설정하고 v$sql_plan_statistics 뷰를 통하여 각 라인이 처리된 횟수를 직접 살펴볼 수있다.

(FILTER 알고리즘)

if this is the first row selected from the driving table

     execute the subquery with this driving value

     retain the driving (input) and return (output) values as 'current values'

     set the 'current values' status to 'not yet stored'.

else

     if the driving value matches the input value from the 'current values'

          return the output value from the 'current values'

     else

          if the status of the 'current values' is 'not yet stored'

               attempt to store the 'current values' in an in-memory hash-table

               if a hash collision occurs

                    discard the 'current values'

               end if

          end if

          probe the hash table using the new driving value

          if the new driving value is in the hash table

               retrieve the stored return value from the in-memory hash table

               retain these values as the 'current values'

               set the 'current values' status to 'previously stored'

          else

               execute the subquery with the new driving value

               retain the driving and return (output) values as 'current values'

               set the 'current values' status to 'not yet stored'.

          end if

          return the output value from the 'current values'

     end if

end if

? 서브쿼리 필터 알고리즘 실험(filter_cost_02.sql)

- FILTER 알고리즘의 작동을 실험한다.

- 실험 데이터 1 :

  main_tab(N,V) = {(1,a),(2,a),(3,a),(4,a)}* 5000=20,000개 튜플

filter_tab(N,V) = {(1,a),(2,a),(3,a),(4,a)}
- create unique index filter_tab_i1 on filter_tab (n);

- 실험 데이터 2 : 실험데이터 1과 비교하여 main_tab 데이터의 내용은 같으나 순서가 다르다.

main_tab(N,V) = (1,a)*5000,,(2,a)*5000,(3,a)*5000,(4,a)*5000= 20,000개 튜플

filter_tab(N,V) = {(1,a),(2,a),(3,a),(4,a)}
- create unique index filter_tab_i1 on filter_tab (n);

- 질의

select count(m.v)

from main_tab m

where exists (

                            select/*+ no_unnest */ v

                            from filter_tab f

                            where f.n=m.n and f.v like 'a%'

);

- 실험결과 : 실험데이터2가 수행속도가 더 좋았다. 필터 알고리즘이 해시테이블을 사용하는지 실험하는 프로그램이다. 선행테이블 값이 서브쿼리에 제공되는 순서에 따라 수행속도가 다르다.

? 서브쿼리 필터 알고리즘 실험- 해시테이블 실험(filter_cost_01a.sql)

- 실험테이블 emp - 20,000 튜플

Column

type

값

비고

dept_no

number

1,2,3,4,5,0 반복, 67

6개 부서

sal

number

1..20000

emp_no

number

1..20000

padding

varchar2(60)

x

- 질의문

select/*+ no_merge(iv) */ count(*)

from (

           select outer.*

           from emp outer

           where outer.sal >

                         (select /*+ no_unnest */ avg(inner.sal)

                          from emp inner

                          where inner.dept_no = outer.dept_no

                          )

         ) iv;

- 첫 번째 실험 수행 시간 : 00:00:00.03

- 두 번째 실험 수행 시간 : 00:00:06.40 (아래 update 문 실행 후)

update emp

set dept_no = 67-- value for 9i

-- set dept_no = 432-- value for 10g

where rownum = 1;

- 결과 설명 : 10g에서 해시테이블 사이즈가 432가 0과 충돌을 일으킨다. 필터알고리즘에 해시테이블의 크기에 영향을 받는다. 또 알고리즘은 선행테이블의 distinct 값, 선행테이블 값이 서브쿼리에 제공되는 순서에 따라 수행속도가 다르다. 오라클은 in_memory 해시 테이블을 과도하게 사용하는 것을 제한한다. 9i 256개, 10g 1024개.

스칼라 서브쿼리 266

/남송휘

select
   count(av_sal)
from (
   select /*+ no_merge */
       outer.dept_no,
       outer.sal,
       outer.emp_no,
       outer.padding,
       (
           select   avg(inner.sal)
          from   emp   inner
           where   inner.dept_no = outer.dept_no
       )                        av_sal
   from   emp   outer
)
where
   sal > av_sal
;

스칼라 서브쿼리: select-list에 중첩해서 (상관) 서브쿼리를 사용하는것 , 단일 값만을 리턴
Select-list에 있는 하나의 컬럼이 되었으나 실제 실행코드는 필터링과 같은 방식으로 작동

COUNT(AV_SAL)
-------------
         9998

9.2.0.4

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=22022 Card=1 Bytes=26)
   1    0   SORT (AGGREGATE)
   2    1     VIEW (Cost=22022 Card=1000 Bytes=26000)
   3    2       FILTER
   4    3         TABLE ACCESS (FULL) OF 'EMP' (Cost=22 Card=1000 Bytes=8000)
   5    3         SORT (AGGREGATE)
   6    5           TABLE ACCESS (FULL) OF 'EMP' (Cost=22 Card=3333 Bytes=26664)

10.2.0.1

--------------------------------------------------------------
| Id | Operation             | Name | Rows | Bytes | Cost |
--------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    26 |    44 |
|   1 | SORT AGGREGATE       |      |     1 |    26 |       |
|   2 |   VIEW                |      | 20000 |   507K|    44 |
|* 3 |    FILTER             |      |       |       |       |
|   4 |     TABLE ACCESS FULL | EMP | 20000 |   156K|    22 |
|   5 |     SORT AGGREGATE    |      |     1 |     8 |       |
|* 6 |      TABLE ACCESS FULL| EMP | 3333 | 26664 |    22 |
--------------------------------------------------------------

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

   3 - filter("OUTER"."SAL"> (SELECT AVG("INNER"."SAL") FROM "EMP"
              "INNER" WHERE "INNER"."DEPT_NO"=:B1))
   6 - filter("INNER"."DEPT_NO"=:B1)

sample code에서 where절을 제거

------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    13 |    22 |
|   1 | SORT AGGREGATE     |      |     1 |    13 |       |
|   2 |   VIEW              |      | 20000 |   253K|    22 |
|   3 |    TABLE ACCESS FULL| EMP | 20000 | 60000 |    22 |
------------------------------------------------------------

scalar_sub_02.sql

create or replace function get_dept_avg(i_dept in number)
return number deterministic
as
   m_av_sal   number;
begin
   select   avg(sal)
   into   m_av_sal
   from   emp
   where   dept_no = i_dept
   ;

   return m_av_sal;

end;
/

function 생성후 쿼리 수행

function 호출을 (select get_dept_avg(dept_no) from dual) av_sal 를 사용하여 수행

-------------------------------------------------------------
| Id | Operation            | Name | Rows | Bytes | Cost |
-------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    26 |    24 |
|   1 | SORT AGGREGATE      |      |     1 |    26 |       |
|   2 |   VIEW               |      | 20000 |   507K|    24 |
|* 3 |    FILTER            |      |       |       |       |
|   4 |     TABLE ACCESS FULL| EMP | 20000 |   156K|    22 |
|   5 |     FAST DUAL        |      |     1 |       |     2 |
-------------------------------------------------------------

select 문에서 직접 수행 get_dept_avg(dept_no)   av_sal

------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    26 |    22 |
|   1 | SORT AGGREGATE     |      |     1 |    26 |       |
|   2 |   VIEW              |      | 1000 | 26000 |    22 |
|* 3 |    TABLE ACCESS FULL| EMP | 1000 | 8000 |    22 |
------------------------------------------------------------

scalar_sub_03.sql

*
create or replace package pack1 as

   g_ct   number(10) := 0;

   function f_n(i in number)   return number;
   function f_v(i in varchar2) return varchar2;

end;
.
/

create or replace package body pack1 as

function f_n(i in number)   return number
is
begin
   pack1.g_ct := pack1.g_ct + 1;
   return i;
end
;

function f_v(i in varchar2) return varchar2
is
begin
   pack1.g_ct := pack1.g_ct + 1;
   return i;
end
;

end;
.
/

두 함수 모두 입력값을 리턴하기 전에 전역변수 값을 1씩 증가시키는 기능을함

create table t1
nologging       -- adjust as necessary
pctfree 10       -- adjust as necessary
pctused 90       -- adjust as necessary
as
/*
with generator as (
   select   --+ materialize
       rownum    id
   from   all_objects
   where   rownum <= 3000
)
*/
select
   /*+ ordered use_nl(v2) */
   rownum           n1,
   lpad(rownum,16,'0')   v16,
   lpad(rownum,32,'0')   v32
from
   generator   v1,
   generator   v2
where
   rownum <= &target
;

insert /*+ append */ into t1
select * from t1;

select
   count(distinct x)
from   (
   select   /*+ no_merge */
       (select pack1.f_n(n1) from dual) x
   from
       t1
   )
;

숫자형일때

9.2.0.4

(select pack1.f_n(n1) from dual) x
Hash table size: 256

(select pack1.f_n(v16) from dual) x
Hash table size: 256

(select pack1.f_n(v32) from dual) x
Hash table size: 256

10.2.1.0

n1
Hash table size: 1024

v16
Hash table size: 1024

v32
Hash table size: 512

※ 9i 는 hash table size가 256
※ 10g 입력값에 따라 차이가 있음

문자형일경우

결과는 모두 동일

COUNT(DISTINCTX)
----------------
           16384

9.2.0.4

(select pack1.f_v(v16) from dual) x
Hash table size: 256

(select pack1.f_v(v32) from dual) x
Hash table size: 256

(select substr(pack1.f_v(v32),1,80) from dual) x
Hash table size: 256

10.2.0.1

COUNT(DISTINCTX)
----------------
           16384

(select pack1.f_v(v16) from dual) x
Hash table size: 16

(select pack1.f_v(v32) from dual) x
Hash table size: 16

(select substr(pack1.f_v(v32),1,80) from dual) x
Hash table size: 256

※ 9i 는 큰변화가 없음
※ 10g 는 입력값 또는 출력 값이 아주 긴경우 실행계획의 변화가 없는데도 더 느리게 동작할수 있음

1) 입력과 출력값의 총크기를 줄임 (substr 사용)
2) 세션 레벨에서 _query_execution_cache_max_size 파라미터 값 변경

서브쿼리 팩토링 274

/이창헌

1. materialize힌트는 옵티마이저가 임시 테이블을 생성하도록 강제하고자 할 때

2. inline힌트는 옵티마이저가 그 이름(with문에 테이블)이 사용된 부분을 정의문으로 대체해서 쿼리를 최적화하도록 강제하고자 할 때 사용

짧은 예화 : 정신 건강을 위해서 관심 있는 분만 참고 하면 좋을 것 같은 내용이라고 생각 합니다.(현재 시간 : 2010년 2월 21일 저녁 6시23분 우리 동네 도서관 close시간 가까워 지고 있습니다. 집에 인터넷이 안됩니다. 그리고 배 고파서 책이 눈에 안들어 옵니다…전 집에 갑니다. 꾸벅) 혹시 운영진분이 요약해서 올리가고 연락 주시면 내일 인터넷 되는 곳에서 정리 해서 다시 올리 겠습니다.

덧글: ㅡㅡ+ 인터넷 설치하면 현금 30만원~~ㅋ

Complex View Merging 281

/이태경

저는 Semi 조인만 하는 줄 알고 그 부분 올린다고 편집 누르면서 스크롤 내리는데 Complex View Merging도 있었네요.
교수님 이건 수요일날 아침까지 올리겠습니다. 미안합니다. ㅡㅡ;

Pushing Predicates 284 /위충환

Pushed Predicate 실행계획의 실제적인 사례를 만들기가 쉽지 않음

☞ - 옵티마이저 제약 이용 : _push_join_predicate

- 조건절을 뷰안으로 Pushing 사례 : 다중 테이블을 포함하는 View와 Outer Join

▼ push_pred.sql

create or replace view v1 as
select
       t2.id1,
       t2.id2,
       t3.small_vc,
       t3.padding
from
       t2, t3
where
   t3.id1 = t2.id1
and t3.id2 = t2.id2

select
       t1.*,
       v1.*
from
       t1,
       v1
where
       t1.n1 = 5
and t1.id1 between 10 and 50
and v1.id1(+) = t1.id1

Execution Plan : 제약조건 설정에 따른 실행계획 결과 (테스트 환경 - 10g2R)

SQL> alter session set "_push_join_predicate"= true;

-------------------------------------------------------------------------
| Id | Operation                       | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |       |     1 |   211 |     6 |
|   1 | NESTED LOOPS OUTER             |       |     1 |   211 |     6 |
|* 2 |   TABLE ACCESS BY INDEX ROWID   | T1    |     1 |   119 |     3 |
|* 3 |    INDEX RANGE SCAN             | T1_PK |    42 |       |     2 |
|   4 |   VIEW PUSHED PREDICATE         | V1    |     1 |    92 |     3 |
|* 5 |    FILTER                       |       |       |       |       |
|   6 |     NESTED LOOPS                |       |     1 |   133 |     3 |
|* 7 |      INDEX RANGE SCAN           | T2_PK |     1 |    13 |     2 |
|   8 |      TABLE ACCESS BY INDEX ROWID| T3    |     1 |   120 |     1 |
|* 9 |       INDEX UNIQUE SCAN         | T3_PK |     1 |       |       |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."N1"=5)
   3 - access("T1"."ID1">=10 AND "T1"."ID1"<=50)
   5 - filter(50>="T1"."ID1" AND 10<="T1"."ID1")
   7 - access("T2"."ID1"="T1"."ID1")
       filter("T2"."ID1">=10 AND "T2"."ID1"<=50)
   9 - access("T3"."ID1"="T1"."ID1" AND "T3"."ID2"="T2"."ID2")
       filter("T3"."ID1">=10 AND "T3"."ID1"<=50)

☞ 5, 7, 9 라인의 필터 조건들은 실제 불필요함

predicate pushing 결과로 있는 것이 아니라 이행적 폐쇄(transitive closure)의 결과

pushing predicate 는 기술적으로 조인조건에서 일어남 -> 라인 7의 access 부분

SQL> alter session set "_push_join_predicate"= false;
------------------------------------------------------------------------
| Id | Operation                      | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |     1 |   209 |    10 |
|* 1 | HASH JOIN OUTER               |       |     1 |   209 |    10 |
|* 2 |   TABLE ACCESS BY INDEX ROWID | T1    |     1 |   119 |     3 |
|* 3 |    INDEX RANGE SCAN            | T1_PK |    42 |       |     2 |
|   4 |   VIEW                         | V1    |     2 |   180 |     6 |
|   5 |    NESTED LOOPS                |       |     2 |   258 |     6 |
|   6 |     TABLE ACCESS BY INDEX ROWID| T3    |   210 | 25200 |     6 |
|* 7 |      INDEX RANGE SCAN          | T3_PK |   210 |       |     2 |
|* 8 |     INDEX UNIQUE SCAN          | T2_PK |     1 |     9 |       |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("V1"."ID1"(+)="T1"."ID1")
   2 - filter("T1"."N1"=5)
   3 - access("T1"."ID1">=10 AND "T1"."ID1"<=50)
   7 - access("T3"."ID1">=10 AND "T3"."ID1"<=50)
   8 - access("T3"."ID1"="T2"."ID1" AND "T3"."ID2"="T2"."ID2")
       filter("T2"."ID1">=10 AND "T2"."ID1"<=50)

일반적인 서브쿼리 286

/남송휘

자연어에 가장 가깝고 쉬운 방식으로 문제를 표현하면서 동시에 옵티마이저가 가장 효율적인 방식으로 결과 쿼리를 수행할 수 있도록 하는것이 가장 좋음

select * from tableX where z_code in ('A','B','C');

tableX에 있는 모든 로우를 스캔하면서 z_code 컬럼이 'A' 또는 'B' 또는 'C'인지 채크

모든 'A'를 찾고 'B'를 찾고, 마지막으로 'C'를 찾음 ( DRIVING 오퍼레이션, 비용적으로 유리한 인덱스 가 존재할경우)

자연어로 말하듯 읽기쉽고 직관적으로 SQL을 작성하는 방법과
DB 엔진이 효율적으로 작성하는 방법이 존재
-> 옵티마이저가 같은 SQL문장들을 엔진에 맞게 잘 변환해주면 사용자의 수고가 줄어듬

서브쿼리 파라미터 289

/남송휘

이름	8i	9i	10g	설명
_unnest_notexists_sq	n/a	single	n/a	하 나 이상의 테이블을 가지는 not exists 서브쿼리를 가능하면 unnest
_unnest_subquery	false	true	true	상관 서브쿼리 unnesting을 가능하게함
_ordered_semi-join	true	true	true	정렬된 세미 조인(exists) 서브쿼리를 가능하게 함
_cost_equality_semi_join	n/a	true	true	등 식 (=equality) 세미 조인 (exists)의 비용계산을 활성화
_always_anti_join	nested_loops	choose	choose	가능하면 anti-join(not exists) 방식을 항상 사용
_always_semi_join	standard	choose	choose	가능하면 semi_join (exists) 방식을 항상사용
_optimizer_correct_sq_selectivity	n/a	n/a	true	서브쿼리 선택도의 정확한 계산을 강제
_optimizer_squ_bottomup	n/a	n/a	true	Bottom-up 방식으로 서브쿼리 unnesting을 가능하게 함
_distinct_view_unnesting	n/a	n/a	false	in subquery를 'select distinct' 뷰로 unnesting 가능하게 함
_right_outer_hash_enable	n/a	n/a	true	right outer(semi와 anti해시 조인을 포함해서) 해시 조인을 가능하게 함
_remove_aggr_subquery	n/a	n/a	true	포함 된 집계 서브쿼리의 제거를 가능하게함

서브쿼리의 분류 290

/이창헌

카테고리	특징
상관/비상관	상관 서브쿼리는 outer쿼리 블록에 있는 컬럼을 참조한다. 상관 서브쿼리는 대개 조인으로 변환될 수 있다. 비상관 서브쿼리는 선행 서브쿼리가 될 가능성이 있다.
단순/복잡	단순한 서브쿼리는 단일 테이블만을 포함한다. 복잡한 서브쿼리는 여러 개의 테이블을 포함하며, 조인형태 또는 서브쿼리 안에 또 다른 서브쿼리르 갖는 형태이다. 서브쿼리가 복잡할 때는 적용되는 않고 단순할 때만 옵티마이저가 수행하는 기능들이 있다.
집계	단순한(단일 테이블) 서브쿼리일지라도 그 안에 어떤 집계연산을 포함한다면 옵티마이저가 그들을 변환하지 못하게 하는 제약이 있을 수 있다.
단일행	(기껏해야)단일 로우를 리턴하는 서브쿼리. 이는 대개 그 서브쿼리가 쿼리전체의 선행 포인트가 될 수 있음을 의미한다.
IN/EXISTS	IN서브쿼리는 Exists서브쿼리로 재 작성될 수 있다. 그런 후에 다시 세미 조인으로 변환될 수 있다.
NOT IN/ NOT EXISTS	NOT IN 서브쿼리는 NOT EXISTS서브쿼리로 재 작성될 수 있다. 그런 후에 어떤 제약 하에서 안티 조인으로 변환될 수 있다. ‘NOT IN’이 ‘IN’의 반대말이 아니며, 특히 NULL컬럼인 경우 이점에 주의해야 한다.

select

outer.*

from empp outer

where outer.sal >

(

select

/*+ no_unnest */ avg(inner.sal)

from empp inner

where inner.dept_no = outer.dept_no

)

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=175 Card=167 Byte =12000)

1 0 FILTER

2 1 TABLE ACCESS (FULL) OF 'EMPP' (TABLE) (Cost=25 Card=20000 Bytes=1440000)

3 1 SORT (AGGREGATE)

4 3 TABLE ACCESS (FULL) OF 'EMPP' (TABLE) (Cost=25 Card=3333 Bytes=26664)

select

outer.*

from empp outer

where outer.sal >

(select

avg(inner.sal)

from empp inner

where inner.dept_no = outer.dept_no

)

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=87 Card=1000 Bytes =98000)

1 0 HASH JOIN (Cost=87 Card=1000 Bytes=98000)

2 1 VIEW OF 'VW_SQ_1' (VIEW) (Cost=61 Card=6 Bytes=156)

3 2 SORT (GROUP BY) (Cost=61 Card=6 Bytes=48)

4 3 TABLE ACCESS (FULL) OF 'EMPP' (TABLE) (Cost=25 Card=20000 Bytes=160000)

5 1 TABLE ACCESS (FULL) OF 'EMPP' (TABLE) (Cost=25 Card=20000 Bytes=1440000)

'VW_SQ_1' : unnesting의 결과로서 보게 되는 뷰

select

/* ordered use_hash(outer) */

outer.*

from

(

select

/*+ no_merge */

dept_no,

avg(inner.sal) avg_sal

from emp inner

group by

dept_no

) inner,

emp outer

where

outer.dept_no = inner.dept_no

and outer.sal > inner.avg_sal

;

서브쿼리를 인라인뷰로 전환해서 그것을 쿼리 본체에 두라는 내용이 있음

--unnest_cost_01a.sql

select

outer.*

from empp outer

where outer.sal >

(

select

avg(inner.sal)

from empp inner

)

;

비상관 서브쿼리에서 선행 서브쿼리가 됨

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=50 Card=1000 Bytes=72000)

1 0 TABLE ACCESS (FULL) OF 'EMPP' (TABLE) (Cost=25 Card=1000 Bytes=72000)

2 1 SORT (AGGREGATE)

3 2 TABLE ACCESS (FULL) OF 'EMPP' (TABLE) (Cost=25 Card=20000 Bytes=100000)

select

outer.*

from empp outer

where outer.dept_no in (

select dept_no

from deptt

where dept_group = 1

)

and outer.sal >

(

select

avg(inner.sal)

from

empp inner

where inner.dept_no = outer.dept_no

and inner.dept_no in (

select dept_no

from deptt

where dept_group = 1

)

;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=81 Card=500 Bytes=51500)
   1    0   HASH JOIN (Cost=81 Card=500 Bytes=51500)
   2    1     TABLE ACCESS (FULL) OF 'DEPTT' (TABLE) (Cost=2 Card=3 Bytes=15)
   3    1     HASH JOIN (Cost=78 Card=1000 Bytes=98000)
   4    3       VIEW OF 'VW_SQ_1' (VIEW) (Cost=52 Card=6 Bytes=156)
   5    4         SORT (GROUP BY) (Cost=52 Card=6 Bytes=78)
   6    5           HASH JOIN (Cost=28 Card=10000 Bytes=130000)
   7    6             TABLE ACCESS (FULL) OF 'DEPTT' (TABLE) (Cost=2 Card=3 Bytes=15)
   8    6             TABLE ACCESS (FULL) OF 'EMPP' (TABLE) (Cost=25 Card=20000 Bytes=160000)
   9    3       TABLE ACCESS (FULL) OF 'EMPP' (TABLE) (Cost=25 Card=20000 Bytes=1440000)

---위 실행 계획은 책이랑 다릅니다.

Outer 쿼리 블목과 Inner쿼리 블록 모두에 비상관 서브쿼리를 사용

1. 비상관 서브쿼리에서 선행 서브쿼리가 됨

2. 비상관 서브쿼리 = 옵티마이저가 단순하다고 생각해서 쿼리 변화 함.

세미 조인(Semi Joins) 298

/이태경

Subquery Unnesting이란 말 그대로 Subquery를 Unnest, 즉 쿼리안으로 풀어버리는 것을 의미한다.
Unnest 힌트를 사용하면 서브쿼리가 인라인 뷰로 바뀐다.

세미(Semi)조인 : Exists (또는 In) 서브쿼리를 다루는 조인

Unique 제약 조건 존재시

SQL> alter table dept add constraint d_uk unique (dept_no);
SQL> alter table dept modify dept_no not null;
SQL> select
2     emp.*
3 from
4     emp
5 where
6     emp.dept_no in (
7             select
8                     dept.dept_no
9             from    dept
10             where   dept.dept_group = 1
11     )
12 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

-----------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 10000 |   751K|    25 |
|* 1 | HASH JOIN         |      | 10000 |   751K|    25 |
|* 2 |   TABLE ACCESS FULL| DEPT |     3 |    15 |     2 |
|   3 |   TABLE ACCESS FULL| EMP | 20000 | 1406K|    22 |
-----------------------------------------------------------

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

   1 - access("EMP"."DEPT_NO"="DEPT"."DEPT_NO")
   2 - filter("DEPT"."DEPT_GROUP"=1)

Unique 제약조건 미존재시

SQL> alter table dept drop constraint d_uk;

SQL>
SQL> select
2     emp.*
3 from
4     emp
5 where
6     emp.dept_no in (
7             select
8                     dept.dept_no
9             from    dept
10             where   dept.dept_group = 1
11     )
12 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 178426960

-----------------------------------------------------------------
| Id | Operation            | Name     | Rows | Bytes | Cost |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT     |          | 10000 |   830K|    25 |
|* 1 | HASH JOIN RIGHT SEMI|          | 10000 |   830K|    25 |
|   2 |   VIEW               | VW_NSO_1 |     3 |    39 |     2 |
|* 3 |    TABLE ACCESS FULL | DEPT     |     3 |    15 |     2 |
|   4 |   TABLE ACCESS FULL | EMP      | 20000 | 1406K|    22 |
-----------------------------------------------------------------

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

   1 - access("EMP"."DEPT_NO"="$nso_col_1")
   3 - filter("DEPT"."DEPT_GROUP"=1)

Unnesting을 막을 때 옵티마이저는 필터 메커니즘 사용

SQL> select
2     emp.*
3 from
4     emp
5 where
6     emp.dept_no in (
7             select /*+ no_unnest */
8                     dept.dept_no
9             from    dept
10             where   dept.dept_group = 1
11     )
12 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1499841400

-----------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 3333 |   234K|    34 |
|* 1 | FILTER            |      |       |       |       |
|   2 |   TABLE ACCESS FULL| EMP | 20000 | 1406K|    22 |
|* 3 |   TABLE ACCESS FULL| DEPT |     1 |     5 |     2 |
-----------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT"
              WHERE "DEPT"."DEPT_NO"=:B1 AND "DEPT"."DEPT_GROUP"=1))
   3 - filter("DEPT"."DEPT_NO"=:B1 AND "DEPT"."DEPT_GROUP"=1)

10g 옵티마이저는 세미 조인 --> Hash조인을 최적화로 판단

SQL> select
2     emp.*
3 from
4     emp
5 where
6     emp.dept_no in (
7             select /*+ nl_sj */
8                     dept.dept_no
9             from    dept
10             where   dept.dept_group = 1
11     )
12 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

-----------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 10000 |   751K|    25 |
|* 1 | HASH JOIN         |      | 10000 |   751K|    25 |
|* 2 |   TABLE ACCESS FULL| DEPT |     3 |    15 |     2 |
|   3 |   TABLE ACCESS FULL| EMP | 20000 | 1406K|    22 |
-----------------------------------------------------------

Predicate Information (identified by operation id):

SQL> select
2     emp.*
3 from
4     emp
5 where
6     emp.dept_no in (
7             select /*+ merge_sj */
8                     dept.dept_no
9             from    dept
10             where   dept.dept_group = 1
11     )
12 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

-----------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 10000 |   751K|    25 |
|* 1 | HASH JOIN         |      | 10000 |   751K|    25 |
|* 2 |   TABLE ACCESS FULL| DEPT |     3 |    15 |     2 |
|   3 |   TABLE ACCESS FULL| EMP | 20000 | 1406K|    22 |
-----------------------------------------------------------

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

   1 - access("EMP"."DEPT_NO"="DEPT"."DEPT_NO")
   2 - filter("DEPT"."DEPT_GROUP"=1)

SQL> select
2     emp.*
3 from
4     emp
5 where
6     emp.dept_no in (
7             select /*+ hash_sj */
8                     dept.dept_no
9             from    dept
10             where   dept.dept_group = 1
11     )
12 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

-----------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 10000 |   751K|    25 |
|* 1 | HASH JOIN         |      | 10000 |   751K|    25 |
|* 2 |   TABLE ACCESS FULL| DEPT |     3 |    15 |     2 |
|   3 |   TABLE ACCESS FULL| EMP | 20000 | 1406K|    22 |
-----------------------------------------------------------

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

   1 - access("EMP"."DEPT_NO"="DEPT"."DEPT_NO")
   2 - filter("DEPT"."DEPT_GROUP"=1)

세미조인의 필요성

기능 : Unique 제약 조건이 없는 상황에서 서브쿼리 사용시 잘못 된 결과가 나오는 것을 방지
내부로직 : Outer 테이블의 로우가 Inner 테이블의 로우와 조인에 성공하면 해당 Outer 로우에 대해선 Inner테이블로의 조인을 수행하지 않음.

Right Semi

9i에서는 서브쿼리는 데이터 집합 크기에 상관없이 항상 후행집합이었으나 10g 부터 Hash Join Right (Semi/Anti/Outer) 기능이 나오게 되면서 서브쿼리 혹은 아우터 Join 되는 쪽의 집합이 선행집합이 될수 있다.이때 Right 의 뜻은 left 집합 대신에 right(후행집합)을 선행집합으로 하겠다는 뜻이다.

안티 조인 301 /위충환

세미(Semi)조인 : Exists (또는 In) 서브쿼리를 다루는 조인
안티(Anti)조인 : Not Exists (또는 Not In) 서브쿼리를 다루는 조인

상황 : 회사의 부서번호(dept_group)가 1과 2로만 구성

그룹 1의 사원 = Not In 그룹 2의 사원 (즉, 그룹 2의 여집합)

▼ anti_01.sql

create table emp (
    dept_no       number    not null,
    sal           number,
    emp_no        number,
    padding       varchar2(60),
    constraint e_pk primary key(emp_no)
);

insert into emp
select
    mod(rownum,6),
    rownum,
    rownum,
    rpad('x',60)
from
    all_objects
where
    rownum <= 20000;

create table dept (
    dept_no       number(6),
    dept_group    number
);

insert into dept values(0, 1);
insert into dept values(1, 1);
insert into dept values(2, 1);
insert into dept values(3, 2);
insert into dept values(4, 2);
insert into dept values(5, 2);
commit;

alter table dept add constraint d_uk unique (dept_no);
alter table dept modify dept_no not null;

☞ emp, dept 각 테이블의 dept_no 컬럼 not null 제약조건

■ 힌트에 의한 각 안티조인 실행계획

select
       emp.*
from
       emp
where
       emp.dept_no not in (
        select
            dept.dept_no
        from dept
        where dept.dept_group = 2
);

Baseline query (Nested Loop anti - join 과 동일한 실행계획)

---------------------------------------------------------------------
| Id | Operation                    | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      | 10000 |   751K|    23 |
|   1 | NESTED LOOPS ANTI           |      | 10000 |   751K|    23 |
|   2 |   TABLE ACCESS FULL          | EMP | 20000 | 1406K|    22 |
|* 3 |   TABLE ACCESS BY INDEX ROWID| DEPT |     2 |    10 |     1 |
|* 4 |    INDEX UNIQUE SCAN         | D_UK |     1 |       |       |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("DEPT"."DEPT_GROUP"=2)
   4 - access("EMP"."DEPT_NO"="DEPT"."DEPT_NO")

Block unnest query

---------------------------------------------------------------------
| Id | Operation                    | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      | 16667 | 1171K|    28 |
|* 1 | FILTER                      |      |       |       |       |
|   2 |   TABLE ACCESS FULL          | EMP | 20000 | 1406K|    22 |
|* 3 |   TABLE ACCESS BY INDEX ROWID| DEPT |     1 |     5 |     1 |
|* 4 |    INDEX UNIQUE SCAN         | D_UK |     1 |       |       |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( NOT EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT"
              WHERE "DEPT"."DEPT_NO"=:B1 AND "DEPT"."DEPT_GROUP"=2))
   3 - filter("DEPT"."DEPT_GROUP"=2)
   4 - access("DEPT"."DEPT_NO"=:B1)

Nested Loop anti - join

---------------------------------------------------------------------
| Id | Operation                    | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      | 10000 |   751K|    23 |
|   1 | NESTED LOOPS ANTI           |      | 10000 |   751K|    23 |
|   2 |   TABLE ACCESS FULL          | EMP | 20000 | 1406K|    22 |
|* 3 |   TABLE ACCESS BY INDEX ROWID| DEPT |     2 |    10 |     1 |
|* 4 |    INDEX UNIQUE SCAN         | D_UK |     1 |       |       |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("DEPT"."DEPT_GROUP"=2)
   4 - access("EMP"."DEPT_NO"="DEPT"."DEPT_NO")

Merge anti - join

--------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes |TempSpc| Cost |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 10000 |   751K|       |   260 |
|   1 | MERGE JOIN ANTI    |      | 10000 |   751K|       |   260 |
|   2 |   SORT JOIN         |      | 20000 | 1406K| 3320K|   255 |
|   3 |    TABLE ACCESS FULL| EMP | 20000 | 1406K|       |    22 |
|* 4 |   SORT UNIQUE       |      |     3 |    15 |       |     5 |
|* 5 |    TABLE ACCESS FULL| DEPT |     3 |    15 |       |     2 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("EMP"."DEPT_NO"="DEPT"."DEPT_NO")
       filter("EMP"."DEPT_NO"="DEPT"."DEPT_NO")
   5 - filter("DEPT"."DEPT_GROUP"=2)

Hash anti - join

SQL> alter session set events '10053 trace name context forever, level 2';
-------------------------------------------------------------
| Id | Operation            | Name | Rows | Bytes | Cost |
-------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 10000 |   751K|    25 |
|* 1 | HASH JOIN RIGHT ANTI|      | 10000 |   751K|    25 |
|* 2 |   TABLE ACCESS FULL | DEPT |     3 |    15 |     2 |
|   3 |   TABLE ACCESS FULL | EMP | 20000 | 1406K|    22 |
-------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("EMP"."DEPT_NO"="DEPT"."DEPT_NO")
   2 - filter("DEPT"."DEPT_GROUP"=2)

■ Not In 서브쿼리를 일반적인 조인문으로 변경한 실행계획

Simple join

select

       emp.*
from
       emp,
       dept
where
       dept.dept_no = emp.dept_no
   and dept.dept_group = 1;



---------------------------------------------------------------------
| Id | Operation                    | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      | 10000 |   751K|    23 |
|   1 | NESTED LOOPS                |      | 10000 |   751K|    23 |
|   2 |   TABLE ACCESS FULL          | EMP | 20000 | 1406K|    22 |
|* 3 |   TABLE ACCESS BY INDEX ROWID| DEPT |     1 |     5 |     1 |
|* 4 |    INDEX UNIQUE SCAN         | D_UK |     1 |       |       |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("DEPT"."DEPT_GROUP"=1)
   4 - access("DEPT"."DEPT_NO"="EMP"."DEPT_NO")

Simple outer join

   select
          emp.*
   from
          emp,
          dept
   where
          dept.dept_no(+) = emp.dept_no
   and dept.dept_group(+) = 2
   and dept.dept_no is null;



----------------------------------------------------------------------
| Id | Operation                     | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      | 20000 | 1503K|    23 |
|* 1 | FILTER                       |      |       |       |       |
|   2 |   NESTED LOOPS OUTER          |      | 20000 | 1503K|    23 |
|   3 |    TABLE ACCESS FULL          | EMP | 20000 | 1406K|    22 |
|* 4 |    TABLE ACCESS BY INDEX ROWID| DEPT |     1 |     5 |     1 |
|* 5 |     INDEX UNIQUE SCAN         | D_UK |     1 |       |       |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPT"."DEPT_NO" IS NULL)
   4 - filter("DEPT"."DEPT_GROUP"(+)=2)
   5 - access("DEPT"."DEPT_NO"(+)="EMP"."DEPT_NO")

☞ 교재에서는 Outer 조인결과 조회하려는 부서 그룹의 dept_no 컬럼 값이 null인 항목이 되어

    dept_no is null 조건으로 나머지 조회결과를 제거하여 조회하려는 부서그룹의 결과만 나타내려하지만

    10gR2 실행결과(적어도 제 컴에서 수행한 위의 결과)에서는 dept_no is null 조건을 먼저 필터링해서

    원하는 계획과 다르게 나오는 것 같음. (Rows : 10000 -> 20000)

☞ 교재에서는 위의 아우터 조인 접근방식과 안티조인방식이 구현 측면에서 동등하다고 표현.

안티 조인의 이상 현상 304 /위충환

   In 서브쿼리 → Exists 서브쿼리 변환 과 동일한 방식으로

   Not In 서브쿼리 → Not Exists 서브쿼리 변환                   ※ 가끔 변환이 일어나지 않을 때가 있음 (원인불명)

■ 논리적으로 동일한 두 쿼리 (Not Exists & Not In)

     ▼ book_subq.sql

create table generator as
select
      rownum id
from  all_objects
where rownum <= 2000;

create table sales as
select
    trunc(7000*dbms_random.normal) book_key,
    rownum          sale_id,
    rpad('x',200)   padding
/*+ ordered use_nl(v2) */
from
    generator v1,
    generator v2
where
    rownum <= 100000;

create table books as
select
    book_key,
    rpad('x',200) padding
from (
    select book_key from sales
    union
    select rownum from all_objects
    where rownum <= 2000
);

create unique index bk_pk on books(book_key);

alter table books add constraint bk_pk primary key(book_key);

create unique index sl_pk on sales(book_key, sale_id);

alter table sales add constraint sl_pk primary key(book_key, sale_id);

alter table sales add constraint sl_fk_bk foreign key (book_key) references books(book_key)

The NOT IN subquery

select book_key
from books
where book_key NOT IN (
select book_key from sales
);

---------------------------------------------------------------
| Id | Operation             | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     5 |    50 |    52 |
|* 1 | HASH JOIN ANTI       |       |     5 |    50 |    52 |
|   2 |   INDEX FAST FULL SCAN| BK_PK | 28903 |   141K|     7 |
|   3 |   INDEX FAST FULL SCAN| SL_PK |   100K|   488K|    28 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("BOOK_KEY"="BOOK_KEY")

The equivalent NOT EXISTS subquery

select book_key
from books
where NOT EXISTS (
select null
from   sales
where sales.book_key = books.book_key
);

---------------------------------------------------------------
| Id | Operation             | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     5 |    50 |    52 |
|* 1 | HASH JOIN ANTI       |       |     5 |    50 |    52 |
|   2 |   INDEX FAST FULL SCAN| BK_PK | 28903 |   141K|     7 |
|   3 |   INDEX FAST FULL SCAN| SL_PK |   100K|   488K|    28 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("SALES"."BOOK_KEY"="BOOKS"."BOOK_KEY")

☞ (9i환경에서는 테스트 해보지 못함)

    10g에서 테스트 결과는 교재에 기술된 내용 처럼 첫번째 쿼리를 수행하기 위해 해시 안티 조인을 사용하였고,

    두 번째 쿼리도 첫 번쨰 쿼리 형태로 변환해서 동일하게 해시 안티 조인방식으로 처리

∴ 동일한 의미의 쿼리를 작성하는 여러가지 방법이 존재하고

    옵티마이저 스스로 그 방법안에 행할 수 있는 모든 종류의 변환을 지능적으로 수행

    ☞ 쿼리 작성 시, 작성자가 스스로 이해하기 쉬운 형태로 작성

        가끔 예상과는 달리 변환이 일어나지 않을 시, 옵티마이저를 약간 돕는 방식으로 쿼리 재작성

NULL과 NOT IN 305

/이창헌

1. NULL값은 IS NULL 또는 IS NOT NULL 이외의 다른 방식으로 비교를 시작할 때 항상 문제를 일으킨다.

  2.

   - colX    not in (‘A’,’B’,’C’)   = [colX != ‘A’ and colX != ‘B’ and colX != ‘C’]

   = 어느 한 조건식이 FALSE 또는 NULL로 평가되면 표현식 전체가 FALSE가 된다.

    - colX in (‘A’,’B’,’C’)   = [colX = ‘A’ or colX = ‘B’ or colX = ‘C’]

  = 어느 하나가 TRUE로 평가되면 TRUE, NULL값을 만나더라도 문제가 되지 않는다는 것을 의미한다.

결론 : IN 과 NOT IN 두개의 연산자가 서로 정확히 반대의 의미가 아님을 보여 준다.

--------------------------------------------------------------------

select * from t1 where n1 =99

        N1 V1

---------- --------------------

        99 Ninety-nine

select * from t2 where n1 =99

선택된 레코드가 없습니다.

--------------------------------------------------------------------

select *

from     t1

where t1.n1 not in (

                           select t2.n1

                           from t2

             )

선택된 레코드가 없습니다.

--------------------------------------------------------------------

select *

from     t1

where t1.n1 is not null

and     t1.n1 not in (

                        select t2.n1

                        from t2

                        where t2.n1 is not null

             )

        N1 V1

---------- --------------------

        99 Ninety-nine

--------------------------------------------------------------------

Ordered 힌트 307

/남송휘

효율적이던 코드가 어느날 느려질수 있음

ordered 힌트의 사용예를 통해 간단한 쿼리라도 위험해 질수 있는 예를 보여줌

select
   /*+ ordered push_subq */
   t1.v1
from
   t1, t3
where
   t3.n1 = t1.n1
and   exists (
       select
           t2.id
       from   t2
       where   t2.n1 = 15
       and   t2.id = t1.id
   )
and   exists (
       select
           t4.id
       from   t4
       where   t4.n1 = 15
       and   t4.id = t3.id
   )
;

9.2.0.4

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3693 Card=1812 Bytes=45300)
   1    0   FILTER
   2    1     HASH JOIN (Cost=69 Card=1812 Bytes=45300)
   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=34 Card=1000 Bytes=16000)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1 Bytes=8)
   5    4           INDEX (UNIQUE SCAN) OF 'T2_PK' (UNIQUE) (Cost=1 Card=2000)
   6    2       TABLE ACCESS (FULL) OF 'T3' (Cost=34 Card=1000 Bytes=9000)
   7    1     TABLE ACCESS (BY INDEX ROWID) OF 'T4' (Cost=2 Card=1 Bytes=8)
   8    7       INDEX (UNIQUE SCAN) OF 'T4_PK' (UNIQUE) (Cost=1 Card=2000)

책의 실행계획은 9.2.0.6이며 사용한 버젼은 9.2.0.4버젼 (책의 8.1.7.4.와 동일한 결과를 보여줌),10g 는 9.2.0.6의 결과와 동일

10.2.0.1

-----------------------------------------------------------------------------
| Id | Operation                         | Name    | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |     1 |    51 |    36 |
|   1 | NESTED LOOPS                     |         |     1 |    51 |    36 |
|   2 |   NESTED LOOPS                    |         |     9 |   378 |    27 |
|   3 |    MERGE JOIN CARTESIAN           |         |     9 |   234 |    18 |
|   4 |     VIEW                          | VW_SQ_2 |     3 |    39 |     4 |
|   5 |      HASH UNIQUE                  |         |     3 |    24 |       |
|   6 |       TABLE ACCESS BY INDEX ROWID | T4      |     3 |    24 |     4 |
|* 7 |        INDEX RANGE SCAN           | T4_N1   |     3 |       |     1 |
|   8 |     BUFFER SORT                   |         |     3 |    39 |    18 |
|   9 |      VIEW                         | VW_SQ_1 |     3 |    39 |     4 |
| 10 |       HASH UNIQUE                 |         |     3 |    24 |       |
| 11 |        TABLE ACCESS BY INDEX ROWID| T2      |     3 |    24 |     4 |
|* 12 |         INDEX RANGE SCAN          | T2_N1   |     3 |       |     1 |
| 13 |    TABLE ACCESS BY INDEX ROWID    | T1      |     1 |    16 |     1 |
|* 14 |     INDEX UNIQUE SCAN             | T1_PK   |     1 |       |       |
|* 15 |   TABLE ACCESS BY INDEX ROWID     | T3      |     1 |     9 |     1 |
|* 16 |    INDEX UNIQUE SCAN              | T3_PK   |     1 |       |       |
-----------------------------------------------------------------------------

두개의 서브쿼리를 unnest 하려고 시도

서브 쿼리 unnesting이 아래쪽부터 일어나고 unnesting후 인라인 뷰는 from젤에서 압쪽부터 삽입

테이블순서는 t4,t2,t1,t3인상태에서 ordered 힌트가 작용되어 push_suq는 할일이없어져 위와 같은 실행계획생성

참고)

select
   /*+ ordered push_subq */
   t1.v1
from
   t1, t3
where
   t3.n1 = t1.n1
and   exists (
       select    /*+ no_unnest */
           t2.id
       from   t2
       where   t2.n1 = 15
       and   t2.id = t1.id
   )
and   exists (
       select    /*+ no_unnest */
           t4.id
       from   t4
       where   t4.n1 = 15
       and   t4.id = t3.id
   )
;

9.2.0.4

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3693 Card=1812 Bytes=45300)
   1    0   FILTER
   2    1     HASH JOIN (Cost=69 Card=1812 Bytes=45300)
   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=34 Card=1000 Bytes=16000)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1 Bytes=8)
   5    4           INDEX (UNIQUE SCAN) OF 'T2_PK' (UNIQUE) (Cost=1 Card=2000)
   6    2       TABLE ACCESS (FULL) OF 'T3' (Cost=34 Card=1000 Bytes=9000)
   7    1     TABLE ACCESS (BY INDEX ROWID) OF 'T4' (Cost=2 Card=1 Bytes=8)
   8    7       INDEX (UNIQUE SCAN) OF 'T4_PK' (UNIQUE) (Cost=1 Card=2000)

10.2.0.1

-----------------------------------------------------------------------
| Id | Operation                     | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     1 |    25 |   518K|
|* 1 | FILTER                       |       |       |       |       |
|* 2 |   HASH JOIN                   |       |   547K|    13M|   130 |
|   3 |    TABLE ACCESS FULL          | T1    | 20000 |   312K|    34 |
|   4 |    TABLE ACCESS FULL          | T3    | 20000 |   175K|    34 |
|* 5 |   TABLE ACCESS BY INDEX ROWID | T2    |     1 |     8 |     2 |
|* 6 |    INDEX UNIQUE SCAN          | T2_PK |     1 |       |     1 |
|* 7 |    TABLE ACCESS BY INDEX ROWID| T4    |     1 |     8 |     2 |
|* 8 |     INDEX UNIQUE SCAN         | T4_PK |     1 |       |     1 |
-----------------------------------------------------------------------

스타 변환 조인 309

/박우창

? 오라클은 스타변환을 통하여 효율적인 질의 처리를 한다. 스타변환조인은 DW 환경에서 Star Schema에서의 질의 최적화를 하는 방법이다. 스타스키마는 대량의 데이터를 갖는 1개의 fact 테이블과 소량의 데이터를 갖는 여러개의 디멘전 테이블로 구성된다.

? 예제 - (star_trans.sql)

- 실험테이블

fact1(id, mod_23, mod_31, mod_53, small_vc) - 2,000,000튜플

dim_23(id_23, rep_23, vc_23, padding_23) - 460튜플(23*20)

dim_31(id_31, rep_31, vc_31, padding_31) - 620튜플(31*20)

dim_53(id_53, rep_53, vc_53, padding_53) - 1060튜플(53*20)

(주1) 밑줄 속성은 fact1 테이블에 대한 외래키

(주2) rep_* 값이 각각 0부터 (22,30,52)까지 20번 반복되는 값을 가짐

- 실험테이블 fact1 인덱스

create bitmap index fact1_23 on fact1(mod_23) local;

create bitmap index fact1_31 on fact1(mod_31) local;

create bitmap index fact1_53 on fact1(mod_53) local;

? 질의 실험 및 관찰

- 예제 질의

SELECT

--count(*)

dim_23.vc_23, dim_31.vc_31, dim_53.vc_53, fact1.small_vc

FROMdim_23,dim_31,dim_53,fact1

WHEREfact1.mod_23 = dim_23.id_23 and dim_23.rep_23 = 10

andfact1.mod_31 = dim_31.id_31 and dim_31.rep_31 = 10

andfact1.mod_53 = dim_53.id_53 and dim_53.rep_53 = 10;

- 실행결과

(1) star_transformation_enabled = false;

---------------------------------------------------------------------------------
| Id | Operation              | Name   | Rows | Bytes | Cost | Pstart| Pstop |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |   423K|    47M| 6806 |       |       |
|* 1 | HASH JOIN             |        |   423K|    47M| 6806 |       |       |
|* 2 |   TABLE ACCESS FULL    | DIM_23 |    20 |   560 |    18 |       |       |
|* 3 |   HASH JOIN            |        |   486K|    41M| 6781 |       |       |
|* 4 |    TABLE ACCESS FULL   | DIM_31 |    20 |   560 |    23 |       |       |
|* 5 |    HASH JOIN           |        |   754K|    43M| 6751 |       |       |
|* 6 |     TABLE ACCESS FULL | DIM_53 |    20 |   560 |    37 |       |       |
|   7 |     PARTITION RANGE ALL|        | 2000K|    62M| 6702 |     1 |     4 |
|   8 |      TABLE ACCESS FULL | FACT1 | 2000K|    62M| 6702 |     1 |     4 |
---------------------------------------------------------------------------------

(설명) 단계적인 hash 조인 : fact1 -> dim_53 -> dim_31 -> dim_23

(2) star_transformation_enabled = true;

- transform 된 질의

select dim23.vc_23, dim31.vc_31, dim53.vc_53, v1.small_vc

from dim_23,dim_31,dim_53,

(

          select mod_23, mod_31, mod_53, small_vc

          from fact1

          where

                       fact1.mod_23 in (select id_23 from dim_23 where dim_23.rep_23 = 10)

                  and fact1.mod_23 in (select id_31 from dim_31 where dim_31.rep_31 = 10)

                  and fact1.mod_53 in (select id_53 from dim_53 where dim_53.rep_53 = 10)

) v1

where           dim_23.id_23 = v1.mod_23

              and dim_31.id_31 = v1.mod_31

              and dim_53.id_53 = v1.mod_53

-실행계획

----------------------------------------------------------------------------------------------------
| Id | Operation                              | Name                      | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                           |    11 | 1188 |   189 |
|   1 | TEMP TABLE TRANSFORMATION             |                           |       |       |       |
|   2 |   LOAD AS SELECT                       | SYS_TEMP_0FD9D6606_8BEE46 |       |       |       |
|* 3 |    TABLE ACCESS FULL                   | DIM_53                    |    20 |   560 |    37 |
|   4 |   LOAD AS SELECT                       | SYS_TEMP_0FD9D6606_8BEE46 |       |       |       |
|* 5 |    TABLE ACCESS FULL                   | DIM_31                    |    20 |   560 |    23 |
|   6 |   LOAD AS SELECT                       | SYS_TEMP_0FD9D6606_8BEE46 |       |       |       |
|* 7 |    TABLE ACCESS FULL                   | DIM_23                    |    20 |   560 |    18 |
|* 8 |   HASH JOIN                            |                           |    11 | 1188 |   111 |
|* 9 |    HASH JOIN                           |                           |    13 | 1079 |   108 |
|* 10 |     HASH JOIN                          |                           |    20 | 1160 |   105 |
| 11 |      TABLE ACCESS FULL                 | SYS_TEMP_0FD9D6604_8BEE46 |    20 |   500 |     2 |
| 12 |      PARTITION RANGE ALL               |                           |    53 | 1749 |   101 |
| 13 |       TABLE ACCESS BY LOCAL INDEX ROWID| FACT1                     |    53 | 1749 |   101 |
| 14 |        BITMAP CONVERSION TO ROWIDS     |                           |       |       |       |
| 15 |         BITMAP AND                     |                           |       |       |       |
| 16 |          BITMAP MERGE                  |                           |       |       |       |
| 17 |           BITMAP KEY ITERATION         |                           |       |       |       |
| 18 |            BUFFER SORT                 |                           |       |       |       |
| 19 |             TABLE ACCESS FULL          | SYS_TEMP_0FD9D6604_8BEE46 |     1 |    13 |     2 |
|* 20 |            BITMAP INDEX RANGE SCAN     | FACT1_53                  |       |       |       |
| 21 |          BITMAP MERGE                  |                           |       |       |       |
| 22 |           BITMAP KEY ITERATION         |                           |       |       |       |
| 23 |            BUFFER SORT                 |                           |       |       |       |
| 24 |             TABLE ACCESS FULL          | SYS_TEMP_0FD9D6606_8BEE46 |     1 |    13 |     2 |
|* 25 |            BITMAP INDEX RANGE SCAN     | FACT1_23                  |       |       |       |
| 26 |          BITMAP MERGE                  |                           |       |       |       |
| 27 |           BITMAP KEY ITERATION         |                           |       |       |       |
| 28 |            BUFFER SORT                 |                           |       |       |       |
| 29 |             TABLE ACCESS FULL          | SYS_TEMP_0FD9D6605_8BEE46 |     1 |    13 |     2 |
|* 30 |            BITMAP INDEX RANGE SCAN     | FACT1_31                  |       |       |       |
| 31 |     TABLE ACCESS FULL                  | SYS_TEMP_0FD9D6605_8BEE46 |    20 |   500 |     2 |
| 32 |    TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6606_8BEE46 |    20 |   500 |     2 |
----------------------------------------------------------------------------------------------------

(설명1) -> 디멘전 테이블 full scan하여 rep 값이 10인 찾아 킷값을 정렬한 후

fact1 테이블의 비트맵 인덱스 스캔하여 BITMAP MERGE

위 과정을 3개의 디멘전 테이블에 대하여 수행(17-20,22-25,27-30)

-> 비트맵 스트림으로 병합(16,21,26)

-> BITMAP AND 수행(15)

-> rowid 변환(14)

-> fact1 테이블 access(13, card=53) /* 앞 예에서 423K에서 개선됨)

(설명2) 질의변환을 통하여 비용값이 6806 -> 189로 감소

스타 조인 317

/박우창

? 오라클은 /*+ star */를 사용하면 디멘전 테이블에 대하여 조인의 가능한 케이스를 모두 검토한다. 7개의 디멘전 테이블의 경우 5040 가지의 방법을 모두 검토한다. 검토 시간은 무시할 정도이다. /*+ star */를 사용하지 않으면 _optimizer_max_permutation 값의 범위 내에서 스타 조인외에 다른 방법을 검토한다. 저자는 /+* star */ 힌트의 유용한 실사례는 찾기 어렵다고 언급하였다.

? 예제 - (star_join.sql)

   - 실험테이블

fact_tab(id, id2, id3, small_vc, padding)

dim1(id, v1, p1)

dim2(id, v2, p2)

dim3(id, v3, p3)

? 질의 실험 및 관찰

- 예제 질의

select /*+ star */ d1.p1, d2.p2, d3.p3, f.small_vc

from dim1 d1,dim2 d2,dim3 d3,fact_tabf

where d1.v1 = 'abc' and d2.v2 = 'def' and d3.v3 = 'ghi'

          and f.id1 = d1.idand f.id2 = d2.idand f.id3 = d3.id;

- 실행결과

(1) 힌트없음

-------------------------------------------------------------------------
| Id | Operation                    | Name     | Rows | Bytes | Cost |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |   127 |     6 |
|   1 | NESTED LOOPS                |          |     1 |   127 |     6 |
|   2 |   MERGE JOIN CARTESIAN       |          |     1 |    81 |     6 |
|   3 |    MERGE JOIN CARTESIAN      |          |     1 |    54 |     4 |
|* 4 |     TABLE ACCESS FULL        | DIM1     |     1 |    27 |     2 |
|   5 |     BUFFER SORT              |          |     1 |    27 |     2 |
|* 6 |      TABLE ACCESS FULL       | DIM2     |     1 |    27 |     2 |
|   7 |    BUFFER SORT               |          |     1 |    27 |     4 |
|* 8 |     TABLE ACCESS FULL        | DIM3     |     1 |    27 |     2 |
|   9 |   TABLE ACCESS BY INDEX ROWID| FACT_TAB |     1 |    46 |       |
|* 10 |    INDEX UNIQUE SCAN         | F_PK     |     1 |       |       |
-------------------------------------------------------------------------

(설명)

(2) 힌트 /*+ star */ 사용

-------------------------------------------------------------------------
| Id | Operation                    | Name     | Rows | Bytes | Cost |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |   127 |     6 |
|   1 | NESTED LOOPS                |          |     1 |   127 |     6 |
|   2 |   MERGE JOIN CARTESIAN       |          |     1 |    81 |     6 |
|   3 |    MERGE JOIN CARTESIAN      |          |     1 |    54 |     4 |
|* 4 |     TABLE ACCESS FULL        | DIM1     |     1 |    27 |     2 |
|   5 |     BUFFER SORT              |          |     1 |    27 |     2 |
|* 6 |      TABLE ACCESS FULL       | DIM2     |     1 |    27 |     2 |
|   7 |    BUFFER SORT               |          |     1 |    27 |     4 |
|* 8 |     TABLE ACCESS FULL        | DIM3     |     1 |    27 |     2 |
|   9 |   TABLE ACCESS BY INDEX ROWID| FACT_TAB |     1 |    46 |       |
|* 10 |    INDEX UNIQUE SCAN         | F_PK     |     1 |       |       |
-------------------------------------------------------------------------

향후 전망 319

/박우창

? SQL 질의를 의미는 같지만 다른 표현으로 바꾸는 새로운 방법들이 나오고 있다. 사용자가 직접 변환하는 실수를 줄이는 장점이 있다.

? 예

- original 질의

select n2 from t1 where n1 < 3

intersect

select n2 from t2 where n1 < 2;

-----------------------------------------------------------------------
| Id | Operation                     | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |    30 |   600 |    28 |
|   1 | INTERSECTION                 |       |       |       |       |
|   2 |   SORT UNIQUE                 |       |    45 |   360 |    11 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    |    45 |   360 |     4 |
|* 4 |     INDEX RANGE SCAN          | T1_I1 |    45 |       |     2 |
|   5 |   SORT UNIQUE                 |       |    30 |   240 |    17 |
|* 6 |    TABLE ACCESS FULL          | T2    |    30 |   240 |    10 |
-----------------------------------------------------------------------

- 재 작성된 질의

selectdistinct t1.n2

fromt1, t2

wheret1.n1 < 3 andt2.n1 < 2

andsys_op_map_nonnull(t2.n2) = sys_op_map_nonnull(t1.n2);

-----------------------------------------------------------------------
| Id | Operation                     | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |    13 |   208 |    22 |
|   1 | HASH UNIQUE                  |       |    13 |   208 |    22 |
|* 2 |   HASH JOIN ANTI              |       |    13 |   208 |    15 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    |    45 |   360 |     4 |
|* 4 |     INDEX RANGE SCAN          | T1_I1 |    45 |       |     2 |
|* 5 |    TABLE ACCESS FULL          | T2    |    30 |   240 |    10 |
-----------------------------------------------------------------------

요약 322

/박우창

테스트 스크립트 323

/박우창

이 게시물을

번호	제목	글쓴이	날짜	조회 수
17	진행기록	운영자	2010.04.10	7686
16	Front Page	운영자	2009.12.20	130184
15	비용기반의 오라클 원리 첫 모임 [4]	운영자	2010.01.03	5824
14	1. 비용(COST)이란?	ZoneWorker2	2009.12.28	18005
13	2. 테이블 스캔	balto	2009.12.28	13228
12	3. 단일 테이블 선택도	ZoneWorker2	2009.12.28	8412
11	4. 단순 B-tree 액세스	휘휘	2009.12.28	8887
10	5. 클러스터링 팩터	balto	2009.12.28	14776
9	6. 선택도 이슈	ZoneWorker2	2009.12.28	7630
8	7. 히스토그램	ZoneWorker2	2009.12.28	10093
7	8. 비트맵 인덱스	ZoneWorker2	2009.12.28	8017
»	9. 쿼리 변환	헌쓰	2009.12.28	15080
5	10. 조인 카디널리티	휘휘	2009.12.28	8824
4	11. NL(Nested Loops) 조인	휘휘	2009.12.28	11865
3	12. 해시 조인	헌쓰	2009.12.28	16986
2	13. 정렬과 머지 조인	실천하자	2009.12.28	16128
1	14. 10053 트레이스 파일	휘휘	2009.12.28	10269

첫 페이지 1 끝 페이지

쓰기

태그

bysql.net

로그인

9. 쿼리 변환

댓글 0

오라클 버전	1단계(내부테이블) ACCESS(FULL)-> SORT(AGGREGATE)	2단계(외부테이블) ACCESS(FULL)	3단계(SELECT) FILTER	설명
8.1.7.4	33(3334)	33(1000)	33(1000)	(주1)
9.2.0.6	35(3333)	35(1000)	35035(1000)	(주2),(주3)
10.1.0.4	35(3333)	35(20000)	245(167)	(주4),(주5)

push subquery /+ push_subq /	nesting /+ no_unnest /	실행계획	consistent gets(cost)	설명
	no_unnest	(parent join child) FILTER subtest	829(163)	(주1)
push_subq	no_unnest	(parent join child) FILTER subtest	829(163)
push_subq		(parent join subtest) join child	22(11)	(주2)
		(parent join subtest) join child	22(11)