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

(on) Undocumented Hints

OPT_ESTIMATE	CARDINALITY
{code:sql	titleBGColor=#FFFFFF	title=OPT_ESTIMATE	borderStyle=solid} SELECT /+ GATHER_PLAN_STATISTICS NO_INDEX(T1) NO_INDEX(T2) USE_HASH(T1 T2) / * FROM T1, T2 WHERE T1.N4 = T2.N4 AND T1.N2 = 0 AND T2.N3 = 0;

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

OMem

1Mem

Used-Mem

0	SELECT STATEMENT		1		1000	00:00:00.01	127
1	HASH JOIN		1	12	1000	00:00:00.01	127	1180K	1180K	421K (0)
2	TABLE ACCESS FULL	T1	1	10	10	00:00:00.01	31
3	TABLE ACCESS FULL	T2	1	55	100	00:00:00.01	96

SELECT /*+ GATHER_PLAN_STATISTICS NO_INDEX(T1) NO_INDEX(T2) USE_HASH(T1 T2) OPT_ESTIMATE(TABLE, T1, SCALE_ROWS=1000) */ *
FROM T1, T2
WHERE T1.N4 = T2.N4
AND T1.N2 = 0
AND T2.N3 = 0;

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

OMem

1Mem

Used-Mem

0	SELECT STATEMENT		1		1000	00:00:00.01	70
1	HASH JOIN		1	133	1000	00:00:00.01	70	1156K	1156K	430K (0)
2	TABLE ACCESS FULL	T2	1	100	100	00:00:00.01	29
3	TABLE ACCESS FULL	T1	1	1048	10	00:00:00.01	41

SELECT /*+ GATHER_PLAN_STATISTICS NO_INDEX(T1) NO_INDEX(T2) USE_HASH(T1 T2) OPT_ESTIMATE(TABLE, T2, SCALE_ROWS=1000) */ *
FROM T1, T2
WHERE T1.N4 = T2.N4
AND T1.N2 = 0
AND T2.N3 = 0;

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

OMem

1Mem

Used-Mem

0	SELECT STATEMENT		1		1000	00:00:00.01	127
1	HASH JOIN		1	154	1000	00:00:00.01	127	1180K	1180K	439K (0)
2	TABLE ACCESS FULL	T1	1	10	10	00:00:00.01	31
3	TABLE ACCESS FULL	T2	1	1000	100	00:00:00.01	96

-- 쌔거

|{code:sql|titleBGColor=#FFFFFF|title=CARDINALITY|borderStyle=solid}
SELECT /*+ GATHER_PLAN_STATISTICS NO_INDEX(T1) NO_INDEX(T2) USE_HASH(T1 T2) CARDINALITY(T1, 1000) */ *
  FROM T1, T2
 WHERE T1.N4 = T2.N4
   AND T1.N2 = 0
   AND T2.N3 = 0;
   
----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000 |00:00:00.01 |      70 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |   5500 |   1000 |00:00:00.01 |      70 |  1156K|  1156K|  392K (0)|
|*  2 |   TABLE ACCESS FULL| T2   |      1 |    100 |    100 |00:00:00.01 |      29 |       |       |          |
|*  3 |   TABLE ACCESS FULL| T1   |      1 |   1000 |     10 |00:00:00.01 |      41 |       |       |          |
----------------------------------------------------------------------------------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS NO_INDEX(T1) NO_INDEX(T2) USE_HASH(T1 T2) CARDINALITY(T2, 1000) */ *
  FROM T1, T2
 WHERE T1.N4 = T2.N4
   AND T1.N2 = 0
   AND T2.N3 = 0;

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000 |00:00:00.01 |     127 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |   1000 |   1000 |00:00:00.01 |     127 |  1180K|  1180K|  392K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |     10 |     10 |00:00:00.01 |      31 |       |       |          |
|*  3 |   TABLE ACCESS FULL| T2   |      1 |   1000 |    100 |00:00:00.01 |      96 |       |       |          |
----------------------------------------------------------------------------------------------------------------

-- 흔거

|

NATIVE_FULL_OUTER_JOIN	NO_NATIVE_FULL_OUTER_JOIN
{code:sql	titleBGColor=#FFFFFF	title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/native_foj_hint.gif!	borderStyle=solid} SELECT /+ NATIVE_FULL_OUTER_JOIN / T1.N4, COUNT(*) FROM T1 FULL OUTER JOIN T2 ON T1.N4 = T2.N4 GROUP BY T1.N4;

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

0	SELECT STATEMENT		10	30	11 (19)	00:00:01
1	HASH GROUP BY		10	30	11 (19)	00:00:01
2	VIEW	VW_FOJ_0	100	300	10 (10)	00:00:01
3	HASH JOIN FULL OUTER		100	900	10 (10)	00:00:01
4	INDEX FAST FULL SCAN	T2N4	10000	30000	7 (0)	00:00:01
5	INDEX FAST FULL SCAN	T1N4	10000	30000	2 (0)	00:00:01

|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/no_native_foj_hint.gif!|borderStyle=solid}
SELECT /*+ NO_NATIVE_FULL_OUTER_JOIN */ T1.N4, COUNT(*)
  FROM T1 FULL OUTER JOIN T2
    ON T1.N4 = T2.N4
 GROUP BY T1.N4;

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    21 |   273 |     5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY        |      |    21 |   273 |     5  (20)| 00:00:01 |
|   2 |   VIEW                |      |    21 |   273 |     4   (0)| 00:00:01 |
|   3 |    UNION-ALL          |      |       |       |            |          |
|   4 |     NESTED LOOPS OUTER|      |    10 |    60 |     2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN  | T1N4 |     1 |     3 |     1   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN | T2N4 |    10 |    30 |     1   (0)| 00:00:01 |
|   7 |     NESTED LOOPS ANTI |      |    11 |    66 |     2   (0)| 00:00:01 |
|   8 |      INDEX FULL SCAN  | T2N4 |   220 |   660 |     1   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN | T1N4 |  9500 | 28500 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

|

SWAP_JOIN_INPUTS	NO_SWAP_JOIN_INPUTS
{code:sql	titleBGColor=#FFFFFF	title=SWAP_JOIN_INPUTS	borderStyle=solid} SELECT /+ GATHER_PLAN_STATISTICS USE_HASH(T1 T2) / * FROM T1, T2 WHERE T1.N4 = T2.N4(+) AND T1.N1 = 0 AND T2.N3(+) = 0;

--

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

OMem

1Mem

Used-Mem

--

0	SELECT STATEMENT		1		100	00:00:00.01	20
1	HASH JOIN OUTER		1	10	100	00:00:00.01	20	1180K	1180K	392K (0)
2	TABLE ACCESS BY INDEX ROWID	T1	1	1	1	00:00:00.01	3
3	INDEX UNIQUE SCAN	T1PK	1	1	1	00:00:00.01	2
4	TABLE ACCESS BY INDEX ROWID	T2	1	100	100	00:00:00.01	17
5	INDEX RANGE SCAN	T2N3	1	100	100	00:00:00.01	9

--

SELECT /*+ GATHER_PLAN_STATISTICS USE_HASH(T1 T2) SWAP_JOIN_INPUTS(T2) */ *
FROM T1, T2
WHERE T1.N4 = T2.N4(+)
AND T1.N1 = 0
AND T2.N3(+) = 0;

--

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

OMem

1Mem

Used-Mem

--

0	SELECT STATEMENT		1		100	00:00:00.01	6
1	HASH JOIN RIGHT OUTER		1	10	100	00:00:00.01	6	1156K	1156K	397K (0)
2	TABLE ACCESS BY INDEX ROWID	T2	1	100	100	00:00:00.01	3
3	INDEX RANGE SCAN	T2N3	1	100	100	00:00:00.01	2
4	TABLE ACCESS BY INDEX ROWID	T1	1	1	1	00:00:00.01	3
5	INDEX UNIQUE SCAN	T1PK	1	1	1	00:00:00.01	2

--

|{code:sql|titleBGColor=#FFFFFF|title=NO_SWAP_JOIN_INPUTS|borderStyle=solid}
SELECT /*+ GATHER_PLAN_STATISTICS USE_HASH(T1 T2) NO_SWAP_JOIN_INPUTS(T2) */ *
  FROM T1, T2
 WHERE T1.N4 = T2.N4(+)
   AND T1.N1 = 0
   AND T2.N3(+) = 0;

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |        |    100 |00:00:00.01 |      20 |       |       |          |
|*  1 |  HASH JOIN OUTER             |      |      1 |     10 |    100 |00:00:00.01 |      20 |  1180K|  1180K|  421K (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|*  3 |    INDEX UNIQUE SCAN         | T1PK |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2   |      1 |    100 |    100 |00:00:00.01 |      17 |       |       |          |
|*  5 |    INDEX RANGE SCAN          | T2N3 |      1 |    100 |    100 |00:00:00.01 |       9 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------

|

NO_ELIMINATE_JOIN	ELIMINATE_JOIN
{code:sql	titleBGColor=#FFFFFF	title=NO_ELIMINATE_JOIN	borderStyle=solid} SELECT T1.N1 FROM T1, T2 WHERE T1.N1 = T2.N1(+) AND T1.N1 = 0;

--

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

--

0	SELECT STATEMENT		1	4	1 (0)	00:00:01
1	INDEX UNIQUE SCAN	T1PK	1	4	1 (0)	00:00:01

--

SELECT /*+ NO_ELIMINATE_JOIN(T2) */ T1.N1
FROM T1, T2
WHERE T1.N1 = T2.N1(+)
AND T1.N1 = 0;

---

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

---

0	SELECT STATEMENT		1	8	2 (0)	00:00:01
1	NESTED LOOPS OUTER		1	8	2 (0)	00:00:01
2	INDEX UNIQUE SCAN	T1PK	1	4	1 (0)	00:00:01
3	INDEX UNIQUE SCAN	T2PK	1	4	1 (0)	00:00:01

---

|{code:sql|titleBGColor=#FFFFFF|title=ELIMINATE_JOIN|borderStyle=solid}
SELECT /*+ ELIMINATE_JOIN(T2) */ T1.N1
  FROM T1, T2
 WHERE T1.N1 = T2.N1(+)
   AND T1.N1 = 0;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| T1PK |     1 |     4 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

|

ELIMINATE_OBY	NO_ELIMINATE_OBY
{code:sql	titleBGColor=#FFFFFF	title=ELIMINATE_OBY	borderStyle=solid} SELECT /+ ELIMINATE_OBY(@QB) / A.N4, SUM(A.N1) FROM (SELECT /+ QB_NAME(QB) / N4, N1 FROM T1 WHERE N2 = 0 ORDER BY N3) A GROUP BY A.N4;

-

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

-

0	SELECT STATEMENT		7	77	2 (50)	00:00:01
1	HASH GROUP BY		7	77	2 (50)	00:00:01
2	TABLE ACCESS BY INDEX ROWID	T1	10	110	1 (0)	00:00:01
3	INDEX RANGE SCAN	T1N2	10		1 (0)	00:00:01

-

|{code:sql|titleBGColor=#FFFFFF|title=NO_ELIMINATE_OBY|borderStyle=solid}
SELECT /*+ NO_ELIMINATE_OBY(@QB) */ A.N4, SUM(A.N1) FROM (SELECT /*+ QB_NAME(QB) */ N4, N1 FROM T1 WHERE N2 = 0 ORDER BY N3) A GROUP BY A.N4;

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |    10 |   160 |     3  (67)| 00:00:01 |
|   1 |  HASH GROUP BY                 |      |    10 |   160 |     3  (67)| 00:00:01 |
|   2 |   VIEW                         |      |    10 |   160 |     2  (50)| 00:00:01 |
|   3 |    SORT ORDER BY               |      |    10 |   140 |     2  (50)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1   |    10 |   140 |     1   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T1N2 |    10 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

|

Undocumented Hints (by openlsw) [2011.10.19]

(on) Undocumented Hints