2019獨(dú)角獸企業(yè)重金招聘Python工程師標(biāo)準(zhǔn)>>>

Hint概述?
基于代價(jià)的優(yōu)化器是很聰明的，在絕大多數(shù)情況下它會(huì)選擇正確的優(yōu)化器，減輕了DBA的負(fù)擔(dān)。但有時(shí)它也聰明反被聰明誤，選擇了很差的執(zhí)行計(jì)劃，使某個(gè)語(yǔ)句的執(zhí)行變得奇慢無(wú)比。

此時(shí)就需要DBA進(jìn)行人為的干預(yù)，告訴優(yōu)化器使用我們指定的存取路徑或連接類(lèi)型生成執(zhí)行計(jì)劃，從 而使語(yǔ)句高效的運(yùn)行。例如，如果我們認(rèn)為對(duì)于一個(gè)特定的語(yǔ)句，執(zhí)行全表掃描要比執(zhí)行索引掃描更有效，則我們就可以指示優(yōu)化器使用全表掃描。在Oracle 中，是通過(guò)為語(yǔ)句添加 Hints(提示)來(lái)實(shí)現(xiàn)干預(yù)優(yōu)化器優(yōu)化的目的。
不建議在代碼中使用hint，在代碼使用hint使得CBO無(wú)法根據(jù)實(shí)際的數(shù)據(jù)狀態(tài)選擇正確的執(zhí)行計(jì)劃。畢竟?數(shù)據(jù)是不斷變化的，?10g以后的CBO也越來(lái)越完善，大多數(shù)情況下我們?cè)撟孫racle自行決定采用什么執(zhí)行計(jì)劃。

Oracle Hints是一種機(jī)制，用來(lái)告訴優(yōu)化器按照我們的告訴它的方式生成執(zhí)行計(jì)劃。我們可以用Oracle Hints來(lái)實(shí)現(xiàn)：
1) 使用的優(yōu)化器的類(lèi)型
2) 基于代價(jià)的優(yōu)化器的優(yōu)化目標(biāo)，是all_rows還是first_rows。
3) 表的訪(fǎng)問(wèn)路徑，是全表掃描，還是索引掃描，還是直接利用rowid。
4) 表之間的連接類(lèi)型
5) 表之間的連接順序
6) 語(yǔ)句的并行程度

除了”RULE”提示外，一旦使用的別的提示，語(yǔ)句就會(huì)自動(dòng)的改為使用CBO優(yōu)化器，此時(shí)如果你的數(shù)據(jù)字典中沒(méi)有統(tǒng)計(jì)數(shù)據(jù)，就會(huì)使用缺省的統(tǒng)計(jì)數(shù)據(jù)。所以建議大家如果使用CBO或Hints提示，則最好對(duì)表和索引進(jìn)行定期的分析。

如何使用Hints:

Hints只應(yīng)用在它們所在sql語(yǔ)句塊(statement block，由select、update、delete關(guān)鍵字標(biāo)識(shí))上，對(duì)其它SQL語(yǔ)句或語(yǔ)句的其它部分沒(méi)有影響。如：對(duì)于使用union操作的2個(gè)sql語(yǔ)句，如果只在一個(gè)sql語(yǔ)句上有Hints，則該Hints不會(huì)影響另一個(gè)sql語(yǔ)句。

我們可以使用注釋(comment)來(lái)為一個(gè)語(yǔ)句添加Hints，一個(gè)語(yǔ)句塊只能有一個(gè)注釋，而且注釋只能放在SELECT, UPDATE, or DELETE關(guān)鍵字的后面

使用Oracle Hints的語(yǔ)法：

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

or

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

