(on) Hints for Query Transformations

SELECT /*+ NO_QUERY_TRANSFORMATION */ N1 FROM (SELECT * FROM T1 A) V WHERE V.N1 = 1;

|

||USE_CONCAT||NO_EXPAND||
|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/use_concat_hint.gif!|borderStyle=solid}
SELECT /*+ USE_CONCAT */ COUNT(*) FROM T1 WHERE N1 = 10 OR N2 = 10;

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |     8 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |      |     1 |     8 |            |          |
|   2 |   CONCATENATION               |      |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1   |     1 |     8 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | T1PK |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| T1   |    10 |    80 |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | T1N2 |    10 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

-- OR-Expansion 유도

|

SELECT /*+ NO_EXPAND */ COUNT(*) FROM T1 WHERE N1 = 10 OR N2 = 10;

-----------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |     1 |     8 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                  |      |     1 |     8 |            |          |
|   2 |   BITMAP CONVERSION COUNT        |      |    11 |    88 |     2   (0)| 00:00:01 |
|   3 |    BITMAP OR                     |      |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | T1N2 |       |       |     1   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |
|*  7 |      INDEX RANGE SCAN            | T1PK |       |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

-- OR-Expansion 방지

|

|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/no_rewrite_hint.gif!|borderStyle=solid}
SELECT /*+ NO_REWRITE */ N4, COUNT(*) AS CNT FROM T1 GROUP BY N4;

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    10 |    30 |     5   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|      |    10 |    30 |     5   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN    | T1N4 | 10000 | 30000 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------

|

|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/no_merge_hint.gif!|borderStyle=solid}
SELECT /*+ NO_MERGE(X) */ COUNT(*) FROM T1, (SELECT N4 FROM T2 WHERE N2 = 10) X WHERE T1.N4 = X.N4;

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |     1 |     6 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                |      |     1 |     6 |            |          |
|   2 |   NESTED LOOPS                 |      |  5000 | 30000 |     2   (0)| 00:00:01 |
|   3 |    VIEW                        |      |    10 |    30 |     1   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T2   |    10 |    70 |     1   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T2N2 |    10 |       |     1   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN            | T1N4 |   500 |  1500 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

|

-- 서브쿼리를 조인형태로 변형

|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/no_unnest_hint.gif!|borderStyle=solid}
SELECT /*+ NO_UNNEST(@QB) */COUNT(*) FROM T1 WHERE T1.N1 IN (SELECT /*+ QB_NAME(QB) */ N4 FROM T2 WHERE N1 = 10);

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |     4 |  5007   (1)| 00:01:01 |
|   1 |  SORT AGGREGATE               |      |     1 |     4 |            |          |
|*  2 |   INDEX FULL SCAN             | T1PK |   500 |  2000 |     5   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2   |     1 |     7 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | T2PK |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

|

– FACT TABLE : T2 매출
– DIMENSION TABLE : T1(A) 고객, T1(B) 상품, T1© 부서

– BITMAP 연산을 통해 FACT TABLE(T2) 범위를 줄인 후 각 DIMENSION TABLE(T1(A), T1(B), T1©)과 JOIN

|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/no_star_transformation_hint.gif!|borderStyle=solid}
SELECT /*+ NO_STAR_TRANSFORMATION */ X.N4, COUNT(*)
  FROM T1 A, T1 B, T1 C, T2 X
 WHERE X.N1 = A.N1
   AND X.N2 = B.N2
   AND X.N3 = C.N3
 GROUP BY X.N4;

----------------------------------------------------------------------------------------
| Id  | Operation                       | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |      |    10 |   250 |    68  (78)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT           |      |    10 |   250 |    68  (78)| 00:00:01 |
|   2 |   NESTED LOOPS                  |      |    10M|   238M|    68  (78)| 00:00:01 |
|   3 |    NESTED LOOPS                 |      |   100K|  2148K|    15   (7)| 00:00:01 |
|   4 |     NESTED LOOPS                |      | 10000 |   175K|    13   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| T2   | 10000 |   136K|    12   (0)| 00:00:01 |
|   6 |       INDEX FULL SCAN           | T2N4 | 10000 |       |     5   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN          | T1PK |     1 |     4 |     1   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN            | T1N2 |    10 |    40 |     1   (0)| 00:00:01 |
|*  9 |    INDEX RANGE SCAN             | T1N3 |   100 |   300 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

|