【sql hint 提示】
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)
附上：當使用索引提示的時候，如果結果集不完整，最佳化器就會忽略這提示。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-676412/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL SQL hint 提示MySql
- 【HINT】使用“NO_INDEX ”Hint提示避免SQL使用特定索引IndexSQL索引
- Oracle 12c 新SQL提示（hint）OracleSQL
- Sql最佳化(五) hint（提示）介紹SQL
- SQL hint中正確使用use_nl提示SQL
- oracle sql hint提示_BITMAP CONVERSION FROM ROWIDSOracleSQL
- 【SQL 提示 之二】index_ss Index Skip HintSQLIndex
- sql hint articleSQL
- Oralce SQL hintSQL
- Oracle中的sql hintOracleSQL
- openGauss 支援SQL-hintSQL
- 【sql調優之執行計劃】使用hint(五)Hint for parallelSQLParallel
- 檢視SQL PROFILE使用的HINTSQL
- 11G new SQL hint大全SQL
- 使用Oracle Hint提示來更改執行計劃Oracle
- oracle 通過sql profile為sql語句加hintOracleSQL
- 使用hint來調優sql語句SQL
- SQL Server調優系列玩轉篇（如何利用查詢提示（Hint）引導語句執行）SQLServer
- 使用Oracle Hint提示來更改執行計劃 ZTOracle
- 通過hint push_subq優化sql優化SQL
- sql調優一例---索引排序hintSQL索引排序
- 關於append sql hint的實驗APPSQL
- ORACLE常用SQL最佳化hint語句OracleSQL
- SQL Server調優系列玩轉篇三（利用索引提示（Hint）引導語句最大優化執行）SQLServer索引優化
- ORACLE 通過SPM為SQL語句加HINTOracleSQL
- 透過使用hint unnest調優sql語句SQL
- 通過使用hint unnest調優sql語句SQL
- SQL hint中的ORDER與Leading(zt)SQL
- 【SQL 提示 之三】SQL
- [Oracle] Append hint(insert sql) will lead a TM LOCKOracleAPPSQL
- 影片直播系統原始碼，Android EditText不顯示提示文字hint原始碼Android
- [20190430]注意sql hint寫法.txtSQL
- 使用USE_HASH Hint調優一個SQL語句SQL
- cursor_sharing=force導致sql profile部分hint失效SQL
- SQL語句優化方法用hint的30種方法SQL優化
- oracle hintOracle
- [20200801]sql hint衝突.txtSQL
- 【sql調優之執行計劃】使用hint(四)Hints for JoinSQL