溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
Hint可以影響優(yōu)化器對(duì)于執(zhí)行計(jì)劃的選擇，但這種影響不是強(qiáng)制性的，優(yōu)化器在某些情況下可能會(huì)忽略目標(biāo)SQL中的Hint。由于各種原因?qū)е翲int被Oracle忽略后，Oracle并不會(huì)給出任何提示或者警告，更不會(huì)報(bào)錯(cuò)，目標(biāo)SQL依然可以正常運(yùn)行，這也符合Hint實(shí)際上是一種特殊注釋的身份。注釋本來就是可有可無的東西，不應(yīng)該因?yàn)樗拇嬖诙鴮?dǎo)致原先在沒有Hint時(shí)可以正常執(zhí)行的SQL因?yàn)榧恿薍int后而變得不能正常執(zhí)行。
下面來看幾種Hint被Oracle忽略的常見情形。
1 使用的Hint有語法或者拼寫錯(cuò)誤
一旦使用的Hint中有語法或者拼寫錯(cuò)誤，Oracle就會(huì)忽略該Hint，看幾個(gè)示例SQL：
select /*+ ind(emp pk_emp) */* from emp;
select /*+ index(emp pk_emp */* from emp;
select /* + index(emp pk_emp) */* from emp;
select */*+ index(emp pk_emp) */ from emp;
select /*+ index(scott.emp pk_emp) */* from emp;
select /*+ index(emp pk_emp) */* from emp e;
select /*+ index(emp emp_pk) */* from emp;
select /*+ full(t2) */ t1.ename,t1.deptno from emp t1 where t1.deptno in (select t2.deptno from detp t where t2.loc='CHICAGO');
實(shí)際上，上述8條SQL中的Hint都是無效的，它們都會(huì)被Oracle忽略。
1是因?yàn)殛P(guān)鍵字應(yīng)該是"index"而不是"ind"
2是因?yàn)槁┑袅艘粋€(gè)右括號(hào)
3是因?yàn)镠int中第一個(gè)*和+之間出現(xiàn)了空格
4是因?yàn)镠int出現(xiàn)的位置不對(duì)，它應(yīng)該出現(xiàn)在*前面
5是因?yàn)閑mp表前面帶上了SCHEME名稱
6是因?yàn)闆]有emp表的別名
7是因?yàn)樗饕Q寫錯(cuò)了
8是因?yàn)镠int跨了Query Block。Hint生效的范圍公限于它本身所在的Query Block，如果將某個(gè)Hint生將范圍擴(kuò)展到它所在的Query Block之外而又沒在該Hint中指定其生效的Query Block名稱的話，Oracle就會(huì)忽略該Hint。
2 使用的Hint無效
即使語法是正確的，但如果由于某種原因?qū)е翺racle認(rèn)為這個(gè)Hint無效，則Oracle還是會(huì)忽略該Hint。
看幾個(gè)實(shí)例
scott@TEST>set autotrace traceonly scott@TEST>select /*+ index(dept idx_dept_loc) */ deptno,dname from dept where loc='CHICAGO'; Execution Plan ---------------------------------------------------------- Plan hash value: 492093765 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 300 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 10 | 300 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_DEPT_LOC | 4 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- ......
從上面的輸出可以看出，上面的SQL的執(zhí)行計(jì)劃走的是對(duì)索引IDX_DEPT_LOC的索引范圍掃描，說明Hint生效了，但是如果把where條件替換為與索引IDX_DEPT_LOC毫不相關(guān)的deptno=30，再來看執(zhí)行情況
scott@TEST>select /*+ index(dept idx_dept_loc) */ deptno,dname from dept where deptno=30; Execution Plan ---------------------------------------------------------- Plan hash value: 2852011669 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 22 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 22 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- ......
從上面的輸出可以看出，執(zhí)行計(jì)劃走的是對(duì)主鍵PK_DEPT的INDEX UNIQUE SCAN，面不是Hint里的IDX_DEPT_LOC。這就說明Hint在這個(gè)SQL失效了。
即使不改where條件，如果把索引IDX_DEPT_LOC刪除，這個(gè)Hint也會(huì)失效：
scott@TEST>drop index idx_dept_loc; Index dropped. scott@TEST>select /*+ index(dept idx_dept_loc) */ deptno,dname from dept where loc='CHICAGO'; Execution Plan ---------------------------------------------------------- Plan hash value: 3383998547 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 300 | 29 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DEPT | 10 | 300 | 29 (0)| 00:00:01 | --------------------------------------------------------------------------
從上面的執(zhí)行計(jì)劃可以看出走的是對(duì)表DEPT的TABLE ACCESS FULL，Hint也是失效的。
再來看一個(gè)使用組合Hint的例子，先看如下SQL的執(zhí)行計(jì)劃
scott@TEST>select /*+ full(dept) parallel(dept 2) */ deptno from dept; Execution Plan ---------------------------------------------------------- Plan hash value: 587379989 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000 | 13000 | 16 (0)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM)| :TQ10000 | 1000 | 13000 | 16 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 3 | PX BLOCK ITERATOR | | 1000 | 13000 | 16 (0)| 00:00:01 | Q1,00 | PCWC | | | 4 | TABLE ACCESS FULL| DEPT | 1000 | 13000 | 16 (0)| 00:00:01 | Q1,00 | PCWP | | -------------------------------------------------------------------------------------------------------------- ......
從上面輸出內(nèi)容可以看出，現(xiàn)在是對(duì)表DEPT做的并行全表掃描，說明組合Hint中的兩個(gè)都生效了，這個(gè)Hint的含義是既要全表掃描又要并行訪問表DEPT，兩者不矛盾，因?yàn)槿頀呙杩梢圆⑿袌?zhí)行。再看如下的SQL：
scott@TEST>select /*+ index(dept pk_dept) parallel(dept 2) */ deptno from dept; 4 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2913917002 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000 | 13000 | 26 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | PK_DEPT | 1000 | 13000 | 26 (0)| 00:00:01 | ---------------------------------------------------------------------------- ......
現(xiàn)在SQL走的是對(duì)索引PK_DEPT的索引全掃描，但是串行的，說明Hint中的parallel(dept 2)失效了，因?yàn)楸鞤EPT上的主鍵索引PK_DEPT不是分區(qū)索引，而對(duì)于非分區(qū)索引而言，索引范圍掃描或索引全掃描并不能并行執(zhí)行，所以上述組合Hint中忽略了parallel(dept 2)。
再看一個(gè)HASH JOIN的例子：
下面的SQL中use_hash的Hint是生效的：
scott@TEST>select /*+ use_hash(t1) */ t1.empno,t1.empno,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno and t2.loc='CHICAGO'; 6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 615168685 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 185 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 5 | 185 | 7 (15)| 00:00:01 | |* 2 | TABLE ACCESS FULL| DEPT | 1 | 11 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 14 | 364 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- -
但是如果把SQL修改為如下則use_hash的Hint就會(huì)被忽略
scott@TEST>select /*+ use_hash(t1) */ t1.empno,t1.empno,t2.loc from emp t1,dept t2 where t1.deptno>t2.deptno and t2.loc='CHICAGO'; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 4192419542 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 37 | 6 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 37 | 6 (0)| 00:00:01 | |* 2 | TABLE ACCESS FULL| DEPT | 1 | 11 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 1 | 26 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------
從上面的執(zhí)行計(jì)劃中看出use_hash確實(shí)是被Oracle忽略了，這是因?yàn)楣＿B接只適用于等值連接條件，不等值的連接條件對(duì)哈希連接而言是沒有意義的，所以上述Hint就被Oracle忽略了。
3 使用的Hint自相矛盾
如果使用的組合Hint是自相矛盾的，則這些自相矛盾的Hint都會(huì)被Oracle忽略。但Oracle只會(huì)將自相矛盾的Hint全部忽略掉，但如果使用的組合Hint中還有其他有效的Hint，則這些有效Hint不受影響。
看一個(gè)使用自相矛盾Hint的實(shí)例，先執(zhí)行單個(gè)Hint的SQL
scott@TEST>select /*+ index_ffs(dept pk_dept)*/ deptno from dept; 4 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2578398298 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 12 | 2 (0)| 00:00:01 | | 1 | INDEX FAST FULL SCAN| PK_DEPT | 4 | 12 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------- ...... scott@TEST>select /*+ full(dept)*/ deptno from dept; 4 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3383998547 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 12 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| DEPT | 4 | 12 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------
從上面的輸出可以看出單獨(dú)使用上面的兩個(gè)Hint都能被Oracle生效，但如果這兩個(gè)Hint合并到一起使用就不是那么回事了：
scott@TEST>select /*+ index_ffs(dept pk_dept) full(dept)*/ deptno from dept; 4 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2913917002 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 12 | 1 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | PK_DEPT | 4 | 12 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------
從上面的輸出可以看出執(zhí)行計(jì)劃沒有走Hint中指定的執(zhí)行計(jì)劃，而是對(duì)主鍵索引PK_DEPT做的是INDEX FULL SCAN這說明Hint中的兩個(gè)都失效了。
再來看下面的例子：
scott@TEST>select /*+ index_ffs(dept pk_dept) full(dept) cardinality(dept 1000) */ deptno from dept; 4 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2913917002 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000 | 3000 | 1 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | PK_DEPT | 1000 | 3000 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------
從上面的輸出可以看出執(zhí)行計(jì)劃走的仍然是對(duì)主鍵索引PK_DEPT做的是INDEX FULL SCAN，但是做INDEX FULL SCAN反回結(jié)果集的cardinality從原來的4變?yōu)榱?000，說明cardinality(dept 1000)生效了，也驗(yàn)證了如果使用的組合Hint中還有其他有效的Hint，則這些有效Hint不受影響。
4 使用的Hint受到了查詢轉(zhuǎn)換的干擾
有時(shí)候，查詢轉(zhuǎn)換也會(huì)導(dǎo)致相關(guān)的Hint失效，即Hint被Oracle忽略還可能是因?yàn)槭艿搅瞬樵冝D(zhuǎn)換的干擾。
下面來看一個(gè)因?yàn)槭褂昧瞬樵冝D(zhuǎn)換而導(dǎo)致相關(guān)Hint被Oracle忽略掉的實(shí)例。
創(chuàng)建一個(gè)測(cè)試表jobs
scott@TEST>create table jobs as select empno,job from emp; Table created.
構(gòu)造一個(gè)SQL
select /*+ ordered cardinality(e 100) */ e.ename, j.job, e.sal, v.avg_sal from emp e, jobs j, (select /*+ merge */ e.deptno, avg(e.sal) avg_sal from emp e, dept d where d.loc = 'chicago' and d.deptno = e.deptno group by e.deptno) v where e.empno = j.empno and e.deptno = v.deptno and e.sal > v.avg_sal order by e.ename;
上面的SQL是兩個(gè)表(EMP和JOBS)和內(nèi)嵌視圖V關(guān)聯(lián)的SQL，其中內(nèi)嵌視圖V又是由表EMP和DEPT關(guān)聯(lián)后得到的。在此SQL中使用了三個(gè)Hint，其中merge用于讓內(nèi)嵌視圖V做視圖合并，ordered表示上述SQL在執(zhí)行時(shí)表EMP、JOBS和內(nèi)嵌視圖V的連接順序應(yīng)該和它們?cè)谠揝QL的SQL文本中出現(xiàn)的順序一致，即它們應(yīng)該是按照從左至右的順序依次做表連接。
如果上述三個(gè)Hint都生效的話，那目標(biāo)SQL的執(zhí)行計(jì)劃中應(yīng)該不會(huì)出現(xiàn)關(guān)鍵字“VIEW”(表示做了視圖合并，體現(xiàn)了Merge Hint的作用)，表EMP、JOBS和內(nèi)嵌視圖V的連接應(yīng)該會(huì)變成表EMP、JOBS和內(nèi)嵌視圖V所對(duì)應(yīng)的基表EMP和DEPT的連接，且連接的先后順序應(yīng)該是EMP->JOBS->內(nèi)嵌視圖V所對(duì)應(yīng)的基表EMP和DEPT(體現(xiàn)了Ordered Hint的作用)，外圍查詢中表EMP的掃描結(jié)果所對(duì)應(yīng)的Cardinality的值應(yīng)該是100(體現(xiàn)了Cardinality Hint的作用)。
現(xiàn)在看一下實(shí)際情況，執(zhí)行上面的SQL:
scott@TEST>select /*+ ordered cardinality(e 100) */ 2 e.ename, j.job, e.sal, v.avg_sal 3 from emp e, 4 jobs j, 5 (select /*+ merge */ 6 e.deptno, avg(e.sal) avg_sal 7 from emp e, dept d 8 where d.loc = 'chicago' 9 and d.deptno = e.deptno 10 group by e.deptno) v 11 where e.empno = j.empno 12 and e.deptno = v.deptno 13 and e.sal > v.avg_sal 14 order by e.ename; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 930847561 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 156 | 19656 | 15 (20)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | SORT GROUP BY | | 156 | 19656 | 15 (20)| 00:00:01 | |* 3 | HASH JOIN | | 156 | 19656 | 14 (15)| 00:00:01 | |* 4 | TABLE ACCESS FULL | DEPT | 1 | 11 | 3 (0)| 00:00:01 | |* 5 | HASH JOIN | | 467 | 53705 | 10 (10)| 00:00:01 | | 6 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 (0)| 00:00:01 | |* 7 | HASH JOIN | | 100 | 8900 | 7 (15)| 00:00:01 | | 8 | TABLE ACCESS FULL| EMP | 100 | 5800 | 3 (0)| 00:00:01 | | 9 | TABLE ACCESS FULL| JOBS | 14 | 434 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------
從上面的執(zhí)行計(jì)劃可以看出，確實(shí)沒有出現(xiàn)關(guān)鍵字“VIEW”，表EMP的掃描結(jié)果所對(duì)應(yīng)的Cardinality的值確實(shí)是100，但連接順序不是上面提到的順序，而是先選擇的表DEPT。這說明上述三個(gè)Hint中的Merge Hint和Cardinality Hint生效了，但Ordered Hint被Oracle忽略了。這是因?yàn)槭艿搅瞬樵冝D(zhuǎn)換的干擾(對(duì)內(nèi)嵌視圖V做視圖合并是一種查詢轉(zhuǎn)換)。
為了證明上述SQL的Ordered Hint被Oracle忽略是因?yàn)槭艿搅瞬樵冝D(zhuǎn)換的干擾，現(xiàn)在將內(nèi)嵌視圖V中的merge替換為no_merge(不讓內(nèi)嵌視圖做視圖合并)，再次執(zhí)行該SQL：
scott@TEST>select /*+ ordered cardinality(e 100) */ 2 e.ename, j.job, e.sal, v.avg_sal 3 from emp e, 4 jobs j, 5 (select /*+ no_merge */ 6 e.deptno, avg(e.sal) avg_sal 7 from emp e, dept d 8 where d.loc = 'chicago' 9 and d.deptno = e.deptno 10 group by e.deptno) v 11 where e.empno = j.empno 12 and e.deptno = v.deptno 13 and e.sal > v.avg_sal 14 order by e.ename; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 2898000699 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 728 | 14 (22)| 00:00:01 | | 1 | SORT ORDER BY | | 8 | 728 | 14 (22)| 00:00:01 | |* 2 | HASH JOIN | | 8 | 728 | 13 (16)| 00:00:01 | |* 3 | HASH JOIN | | 100 | 6500 | 7 (15)| 00:00:01 | | 4 | TABLE ACCESS FULL | EMP | 100 | 4600 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | JOBS | 14 | 266 | 3 (0)| 00:00:01 | | 6 | VIEW | | 5 | 130 | 6 (17)| 00:00:01 | | 7 | HASH GROUP BY | | 5 | 185 | 6 (17)| 00:00:01 | | 8 | MERGE JOIN | | 5 | 185 | 6 (17)| 00:00:01 | |* 9 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 2 (0)| 00:00:01 | | 10 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | |* 11 | SORT JOIN | | 14 | 364 | 4 (25)| 00:00:01 | | 12 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------------
從上面的執(zhí)行計(jì)劃中可以看出，出現(xiàn)了“VIEW”關(guān)鍵字，說明沒有做視圖合并，表EMP對(duì)就的Cardinality為100，連接順序與前面預(yù)想的一致，這說明在禁掉了查詢轉(zhuǎn)換后之前被忽略的Ordered Hint又生效了。
5 使用的Hint受到了保留關(guān)鍵字的干擾
Oracle在解析Hint時(shí)，是按照從左到右的順序進(jìn)行的，如果遇到的詞是Oracle的保留關(guān)鍵字，則Oracle將忽略這個(gè)詞以及之后的所有詞；如果遇到詞既不是關(guān)鍵字也不是Hint，就忽略該詞；如果遇到的詞是有效的Hint，那么Oracle就會(huì)保留該Hing。
正是由于上述Oracle解析Hint的原則，保留關(guān)鍵字也可能導(dǎo)致相關(guān)的Hint失效。
Oracle的保留關(guān)鍵字可以從視圖V$RESERVED_WORDS中查到，從下面的查詢結(jié)果可以看到','、'COMMENT'、'IS'都是保留關(guān)鍵字，但“THIS”不是
scott@TEST>select keyword,length from v$reserved_words where keyword in (',','THIS','IS','COMMENT');
KEYWORD LENGTH
---------- ----------
, 1
COMMENT 7
IS 2下面來看一個(gè)保留關(guān)鍵字導(dǎo)致Hint失效的實(shí)例，執(zhí)行下面的SQL
scott@TEST>select t1.empno,t1.empno,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 844388907 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 518 | 6 (17)| 00:00:01 | | 1 | MERGE JOIN | | 14 | 518 | 6 (17)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | |* 4 | SORT JOIN | | 14 | 364 | 4 (25)| 00:00:01 | | 5 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------
從執(zhí)行計(jì)劃上看走的是MERGE SORT JOIN，對(duì)SQL加入如下Hint并執(zhí)行：
scott@TEST>select /*+ use_hash(t1) index(t2 pk_dept) */ t1.empno,t1.empno,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2622742753 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 518 | 6 (17)| 00:00:01 | |* 1 | HASH JOIN | | 14 | 518 | 6 (17)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------
從上面的執(zhí)行計(jì)劃中可以看出Hint中的兩個(gè)都生效了，emp做HASH JOIN的被驅(qū)動(dòng)表，對(duì)DEPT表做使用索引PK_DEPT?，F(xiàn)在對(duì)Hint加入','，查看執(zhí)行情況:
scott@TEST>select /*+ use_hash(t1) , index(t2 pk_dept) */ t1.empno,t1.empno,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 615168685 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 518 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 14 | 518 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 44 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 14 | 364 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------
從執(zhí)行計(jì)劃中可以看出，仍然走的是HASH JOIN但是index(t2 pk_dept)失效了。因?yàn)?,'是Oracle的保留關(guān)鍵字，所以','后面的index(t2 pk_dept)失效了，再修改Hint如下并執(zhí)行SQL：
scott@TEST>select /*+ comment use_hash(t1) index(t2 pk_dept) */ t1.empno,t1.empno,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 844388907 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 518 | 6 (17)| 00:00:01 | | 1 | MERGE JOIN | | 14 | 518 | 6 (17)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | |* 4 | SORT JOIN | | 14 | 364 | 4 (25)| 00:00:01 | | 5 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------
從執(zhí)行計(jì)劃中看出，現(xiàn)在走的是跟一開始的執(zhí)行計(jì)劃一樣，說明Hint中的兩個(gè)都失效了，因?yàn)檫@兩個(gè)都在Oracle保留關(guān)鍵字comment后面。再修改Hint如下再次執(zhí)行SQL:
scott@TEST>select /*+ this use_hash(t1) index(t2 pk_dept) */ t1.empno,t1.empno,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2622742753 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 518 | 6 (17)| 00:00:01 | |* 1 | HASH JOIN | | 14 | 518 | 6 (17)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | EMP | 14 | 364 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------
現(xiàn)在執(zhí)行計(jì)劃又走出了Hint指定的樣子，說明兩個(gè)都生效了，這是因?yàn)閠his不是Oracle保留關(guān)鍵字。
以上介紹了5種Hint被Oracle忽略的情況，在實(shí)例使用過程中一定要注意使用方法，使用正確有效的Hint來提升SQL執(zhí)行效率，避免Hint被Oracle忽略。
參考《基于Oracle的SQL優(yōu)化》
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