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;

exec dbms_stats.gather_table_stats ('HYUN','t1',CASCADE=>TRUE,method_opt =>'FOR ALL COLUMNS SIZE REPEAT');


 select  *
   from t1
   where c1 = 1 or c2 = 5;
------------------------------------------------------------------------------------------
| Id  | Operation                        | Name  | Rows  | Bytes | Cost (%CPU)|Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |       |     2 |    14 |     2   (0)|00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | T1    |     2 |    14 |     2   (0)|00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |       |       |       |            |         |
|   3 |    BITMAP OR                     |       |       |       |            |         |
|   4 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |            |         |
|*  5 |      INDEX RANGE SCAN            | T1_N1 |       |       |     1   (0)|00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |            |         |
|*  7 |      INDEX RANGE SCAN            | T1_N2 |       |       |     1   (0)|00:00:01 |
------------------------------------------------------------------------------------------

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

   5 - access("C1"=1)
   7 - access("C2"=5)

=> or Expansion(혹은 Concatenation으로 변경하려면 use_concat 힌트 사용)
 select /*+ use_concat */ *
   from t1
   where c1 = 1 or c2 = 5;

--------------------------------------------------------------------------------------
| 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"=5)
   4 - filter(LNNVL("C2"=5))
   5 - access("C1"=1)

=> Column C1에 대한 predicate는 Table Full Scan으로,
   Column C2에 대한 predicate는 Index Range Scan으로 처리하고 싶다면?

* UNION ALL로 변경

 select /*+ full(t1) */ *
   from t1
   where c1 = 1
union all
 select /*+ index(t1) */ *
   from t1
   where c2 = 5
   
--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    14 |     9  (23)| 00:00:01 |
|   1 |  UNION-ALL                   |       |       |       |            |     |
|*  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_N2 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - filter("C1"=1)
   4 - access("C2"=5)   
   
* Full Hint Naming 사용

explain plan for
 select /*+ use_concat full(t1) */ *
   from t1
  where c1 = 1 or c2 = 5;
  
select * from table(dbms_xplan.display(null,null,'outline'));
  
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    14 |    15   (7)| 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 힌트를 사용한 부분
      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
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */


   3 - filter("C1"=1 AND LNNVL("C2"=5))   


=> 특정 Query Block의 특정 Object에 대해서만 힌트 부여

explain plan for
 select /*+ use_concat FULL(@"SEL$1_1" "T1"@"SEL$1") index(@"SEL$1_2" "T1"@"SEL$1_2") */ *
   from t1
  where c1 = 1 or c2 = 5;
  
select * from table(dbms_xplan.display(null,null,'outline'));

--------------------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------------------  

  * union all을 사용하지 않고도 첫번째 Query Block에 대해서는 Table Full Scan을, 
    두번째 Query Block에 대해서는 Index Range Scan을 수행하도록 할 수 있다.