SQL> create table t as select * from dba_objects;
表已创建。
SQL> create index idx_t on t (object_id);
索引已创建。
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL 过程已成功完成。
--full() 提示 执行计划走全表扫描
SQL> select /*+ full(t) */ * from t;
已选择68298行。
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68298 | 6736K| 276 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| T | 68298 | 6736K| 276 (1)| 00:00:04 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
5493 consistent gets
0 physical reads
0 redo size
7807613 bytes sent via SQL*Net to client
50499 bytes received via SQL*Net from client
4555 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
68298 rows processed
SQL> select /*+ index(t idx_t) */ * from t;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68298 | 6736K| 276 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| T | 68298 | 6736K| 276 (1)| 00:00:04 |
--------------------------------------------------------------------------
---上面的没有走索引，由于没有使用到索引列，也说明了提示只是给优化器一个建议，
----如果不符合条件，cbo仍然会自己选择合适的执行计划。
---index() 提示优化器执行计划走指定表的索引
SQL> select /*+ index(t idx_t) */ * from t where object_id>1;
已选择68298行。
执行计划
----------------------------------------------------------
Plan hash value: 1594971208
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68298 | 6736K| 1189 (1)| 00:00:15 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 68298 | 6736K| 1189 (1)| 00:00:15 |
|* 2 | INDEX RANGE SCAN | IDX_T | 68298 | | 153 (1)| 00:00:02 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
10218 consistent gets
0 physical reads
0 redo size
7807613 bytes sent via SQL*Net to client
50499 bytes received via SQL*Net from client
4555 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
68298 rows processed
--no_index() 告诉优化器禁止走指定表的索引
SQL> select /*+ no_index(t idx_t) */ * from t where object_id=1;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 101 | 275 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 1 | 101 | 275 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1)
--index_desc()告诉优化器对指定表的索引使用降序方式访问数据。
SQL> select /*+ index_desc(t idx_t) */ * from t where object_id<200;
已选择191行。
执行计划
----------------------------------------------------------
Plan hash value: 2821899338
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 194 | 19594 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 194 | 19594 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| IDX_T | 194 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<200)
filter("OBJECT_ID"<200)
SQL> create bitmap index ibm_t on t (object_name);
索引已创建。
---index_combine()提示告诉优化器选择走位图索引。
SQL> select /*+ index_combine (t ibm_t) */ * from t;
已选择68298行。
执行计划
----------------------------------------------------------
Plan hash value: 2891273134
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68298 | 6736K| 1861 (1)| 00:00:23 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 68298 | 6736K| 1861 (1)| 00:00:23 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 3 | BITMAP INDEX FULL SCAN | IBM_T | | | | |
--------------------------------------------------------------------------------------
----index_ffs () 提示优化器选择快速全索引扫描的方式访问数据。
SQL> select /*+ index_ffs (t idx_t) */ object_id from t where object_id <200;
已选择191行。
执行计划
----------------------------------------------------------
Plan hash value: 2497555198
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 194 | 970 | 44 (3)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_T | 194 | 970 | 44 (3)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<200)
统计信息
---下面的这个jion写错了，所以提示被当作注释信息。。
SQL> select /*+ index_jion (t idx_t ibm_t) */ object_id from t
2 where object_id >200 and status ='vaild';
未选定
执行计划
----------------------------------------------------------
Plan hash value: 1601196873 -----------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 275 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 1 | 11 | 275 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"='vaild' AND "OBJECT_ID">200)
--- index_jion()提示优化器选择索引关联，当谓词中的引用列都为索引列时，可以使用此方式。
SQL> create bitmap index bitmap_t on t (status);
索引已创建。
SQL> select /*+ index_join (t idx_t ibm_t) */ object_id from t
2 where object_id < 200 and status ='vaild';
未选定
执行计划
----------------------------------------------------------
Plan hash value: 2966373114
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 4 (25)|00:00:01 |
|* 1 | VIEW | index$_join$_001 | 1 | 11 | 4 (25)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
| 3 | BITMAP CONVERSION TO ROWIDS| | 1 | 11 | 1 (0)|00:00:01 |
|* 4 | BITMAP INDEX SINGLE VALUE | BITMAP_T | | | | |
|* 5 | INDEX RANGE SCAN | IDX_T | 1 | 11 | 3 (34)|00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"='vaild' AND "OBJECT_ID"<200)
2 - access(ROWID=ROWID)
4 - access("STATUS"='vaild')
5 - access("OBJECT_ID"<200)
附上：当使用索引提示的时候，如果结果集不完整，优化器就会忽略这提示。
