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

(on) Hints for Access Paths

FULL
{code:sql	titleBGColor=#FFFFFF	title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/full_hint.gif!	borderStyle=solid} SELECT /+ FULL(T1) / * FROM T1 WHERE N3 = 50;

--

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

--

0	SELECT STATEMENT		10	140	3 (0)	00:00:01
1	TABLE ACCESS FULL	T1	10	140	3 (0)	00:00:01

--

SELECT * FROM T1 WHERE N3 = 50;

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

0	SELECT STATEMENT		10	140	1 (0)	00:00:01
1	TABLE ACCESS BY INDEX ROWID	T1	10	140	1 (0)	00:00:01
2	INDEX RANGE SCAN	T1N3	100		1 (0)	00:00:01

|

||CLUSTER||
|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/cluster_hint.gif!|borderStyle=solid}
SELECT /*+ FULL(T5) */ * FROM T5 WHERE T5.N4 = 0;

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

SELECT /*+ CLUSTER(T5) */ * FROM T5 WHERE T5.N4 = 0;

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    14 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS CLUSTER| T5   |     1 |    14 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN  | H2N4 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

|

HASH
{code:sql	titleBGColor=#FFFFFF	title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/hash_hint.gif!	borderStyle=solid} SELECT /+ FULL(T4) / * FROM T4 WHERE T4.N4 = 0;

--

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

--

0	SELECT STATEMENT		1	14	2 (0)	00:00:01
1	TABLE ACCESS FULL	T4	1	14	2 (0)	00:00:01

--

SELECT /*+ HASH(T4) */ * FROM T4 WHERE T4.N4 = 0;

--

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

--

0	SELECT STATEMENT		1	14	1 (0)	00:00:01
1	TABLE ACCESS HASH	T4	1	14	1 (0)	00:00:01

--

|

||INDEX||NO_INDEX||
|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/index_hint.gif!|borderStyle=solid}
SELECT /*+ INDEX(T1) */ * FROM T1 WHERE N2 > 0;

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    10 |   140 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |    10 |   140 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1N2 | 10000 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

SELECT /*+ INDEX(T1 T1N2) */ * FROM T1 WHERE N2 > 0;

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    10 |   140 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |    10 |   140 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1N2 |  9990 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

SELECT /*+ INDEX(T1 T1N3) */ * FROM T1 WHERE N2 > 0;

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    10 |   140 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1   |    10 |   140 |     1   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | T1N3 | 10000 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

|

!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/no_index_hint.gif!


SELECT /*+ NO_INDEX(T1) */ * FROM T1 WHERE N2 > 0;

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

SELECT /*+ NO_INDEX(T1 T1N2) */ * FROM T1 WHERE N2 > 0;

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

SELECT /*+ NO_INDEX(T1 T1N3) */ * FROM T1 WHERE N2 > 0;

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    10 |   140 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |    10 |   140 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1N2 |       |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

|

INDEX_ASC(=INDEX)	INDEX_DESC
{code:sql	titleBGColor=#FFFFFF	title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/index_asc_hint.gif!	borderStyle=solid} SELECT /+ INDEX_ASC(T1 T1N2) / * FROM T1 WHERE N2 > 0;

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

0	SELECT STATEMENT		10	140	1 (0)	00:00:01
1	TABLE ACCESS BY INDEX ROWID	T1	10	140	1 (0)	00:00:01
2	INDEX RANGE SCAN	T1N2	9990		1 (0)	00:00:01

|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/index_desc_hint.gif!|borderStyle=solid}
SELECT /*+ INDEX_DESC(T1 T1N2) */ * FROM T1 WHERE N2 > 0;

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |    10 |   140 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T1   |    10 |   140 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN DESCENDING| T1N2 |  9990 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

|