注解：
1) DELETE、INSERT、SELECT和UPDATE是標(biāo)識(shí)一個(gè)語(yǔ)句塊開(kāi)始的關(guān)鍵字，包含提示的注釋只能出現(xiàn)在這些關(guān)鍵字的后面，否則提示無(wú)效。
2) “+”號(hào)表示該注釋是一個(gè)Hints，該加號(hào)必須立即跟在”/*”的后面，中間不能有空格。
3) hint是下面介紹的具體提示之一，如果包含多個(gè)提示，則每個(gè)提示之間需要用一個(gè)或多個(gè)空格隔開(kāi)。
4) text 是其它說(shuō)明hint的注釋性文本

5)使用表別名。如果在查詢(xún)中指定了表別名，那么提示必須也使用表別名。例如：select /*+ index(e,dept_idx) */ * from emp e;
6)不要在提示中使用模式名稱(chēng)：如果在提示中指定了模式的所有者，那么提示將被忽略。例如：
select /*+ index(scott.emp,dept_idx) */ * from emp

注意：如果你沒(méi)有正確的指定Hints，Oracle將忽略該Hints，并且不會(huì)給出任何錯(cuò)誤。
hint被忽略

如果CBO認(rèn)為使用hint會(huì)導(dǎo)致錯(cuò)誤的結(jié)果時(shí)，hint將被忽略，詳見(jiàn)下例
SQL> select /*+ index(t t_ind) */ count(*) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Cost (%CPU)| Time???? |
-------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 57?? (2)| 00:00:01 |
|?? 1 |? SORT AGGREGATE??? |????? |???? 1 |??????????? |????????? |
|?? 2 |?? TABLE ACCESS FULL| T??? | 50366 |??? 57?? (2)| 00:00:01 |
-------------------------------------------------------------------

因?yàn)槲覀兪菍?duì)記錄求總數(shù)，且我們并沒(méi)有在建立索引時(shí)指定不能為空，索引如果CBO選擇在索引上進(jìn)行count時(shí)，但索引字段上的值為空時(shí)，結(jié)果將不準(zhǔn)確，故CBO沒(méi)有選擇索引。?
SQL>? select /*+ index(t t_ind) */ count(id) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 646498162
--------------------------------------------------------------------------
| Id? | Operation??????? | Name? | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT |?????? |???? 1 |???? 5 |?? 285?? (1)| 00:00:04 |
|?? 1 |? SORT AGGREGATE? |?????? |???? 1 |???? 5 |??????????? |????????? |
|?? 2 |?? INDEX FULL SCAN| T_IND | 50366 |?? 245K|?? 285?? (1)| 00:00:04 |
--------------------------------------------------------------------------

因?yàn)槲覀冎粚?duì)id進(jìn)行count，這個(gè)動(dòng)作相當(dāng)于count索引上的所有id值，這個(gè)操作和對(duì)表上的id字段進(jìn)行count是一樣的(組函數(shù)會(huì)忽略null值)

Hint的具體用法

和優(yōu)化器相關(guān)的hint

1、/*+ ALL_ROWS */
表明對(duì)語(yǔ)句塊選擇基于開(kāi)銷(xiāo)的優(yōu)化方法,并獲得最佳吞吐量,使資源消耗最小化.
SELECT /*+ ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';?
2、/*+ FIRST_ROWS(n) */
表明對(duì)語(yǔ)句塊選擇基于開(kāi)銷(xiāo)的優(yōu)化方法,并獲得最佳響應(yīng)時(shí)間,使資源消耗最小化.
SELECT /*+FIRST_ROWS(20) */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';?
3、/*+ RULE*/
表明對(duì)語(yǔ)句塊選擇基于規(guī)則的優(yōu)化方法.
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';?
和訪(fǎng)問(wèn)路徑相關(guān)的hint

