태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Subquery is just like Join

오라클 2008.06.22 22:24
Subquery를 해석하는 가장 좋은 방법은 이를 Join으로 인식하는 것이다.

아래 예를 보면...

select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 in (select t2.c1 from t2)
;

@stat_io

-----------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|*  1 | HASH JOIN RIGHT SEMI |       |      1 |    999 |   1000 |00:00:00.14 |     105 |
|   2 |   INDEX FAST FULL SCAN| T2_N1 |      1 |   1000 |   1000 |00:00:00.01 |       7 |
|   3 |   TABLE ACCESS FULL   | T1    |      1 |  10000 |  10000 |00:00:00.03 |      98 |
-----------------------------------------------------------------------------------------

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

   1 - access("T1"."C1"="T2"."C1")

In 절의 Subquery는 Unnesting 과정에 의해 Hash (Semi) Join으로 변환된다. 따라서 이러한 Query를 튜닝하는 방법 또한 Join Query를 튜닝하는 기법과 거의 동일하다.

가령, 위의 쿼리에서 다음과 같이 첫번째 몇 개의 로우만 빨리 얻고자 한다면?

select * from (
select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 in (select t2.c1 from t2)
)
where rownum <= 1;

------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY         |       |      1 |        |      1 |00:00:00.01 |      11 |
|*  2 |   HASH JOIN RIGHT SEMI |       |      1 |    999 |      1 |00:00:00.01 |      11 |
|   3 |    INDEX FAST FULL SCAN| T2_N1 |      1 |   1000 |   1000 |00:00:00.01 |       7 |
|   4 |    TABLE ACCESS FULL   | T1    |      1 |  10000 |      1 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=1)
   2 - access("T1"."C1"="T2"."C1")

여전히 Hash Semi Join이 사용된다. 이런 경우 Hash Semi Join은 비효율적이다.  만일 Join의 관점에서 생각한다면 Nested Loop Join으로 변환하면 첫번째 몇 개의 로우를 가장 빨리 얻을 수 있다. 최적의 힌트는 다음과 같다.

select * from (
select /*+ gather_plan_statistics first_rows */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 in (select t2.c1 from t2)
)
where rownum <= 1;

@stat_io

---------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY      |       |      1 |        |      1 |00:00:00.01 |       6 |
|   2 |   NESTED LOOPS SEMI |       |      1 |    999 |      1 |00:00:00.01 |       6 |
|   3 |    TABLE ACCESS FULL| T1    |      1 |  10000 |      1 |00:00:00.01 |       4 |
|*  4 |    INDEX RANGE SCAN | T2_N1 |      1 |    100 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=1)
   4 - access("T1"."C1"="T2"."C1")


In이나 Exists Subquery가 항상 Semi Join으로 Unnesting되는 것은 아니다. 아래 예를 보면...

select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 in (select c1 from t2
            union all
            select c1 from t3)
;

@stat_io

--------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN            |          |      1 |   1100 |   1000 |00:00:00.17 |     112 |
|   2 |   VIEW                | VW_NSO_1 |      1 |   1100 |   1000 |00:00:00.03 |      14 |
|   3 |    HASH UNIQUE        |          |      1 |        |   1000 |00:00:00.02 |      14 |
|   4 |     UNION-ALL         |          |      1 |        |   1100 |00:00:00.02 |      14 |
|   5 |      TABLE ACCESS FULL| T2       |      1 |   1000 |   1000 |00:00:00.01 |       7 |
|   6 |      TABLE ACCESS FULL| T3       |      1 |    100 |    100 |00:00:00.01 |       7 |
|   7 |   TABLE ACCESS FULL   | T1       |      1 |  10000 |  10000 |00:00:00.04 |      98 |
--------------------------------------------------------------------------------------------

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

   1 - access("T1"."C1"="$nso_col_1")

일단 Subquery가 Inline View(VW_NSO_1)로 Unnesting된 후, T1 테이블과 Hash Join되는 것을 확인할 수 있다. Join으로 인식할 수 있다는 사실에는 변함이 없다.

문제는 Subquery가 항상 Unnesting이 이루어지는 것은 아니라는 것이다. 아래 예를 보면...

select /*+ gather_plan_statistics */
 t1.c1, t1.c2
from
  t1
where
   t1.c1 in (select c1 from t2) or
   t1.c1 between 1 and 100 
;

@stat_io