INDEX_COMBINE	INDEX_JOIN
{code:sql	titleBGColor=#FFFFFF	title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/index_combine_hint.gif!	borderStyle=solid} SELECT /+ INDEX_COMBINE(T1 T1N3 T1N4) / * FROM T1 WHERE N3 = 50 AND N4 = 5;

-

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

-

0	SELECT STATEMENT		10	140	2 (0)	00:00:01
1	TABLE ACCESS BY INDEX ROWID	T1	10	140	2 (0)	00:00:01
2	BITMAP CONVERSION TO ROWIDS
3	BITMAP AND
4	BITMAP CONVERSION FROM ROWIDS
5	INDEX RANGE SCAN	T1N3			1 (0)	00:00:01
6	BITMAP CONVERSION FROM ROWIDS
7	INDEX RANGE SCAN	T1N4			1 (0)	00:00:01

-

|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/index_join_hint.gif!|borderStyle=solid}
SELECT /*+ INDEX_JOIN(T1 T1N3 T1N4) */ * FROM T1 WHERE N3 = 50 AND N4 = 5;

--------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |    10 |   140 |    17  (12)| 00:00:01 |
|*  1 |  VIEW                   | index$_join$_001 |    10 |   140 |    17  (12)| 00:00:01 |
|*  2 |   HASH JOIN             |                  |       |       |            |          |
|*  3 |    HASH JOIN            |                  |       |       |            |          |
|*  4 |     HASH JOIN           |                  |       |       |            |          |
|*  5 |      INDEX RANGE SCAN   | T1N3             |    10 |   140 |     1   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN   | T1N4             |    10 |   140 |     1   (0)| 00:00:01 |
|   7 |     INDEX FAST FULL SCAN| T1N2             |    10 |   140 |     7   (0)| 00:00:01 |
|   8 |    INDEX FAST FULL SCAN | T1PK             |    10 |   140 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

|

INDEX_FFS	NO_INDEX_FFS
{code:sql	titleBGColor=#FFFFFF	title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/index_ffs_hint.gif!	borderStyle=solid} SELECT /+ INDEX_FFS(T1) / COUNT(N3) FROM T1;

--

Id

Operation

Name

Rows

Cost (%CPU)

Time

--

0	SELECT STATEMENT		1	7 (0)	00:00:01
1	SORT AGGREGATE		1
2	INDEX FAST FULL SCAN	T1N3	10000	7 (0)	00:00:01

--

|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/no_index_ffs_hint.gif!|borderStyle=solid}
SELECT /*+ NO_INDEX_FFS(T1) */ COUNT(N3) FROM T1;

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   2 |   INDEX FULL SCAN| T1N3 | 10000 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------

|

INDEX_SS	NO_INDEX_SS
{code:sql	titleBGColor=#FFFFFF	title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/index_ss_hint.gif!	borderStyle=solid} SELECT /+ NO_INDEX(T1 T1N3) INDEX_SS(T1) / COUNT(*) FROM T1 WHERE N3 = 50;

---

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

---

0	SELECT STATEMENT		1	3	3 (0)	00:00:01
1	SORT AGGREGATE		1	3
2	INDEX SKIP SCAN	T1N4N3	100	300	3 (0)	00:00:01

---

|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/no_index_ss_hint.gif!|borderStyle=solid}
SELECT /*+ NO_INDEX(T1 T1N3) NO_INDEX_SS(T1) */ COUNT(*) FROM T1 WHERE N3 = 50;

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |        |     1 |     3 |            |          |
|*  2 |   INDEX FULL SCAN| T1N4N3 |   100 |   300 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

|

INDEX_SS_ASC	INDEX_SS_DESC
{code:sql	titleBGColor=#FFFFFF	title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/index_ss_asc_hint.gif!	borderStyle=solid} SELECT /+ NO_INDEX(T1 T1N3) INDEX_SS_ASC(T1) / COUNT(*) FROM T1 WHERE N3 = 50;

---

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

---

0	SELECT STATEMENT		1	3	3 (0)	00:00:01
1	SORT AGGREGATE		1	3
2	INDEX SKIP SCAN	T1N4N3	100	300	3 (0)	00:00:01

---

|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/index_ss_desc_hint.gif!|borderStyle=solid}
SELECT /*+ NO_INDEX(T1 T1N3) INDEX_SS_DESC(T1) */ COUNT(*) FROM T1 WHERE N3 = 50;

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |        |     1 |     3 |            |          |
|*  2 |   INDEX SKIP SCAN DESCENDING| T1N4N3 |   100 |   300 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

|

Hints for Access Paths (by openlsw) [2011.10.19]

(on) Hints for Access Paths