1、/*+ FULL(TABLE)*/
表明對(duì)表選擇全局掃描的方法.
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';?
2、/*+ INDEX(TABLE INDEX_NAME) */
表明對(duì)表選擇索引的掃描方法.
SELECT /*+INDEX(BSEMPMS SEX_INDEX) */ * FROM BSEMPMS WHERE SEX='M';?
5、/*+ INDEX_ASC(TABLE INDEX_NAME)*/
表明對(duì)表選擇索引升序的掃描方法.
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ * FROM BSEMPMS WHERE DPT_NO='SCOTT';?
6、/*+ INDEX_COMBINE*/
為指定表選擇位圖訪(fǎng)問(wèn)路經(jīng),如果INDEX_COMBINE中沒(méi)有提供作為參數(shù)的索引,將選擇出位圖索引的布爾組合方式.
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI) */? * FROM BSEMPMS
WHERE SAL<5000000 AND HIREDATE?
7、/*+ INDEX_JOIN(TABLE INDEX_NAME1 INDEX_NAME2) */
當(dāng)謂詞中引用的列都有索引的時(shí)候，可以通過(guò)指定采用索引關(guān)聯(lián)的方式，來(lái)訪(fǎng)問(wèn)數(shù)據(jù)
select /*+ index_join(t t_ind t_bm) */ id from t where id=100 and object_name='EMPLOYEES'?
8、/*+ INDEX_DESC(TABLE INDEX_NAME)*/
表明對(duì)表選擇索引降序的掃描方法.

SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ *?FROM BSEMPMS WHERE DPT_NO='SCOTT';?
9、/*+ INDEX_FFS(TABLE INDEX_NAME) */
對(duì)指定的表執(zhí)行快速全索引掃描,而不是全表掃描的辦法.
SELECT /* + INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';

10、/*+ INDEX_SS(T T_IND) */
從9i開(kāi)始，oracle引入了這種索引訪(fǎng)問(wèn)方式。當(dāng)在一個(gè)聯(lián)合索引中，某些謂詞條件并不在聯(lián)合索引的第一列時(shí)，可以通過(guò)Index Skip Scan來(lái)訪(fǎng)問(wèn)索引獲得數(shù)據(jù)。當(dāng)聯(lián)合索引第一列的唯一值個(gè)數(shù)很少時(shí)，使用這種方式比全表掃描效率高。

