꿈꾸는 개발자, DBA 커뮤니티 구루비


create table t1(c1 int, c2 int);
create index t1_n1 on t1(c1);
create index t1_n2 on t1(c2);

insert into t1
select level, level
  from dual
connect by level <= 10000;

@gather t1

--------------------------------------------------------------
--USE_CONCAT Hint를 이용해 OR 조건을 Union All처럼 변환하는 것
--c2 = :b1 조건에 대해서는 Full Table Scan을
--c1 = :b1 조건에 대해서는 Index를 경유하고자 한다.
--------------------------------------------------------------

woong:WOONG >
  t1  explain plan for
  2   select /*+ use_concat */ *
  3    from t1
  4   where c1 = :b1 or c2 = :b2;

해석되었습니다.

경   과: 00:00:00.01
woong:WOONG >
  t1  @plan

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 82564388

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    14 |     4   (0)| 00:00:01 |
|   1 |  CONCATENATION               |       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_N2 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   3 - access("C2"=TO_NUMBER(:B2))
   4 - filter(LNNVL("C2"=TO_NUMBER(:B2)))
   5 - access("C1"=TO_NUMBER(:B1))

19 개의 행이 선택되었습니다.

경   과: 00:00:00.01
woong:WOONG >
  t1  explain plan for
  2   select /*+ use_concat full(t1) */ *
  3     from t1
  4    where c1 = :b1 or c2 = :b2;

해석되었습니다.

경   과: 00:00:00.00


-------------------------------------------------------------------------
outline에서 어떻게 내부적인 힌트가 어떻게 사용되었는지 확인한다.
-------------------------------------------------------------------------
woong:WOONG >
  t1  select * from table(dbms_xplan.display(null,null,'outline'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 130649462

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    14 |    14   (0)| 00:00:01 |
|   1 |  CONCATENATION     |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |     7 |     7   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |     1 |     7 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1_2" "T1"@"SEL$1_2")
      FULL(@"SEL$1_1" "T1"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE_LEAF(@"SEL$1_2")
      USE_CONCAT(@"SEL$1" 8)
      OUTLINE_LEAF(@"SEL$1_1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.1.0.6')
      OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   2 - filter("C2"=TO_NUMBER(:B2))
   3 - filter("C1"=TO_NUMBER(:B1) AND LNNVL("C2"=TO_NUMBER(:B2)))

35 개의 행이 선택되었습니다.

경   과: 00:00:00.06


----------------------------------------------------------------------
Outline Data에서 ACCESS hint가 나오는 부분의 Query Block Name / Object Alias부분을 힌트에 적용한다.
----------------------------------------------------------------------
woong:WOONG >
  t1  explain plan for
  2  select /*+ use_concat
  3              FULL(@"SEL$1_1" "T1"@"SEL$1")
  4             INDEX(@"SEL$1_2" "T1"@"SEL$1_2" ("T1"."C1")) */
  5         *
  6    from t1
  7   where c1 = :b1 or c2 = :b2;

해석되었습니다.

경   과: 00:00:00.00
woong:WOONG >
  t1  @plan

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 647657254

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    14 |     9   (0)| 00:00:01 |
|   1 |  CONCATENATION               |       |       |       |            |          |
|*  2 |   TABLE ACCESS FULL          | T1    |     1 |     7 |     7   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - filter("C2"=TO_NUMBER(:B2))
   3 - filter(LNNVL("C2"=TO_NUMBER(:B2)))
   4 - access("C1"=TO_NUMBER(:B1))

18 개의 행이 선택되었습니다.

경   과: 00:00:00.01
Full Hint Naming Convention (by kkabong) [2009.04.30]
