1、Oracle提供的示例如下:
SQL> select * from v$version where rownum<2;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
SQL> create table t01 as select * from dba_objects;
SQL> create index ind_t01_object_id on t01(object_id);
SQL> analyze table t01 compute statistics;
SQL> BEGIN
SYS.DBMS_SQLDIAG_INTERNAL.i_create_patch(
sql_text => 'select * from t01 where object_id between 10000 and 30000',
hint_text => 'INDEX(T01,IND_T01_OBJECT_ID)',
name => 't01_sql_patch');
END;
/
These manual SQL patches can be removed in the normal manner.
SQL> BEGIN
DBMS_SQLDIAG.drop_sql_patch(name =>'t01_sql_patch');
END;
/
SQL> select * from t01 where object_id between 10000 and 30000;
19581 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3295674804
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19706 | 2001K| 431 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T01 | 19706 | 2001K| 431 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<=30000 AND "OBJECT_ID">=10000)
Note
-----
- SQL patch "t01_sql_patch" used for this statement
Statistics
----------------------------------------------------------
458 recursive calls
0 db block gets
3213 consistent gets
1 physical reads
0 redo size
1058572 bytes sent via SQL*Net to client
14906 bytes received via SQL*Net from client
1307 SQL*Net roundtrips to/from client
36 sorts (memory)
0 sorts (disk)
19581 rows processed
使用DBMS_XPLAN.DISPLAY_CURSOR查看该SQL对应的执行计划还是全表扫描,就是没有想要的索引范围扫描,HINT也是对的
SQL> select /*+INDEX(T01,IND_T01_OBJECT_ID)*/* from t01 where object_id between 10000 and 30000;
19624 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 95018110
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19825 | 2013K| 596 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T01 | 19825 | 2013K| 596 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T01_OBJECT_ID | 19825 | | 45 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=10000 AND "OBJECT_ID"<=30000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3173 consistent gets
0 physical reads
0 redo size
2573622 bytes sent via SQL*Net to client
14939 bytes received via SQL*Net from client
1310 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19624 rows processed