SQL> create table t as select 1 id,object_name from dba_objects;
Table created.
SQL> insert into t select 2,object_name from dba_objects;???????
50366 rows created.
SQL> insert into t select 3,object_name from dba_objects;???????
50366 rows created.?
SQL> insert into t select 4,object_name from dba_objects;???????
50366 rows created.?
SQL> commit;
Commit complete.
SQL> create index t_ind on t(id,object_name);
Index created.
SQL> exec dbms_stats.gather_table_stats('HR','T',cascade=>true);
PL/SQL procedure successfully completed.
執(zhí)行全表掃描?
SQL> select /*+ full(t) */ * from t where object_name='EMPLOYEES';
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? |???? 5 |?? 135 |?? 215?? (3)| 00:00:03 |
|*? 1 |? TABLE ACCESS FULL| T??? |???? 5 |?? 135 |?? 215?? (3)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - filter("OBJECT_NAME"='EMPLOYEES')
Statistics
----------------------------------------------------------
????????? 0? recursive calls
????????? 0? db block gets
??????? 942? consistent gets
????????? 0? physical reads
????????? 0? redo size
??????? 538? bytes sent via SQL*Net to client
??????? 385? bytes received via SQL*Net from client
????????? 2? SQL*Net roundtrips to/from client
????????? 0? sorts (memory)
????????? 0? sorts (disk)
????????? 6? rows processed?
不采用hint?
SQL>? select * from t where object_name='EMPLOYEES';
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2869677071
--------------------------------------------------------------------------
| Id? | Operation??????? | Name? | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT |?????? |???? 5 |?? 135 |???? 5?? (0)| 00:00:01 |
|*? 1 |? INDEX SKIP SCAN | T_IND |???? 5 |?? 135 |???? 5?? (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 1 - access("OBJECT_NAME"='EMPLOYEES')
?????? filter("OBJECT_NAME"='EMPLOYEES')
Statistics
----------------------------------------------------------
????????? 1? recursive calls
????????? 0? db block gets
???????? 17? consistent gets
????????? 1? physical reads
????????? 0? redo size
??????? 538? bytes sent via SQL*Net to client
??????? 385? bytes received via SQL*Net from client
????????? 2? SQL*Net roundtrips to/from client
????????? 0? sorts (memory)
????????? 0? sorts (disk)
????????? 6? rows processed
當(dāng)全表掃描掃描了942個(gè)塊，聯(lián)合索引只掃描了17個(gè)數(shù)據(jù)塊。可以看到聯(lián)合索引的第一個(gè)字段的值重復(fù)率很高時(shí)，即使謂詞中沒(méi)有聯(lián)合索引的第一個(gè)字段，依然會(huì)使用index_ss方式，效率遠(yuǎn)遠(yuǎn)高于全表掃描效率。但當(dāng)?第一個(gè)字段的值重復(fù)率很低時(shí)，使用?index_ss的效率要低于?全表掃描，讀者可以自行實(shí)驗(yàn)?
和表的關(guān)聯(lián)相關(guān)的hint

/*+ leading(table_1,table_2) */

在多表關(guān)聯(lián)查詢(xún)中，指定哪個(gè)表作為驅(qū)動(dòng)表，即告訴優(yōu)化器首先要訪(fǎng)問(wèn)哪個(gè)表上的數(shù)據(jù)。?
select /*+ leading(t,t1) */ t.* from t,t1 where t.id=t1.id;?
/*+ order */

讓Oracle根據(jù)from后面表的順序來(lái)選擇驅(qū)動(dòng)表，oracle建議使用leading，他更為靈活?
select /*+ order */ t.* from t,t1 where t.id=t1.id;
/*+ use_nl(table_1,table_2) */?
在多表關(guān)聯(lián)查詢(xún)中，指定使用nest loops方式進(jìn)行多表關(guān)聯(lián)。

select /*+ use_nl(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ use_hash(table_1,table_2) */?
在多表關(guān)聯(lián)查詢(xún)中，指定使用hash join方式進(jìn)行多表關(guān)聯(lián)。

select /*+ use_hash(t,t1) */ t.* from t,t1 where t.id=t1.id;

?

在多表關(guān)聯(lián)查詢(xún)中，指定使用hash join方式進(jìn)行多表關(guān)聯(lián)，并指定表t為驅(qū)動(dòng)表。

select /*+ use_hash(t,t1)?leading(t,t1)?*/ t.* from t,t1 where t.id=t1.id;

/*+ use_merge(table_1,table_2) */?
在多表關(guān)聯(lián)查詢(xún)中，指定使用merge join方式進(jìn)行多表關(guān)聯(lián)。

select /*+ use_merge(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ no_use_nl(table_1,table_2) */?
在多表關(guān)聯(lián)查詢(xún)中，指定不使用nest loops方式進(jìn)行多表關(guān)聯(lián)。

select /*+ no_use_nl(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ no_use_hash(table_1,table_2) */?
在多表關(guān)聯(lián)查詢(xún)中，指定不使用hash join方式進(jìn)行多表關(guān)聯(lián)。

select /*+ no_use_hash(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ no_use_merge(table_1,table_2) */?
在多表關(guān)聯(lián)查詢(xún)中，指定不使用merge join方式進(jìn)行多表關(guān)聯(lián)。

select /*+ no_use_merge(t,t1) */ t.* from t,t1 where t.id=t1.id;

其他常用的hint

/*+ parallel(table_name n) */?

在sql中指定執(zhí)行的并行度，這個(gè)值將會(huì)覆蓋自身的并行度

select /*+ parallel(t 4) */ count(*)? from t;

/*+ no_parallel(table_name) */?

在sql中指定執(zhí)行的不使用并行

select /*+ no_parallel(t) */ count(*)? from t;

/*+ append */以直接加載的方式將數(shù)據(jù)加載入庫(kù)

insert into t /*+ append */ select * from t;

/*+ dynamic_sampling(table_name n) */

設(shè)置sql執(zhí)行時(shí)動(dòng)態(tài)采用的級(jí)別，這個(gè)級(jí)別為0~10
select /*+ dynamic_sampling(t 4) */ * from t where id > 1234?

/*+ cache(table_name) */?
進(jìn)行全表掃描時(shí)將table置于LRU列表的最活躍端，類(lèi)似于table的cache屬性

select /*+ full(employees) cache(employees) */ last_name from employees?

轉(zhuǎn)載于:https://my.oschina.net/91jason/blog/834644

總結(jié)

以上是生活随笔為你收集整理的sql的Hint的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。