--------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|*  1 | FILTER            |       |      1 |        |   1000 |00:00:00.22 |   19898 |
|   2 |   TABLE ACCESS FULL| T1    |      1 |  10000 |  10000 |00:00:00.04 |      98 |
|*  3 |   INDEX RANGE SCAN | T2_N1 |   9900 |      1 |    900 |00:00:00.11 |   19800 |
--------------------------------------------------------------------------------------

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

   1 - filter((("T1"."C1"<=100 AND "T1"."C1">=1) OR  IS NOT NULL))
   3 - access("C1"=:B1)


OR 조건에 의해 Unnesting이 원천적으로 이루어지지 않는다. 이로 인해 Join이 아닌 Filter Operation이 사용된다. Filter Operation이 항상 비효율적인 것은 아니지만 위의 결과를 보면 일량이 결코 무시할 수준이 아님을 알 수 있다. 방법은?

Query를 재작성하는 것이다. 다음과 같이...

select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
   t1.c1 in (select c1 from t2)
union
select
  t1.c1, t1.c2
from
  t1
where
  t1.c1 between 1 and 100
;

@stat_io


-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   1 |  SORT UNIQUE                  |       |      1 |   1100 |   1000 |00:00:00.15 |      41 |
|   2 |   UNION-ALL                   |       |      1 |        |   1100 |00:00:00.15 |      41 |
|*  3 |    HASH JOIN                  |       |      1 |   1000 |   1000 |00:00:00.13 |      38 |
|   4 |     INDEX FAST FULL SCAN      | T2_N1 |      1 |   1000 |   1000 |00:00:00.01 |       7 |
|   5 |     TABLE ACCESS FULL         | T1    |      1 |  10000 |  10000 |00:00:00.03 |      31 |
|   6 |    TABLE ACCESS BY INDEX ROWID| T1    |      1 |    100 |    100 |00:00:00.01 |       3 |
|*  7 |     INDEX RANGE SCAN          | T1_N1 |      1 |    100 |    100 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------

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

   3 - access("T1"."C1"="C1")
   7 - access("T1"."C1">=1 AND "T1"."C1"<=100)


OR 조건을 없앰으로써 보다 효율적인 실행 계획으로 유도할 수 있다.
(단, Union 연산자의 사용으로 인해 첫번째 몇 개의 로우만 가져오고자 할 경우에는 오히려 Filter Operation이 사용된 첫번째 쿼리가 더 효율적일 것이다)

아래 예를 하나 더 보면...

select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
   t1.c1 in (
    select c1
    from t2
    where exists (
        select 1
        from t3
        where
            t3.c1 = t1.c1
        )
  )
;

@stat_io

--------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|*  1 |  FILTER            |       |      1 |        |    100 |00:00:00.32 |   10238 |
|   2 |   TABLE ACCESS FULL| T1    |      1 |  10000 |  10000 |00:00:00.04 |      38 |
|*  3 |   FILTER           |       |  10000 |        |    100 |00:00:00.22 |   10200 |
|*  4 |    INDEX RANGE SCAN| T2_N1 |    100 |      1 |    100 |00:00:00.01 |     200 |
|*  5 |    INDEX RANGE SCAN| T3_N1 |  10000 |      1 |    100 |00:00:00.10 |   10000 |
--------------------------------------------------------------------------------------

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

   1 - filter( IS NOT NULL)
   3 - filter( IS NOT NULL)
   4 - access("C1"=:B1)
   5 - access("T3"."C1"=:B1)

왜 Unnesting이 이루어지지 않는가? 위의 Subquery를 Join의 관점에서 해석하면 좀 더 명확해진다. 아래에서 Bold체로 강조된 부분을 보면...

select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 in (
   select c1
   from t2
   where exists (
       select 1
       from t3
       where
           t3.c1 = t1.c1
       )
  )
;

t1과 t2의 Join은 이루어지지 않는 상태에서 t3와 t1의 조인이 이루어지는 것과 같은 꼴이다. 따라서 Join으로 변환하는 것이 논리적으로 불가능하다. 이 경우 Query를 다음과 같이 변환하면 Unnesting이 성공적으로 이루어진다.

select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
   t1.c1 in (
    select c1
    from t2)
   and exists (
        select 1
        from t3
        where t3.c1 = t1.c1
      )
;

@stat_io

위와 같이 변환하면 t1과 t2, t1과 t3가 각각 조인이 이루어지는 것과 같이 인식된다. 아래와 같이 Semi Join으로 변환된 것을 확인할 수 있다.

