版本:10.2.0.4
Hints for Access Paths
使用hints影响访问路径
Full
Rowid
Cluster和Hash
只用于cluster对象,暂不给出例子
Index
Index_asc
Index_combine
Index_join
Index_desc
Index_ffs
No_index
And_equal
具体使用的例子如下:
SQL> select a.empno from scott.emp a;
Execution Plan
----------------------------------------------------------
Plan hash value: 179099197
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL>
SQL> select /*+ full(a)*/ a.empno from scott.emp a ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL>
SQL> select /*+ rowid(a)*/a.empno from scott.emp a where rowid > '0';
Execution Plan
----------------------------------------------------------
Plan hash value: 2267975152
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY ROWID RANGE| EMP | 1 | 16 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(ROWID>'00000000.0000.0000')
SQL>
SQL> select /*+ index(a) index(b) */a.empno,b.dname from scott.emp a,scott.dept b
2 where a.deptno = b.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 3685117374
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 260 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 13 | 260 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 13 | 91 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPTNO"="B"."DEPTNO")
4 - filter("A"."DEPTNO" IS NOT NULL)
SQL>
SQL> select /*+ index(b) */a.empno,b.dname from scott.emp a,scott.dept b
2 where a.deptno = b.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2622742753
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 260 | 6 (17)| 00:00:01 |
|* 1 | HASH JOIN | | 13 | 260 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 13 | 91 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPTNO"="B"."DEPTNO")
4 - filter("A"."DEPTNO" IS NOT NULL)
SQL>
SQL> select /*+ index(a) */a.empno,b.dname from scott.emp a,scott.dept b
2 where a.deptno = b.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 4161849850
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 260 | 5 (20)| 00:00:01 |
| 1 | MERGE JOIN | | 13 | 260 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 13 | 91 | 3 (34)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| EMP | 13 | 91 | 2 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."DEPTNO"="B"."DEPTNO")
filter("A"."DEPTNO"="B"."DEPTNO")
5 - filter("A"."DEPTNO" IS NOT NULL)
SQL>
SQL> select /*+ index(a pk_emp,b pk_dept) */a.empno,b.dname from scott.emp a,scott.dept b
2 where a.deptno = b.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 4161849850
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 260 | 5 (20)| 00:00:01 |
| 1 | MERGE JOIN | | 13 | 260 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 13 | 91 | 3 (34)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| EMP | 13 | 91 | 2 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."DEPTNO"="B"."DEPTNO")
filter("A"."DEPTNO"="B"."DEPTNO")
5 - filter("A"."DEPTNO" IS NOT NULL)
SQL>
SQL> select /*+ index(b pk_dept) index (a pk_emp) */
2 a.empno, b.dname
3 from scott.emp a, scott.dept b
4 where a.deptno = b.deptno
5 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3685117374
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 260 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 13 | 260 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 13 | 91 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPTNO"="B"."DEPTNO")
4 - filter("A"."DEPTNO" IS NOT NULL)
SQL>
SQL> select /*+ index(a IND_TEST_CLUSTERFACTOR1_NAME)*/
2 a.object_name
3 from t_test_clusterfactor1 a
4 where a.object_name <> 'a';
Execution Plan
----------------------------------------------------------
Plan hash value: 883406351
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55004 | 1289K| 270 (1)| 00:00:04 |
|* 1 | INDEX FULL SCAN | IND_TEST_CLUSTERFACTOR1_NAME | 55004 | 1289K| 270 (1)| 00:00:04 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."OBJECT_NAME"<>'a')
SQL>
SQL> select /*+ index(a)*/a.object_id from t_test_clusterfactor1 a;
Execution Plan
----------------------------------------------------------
Plan hash value: 2511989365
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55006 | 161K| 173 (2)| 00:00:03 |
| 1 | TABLE ACCESS FULL| T_TEST_CLUSTERFACTOR1 | 55006 | 161K| 173 (2)| 00:00:03 |
-------------------------------------------------------------------------------------------
SQL>
SQL> select /*+ index(a IND_TEST_CLUSTERFACTOR1_NAME)*/
2 a.object_name
3 from t_test_clusterfactor1 a
4 where a.object_name > 'a'
5 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1915591104
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17596 | 412K| 88 (2)| 00:00:02 |
|* 1 | INDEX RANGE SCAN| IND_TEST_CLUSTERFACTOR1_NAME | 17596 | 412K| 88 (2)| 00:00:02 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OBJECT_NAME">'a')
SQL>
SQL> select /*+ index_asc(a pk_emp) */a.empno from scott.emp a where a.empno > 0;
Execution Plan
----------------------------------------------------------
Plan hash value: 1567865628
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."EMPNO">0)
SQL>
SQL> select /*+ index_combine(a)*/* from scott.emp a;
Execution Plan
----------------------------------------------------------
Plan hash value: 4170700152
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 532 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 532 | 2 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
SQL>
SQL> select /*+ index_combine(a)*/
2 *
3 from scott.emp a
4 where a.empno <> 0
5 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 4170700152
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 532 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 532 | 2 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."EMPNO"<>0)
SQL>
SQL> select /*+ index_combine(t IND_TEST_CLUSTERFACTOR1) */
2 *
3 from t_test_clusterfactor1 t
4 where t.object_id > 0
5 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3163573030
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55006 | 4888K| 950 (1)| 00:00:12 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST_CLUSTERFACTOR1 | 55006 | 4888K| 950 (1)| 00:00:12 |
|* 2 | INDEX RANGE SCAN | IND_TEST_CLUSTERFACTOR1 | 55004 | | 193 (2)| 00:00:03 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_ID">0)
SQL>
SQL> select /*+ index_combine(t IND_TEST_CLUSTERFACTOR1) */
2 *
3 from t_test_clusterfactor1 t
4 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2511989365
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55006 | 4888K| 175 (3)| 00:00:03 |
| 1 | TABLE ACCESS FULL| T_TEST_CLUSTERFACTOR1 | 55006 | 4888K| 175 (3)| 00:00:03 |
-------------------------------------------------------------------------------------------
SQL>
SQL> select /*+ */ * from t_test_clusterfactor1 t
2 where t.object_id > 0;
Execution Plan
----------------------------------------------------------
Plan hash value: 2511989365
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55006 | 4888K| 175 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T_TEST_CLUSTERFACTOR1 | 55006 | 4888K| 175 (3)| 00:00:03 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_ID">0)
SQL>
SQL> select /*+ index_join(t IND_TEST_CLUSTERFACTOR1 IND_TEST_CLUSTERFACTOR1_NAME)*/
2 t.object_id, t.object_name
3 from t_test_clusterfactor1 t
4 where t.object_id > 2
5 and t.object_name <> 'a';
Execution Plan
----------------------------------------------------------
Plan hash value: 3779503118
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 108 | 341 (2)| 00:00:05 |
|* 1 | VIEW | index$_join$_001 | 4 | 108 | 341 (2)| 00:00:05 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | IND_TEST_CLUSTERFACTOR1 | 4 | 108 | 3 (34)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN| IND_TEST_CLUSTERFACTOR1_NAME | 4 | 108 | 337 (1)| 00:00:05 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_ID">2)
2 - access(ROWID=ROWID)
3 - access("T"."OBJECT_ID">2)
4 - filter("T"."OBJECT_NAME"<>'a')
SQL>
SQL> select /*+ */ count(*) from t_test_clusterfactor1 t;
Execution Plan
----------------------------------------------------------
Plan hash value: 3754382477
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 173 (2)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_TEST_CLUSTERFACTOR1 | 55006 | 173 (2)| 00:00:03 |
------------------------------------------------------------------------------------
SQL>
SQL> select /*+ index_ffs(t) */
2 count(t.object_id)
3 from t_test_clusterfactor1 t
4 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 4032570730
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 44 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX FAST FULL SCAN| IND_TEST_CLUSTERFACTOR1 | 55006 | 161K| 44 (3)| 00:00:01 |
-------------------------------------------------------------------------------------------------
SQL>
SQL> select /*+ no_index(t) */
2 count(t.object_id)
3 from t_test_clusterfactor1 t
4 where t.object_id = 1
5 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3754382477
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 174 (2)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| T_TEST_CLUSTERFACTOR1 | 1 | 3 | 174 (2)| 00:00:03 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."OBJECT_ID"=1)
SQL>
SQL> select /*+ */
2 count(t.object_id)
3 from t_test_clusterfactor1 t
4 where t.object_id = 1
5 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 484583943
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| IND_TEST_CLUSTERFACTOR1 | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_ID"=1)
SQL>
SQL> select /*+ and_equal(t) */
2 t.object_id
3 from t_test_clusterfactor1 t
4 where t.object_id = 1
5 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1117762036
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND_TEST_CLUSTERFACTOR1 | 1 | 3 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."OBJECT_ID"=1)
SQL>
SQL> select /*+ and_equal(t IND_TEST_CLUSTERFACTOR1 IND_TEST_CLUSTERFACTOR1_NAME)*/
2 t.object_id
3 from t_test_clusterfactor1 t
4 where t.object_id > 2
5 and t.object_name <> 'a'
6 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3163573030
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 108 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T_TEST_CLUSTERFACTOR1 | 4 | 108 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST_CLUSTERFACTOR1 | 4 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_NAME"<>'a')
2 - access("T"."OBJECT_ID">2)
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16179598/viewspace-672384/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16179598/viewspace-672384/