------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   1 | NESTED LOOPS SEMI     |       |      1 |     99 |    100 |00:00:00.16 |      53 |
|*  2 |   HASH JOIN RIGHT SEMI |       |      1 |    999 |   1000 |00:00:00.14 |      45 |
|   3 |    INDEX FAST FULL SCAN| T2_N1 |      1 |   1000 |   1000 |00:00:00.01 |       7 |
|   4 |    TABLE ACCESS FULL   | T1    |      1 |  10000 |  10000 |00:00:00.03 |      38 |
|*  5 |   INDEX RANGE SCAN     | T3_N1 |   1000 |     10 |    100 |00:00:00.01 |       8 |
------------------------------------------------------------------------------------------

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

   2 - access("T1"."C1"="C1")
   5 - access("T3"."C1"="T1"."C1")


Oracle은 가능한 최선을 다해 Subquery를 Unnesting하며, 이는 곧 Join Operation으로 관찰된다. 만일 Subquery가 Join이 아닌 Filter로 나타나면 이는 Unnesting이 이루어지지 않았다는 의미이다.

Unnesting이 이루어지는 경우 대부분의 경우 성능에 유리하지만 어떤 경우에는 오히려 Filter Operation, 즉 Unnesting이 이루어지지 않는 것이 더 유리한 경우도 많다. 이런 이유 때문에 10g부터는 복잡한 Subquery에 대해서는 Unnesting 작업을 Cost 기반으로 수행한다. (Cost based query transformation)


Subquery를 사용할 경우에는 Plan 점검을 통해 Subquery가 어떻게 변환되는지 꼼꼼이 확인하는 습관이 필요한 대목이다.


신고

'오라클' 카테고리의 다른 글

Automize your test case  (0) 2008.07.04
Optimizing Unoptimizable SQL  (3) 2008.07.01
Subquery is just like Join  (4) 2008.06.22
Visual의 힘  (0) 2008.06.20
왜 내 PPT 자료는 엿같은가?  (6) 2008.06.17
Trackback 0 : Comments 4
  1. extremedb 2008.06.24 20:23 신고 Modify/Delete Reply

    아주 유용한 내용이었습니다.

    그런데 10g 에서 first_rows 가 deprecate 된거 같습니다.
    하위버젼 을 위해서만 남은거 같습니다.
    그리고 union 쿼리에서 아래처럼 바꾸면 어떨까요?
    sort unique 를 없애기 위해서 입니다.
    물론 경우에 따라서 더느려질수도 있습니다.

    select /*+ gather_plan_statistics */
    t1.c1, t1.c2
    from
    t1
    where t1.c1 in (select t2.c1 from t2 where not t2.c1 between 1 and 100)
    union all
    select
    t1.c1, t1.c2
    from
    t1
    where
    t1.c1 between 1 and 100
    ;

  2. 욱짜 2008.06.24 20:57 신고 Modify/Delete Reply

    말씀하신 것처럼 first_rows 힌트는 10g에서 deprecated되었습니다. first_rows(N) 힌트가 100% 비용기반인데 반해 first_rows 힌트는 내부적으로 Rule을 가지고 (가령 인덱스를 선호하라, NL Join을 선호하라 등) 있습니다.

    100% 비용 기반이 더 좋은 방식이지만, 간혹 first_rows 힌트를 사용해야만 원하는 대로 NL Join이나 Index Lookup이 이루어지는 경우가 종종 있습니다. 100% 비용기준이다 보니 오히려 NL Join이나 Index Lookup이 사용되지 않는 경우가 종종 발생합니다.

    Predicate를 적절히 수정해서 union을 union all로 바꾸니 아주 좋은데요? first_rows 같은 힌트를 사용해서 Nested Loop Semi Join으로 유도할 수 있으면 Filter Operation을 사용하지 않으면서 빠른 응답 시간을 얻을 수 있을거 같습니다.

  3. oracler 2008.07.01 00:49 신고 Modify/Delete Reply

    gather plan 결과로서 나오는 리포트 predicate information 에 자주 나오는 단어..
    access 와 filetering 이 나오는데 그 차이점이 정확히 뭔가요.
    gather 돌릴때마다 나오는데 이거야 원~~

  4. 욱짜 2008.07.01 09:20 신고 Modify/Delete Reply

    정확한 정의는 다음과 같습니다.
    Access Predicate = Block을 Read하기 위한 접근 경로(access path) 조건. 즉 실제 Block을 읽기 전에 Block을 어떤 경로로 읽을 것인가를 결정하는 조건이라는 의미입니다. 따라서 Index Lookup이나 Join 조건은 Access Predicate로 표기됩니다.
    Filter Predicate = Block을 Read한 후 특정 Row를 Filter하기 위한 사용되는 조건. 따라서 Full Table Scan 등은 Filter Predicate로 표기됩니다.

Write a comment

티스토리 툴바