Oracle學(xué)習(xí)之性能優(yōu)化（九）使用hint
基于代價的優(yōu)化器是很聰明的，在絕大多數(shù)情況下它會選擇正確的優(yōu)化器，減輕了DBA的負(fù)擔(dān)。但有時它也聰明反被聰明誤，選擇了很差的執(zhí)行計劃，使某個語句的執(zhí)行變得奇慢無比。此時就需要DBA進(jìn)行人為的干預(yù)，告訴優(yōu)化器使用我們指定的存取路徑或連接類型生成執(zhí)行計劃，從 而使語句高效的運(yùn)行。例如，如果我們認(rèn)為對于一個特定的語句，執(zhí)行全表掃描要比執(zhí)行索引掃描更有效，則我們就可以指示優(yōu)化器使用全表掃描。在Oracle 中，是通過為語句添加 Hints(提示)來實(shí)現(xiàn)干預(yù)優(yōu)化器優(yōu)化的目的。
公司專注于為企業(yè)提供做網(wǎng)站、成都網(wǎng)站制作、微信公眾號開發(fā)、成都做商城網(wǎng)站，成都小程序開發(fā)，軟件按需求定制制作等一站式互聯(lián)網(wǎng)企業(yè)服務(wù)。憑借多年豐富的經(jīng)驗(yàn)，我們會仔細(xì)了解各客戶的需求而做出多方面的分析、設(shè)計、整合，為客戶設(shè)計出具風(fēng)格及創(chuàng)意性的商業(yè)解決方案，創(chuàng)新互聯(lián)建站更提供一系列網(wǎng)站制作和網(wǎng)站推廣的服務(wù)。
不建議在代碼中使用hint，在代碼使用hint使得CBO無法根據(jù)實(shí)際的數(shù)據(jù)狀態(tài)選擇正確的執(zhí)行計劃。畢竟 數(shù)據(jù)是不斷變化的， 10g以后的CBO也越來越完善，大多數(shù)情況下我們該讓Oracle自行決定采用什么執(zhí)行計劃。Oracle Hints是一種機(jī)制，用來告訴優(yōu)化器按照我們的告訴它的方式生成執(zhí)行計劃。我們可以用Oracle Hints來實(shí)現(xiàn)：
Hints for Optimization Approaches and Goals
Hints for Access Paths
Hints for Query Transformations
Hints for Join Orders
Hints for Join Operations
Hints for Parallel Execution
Additional Hints
實(shí)現(xiàn)提示的語法：
{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
or
{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...Hints for Optimization Approaches and Goals
/*+ ALL_ROWS*/ 語句塊選擇基于成本的優(yōu)化方法,并獲得最佳吞吐量,使資源消耗最小化.
/*+ FIRST_ROWS(n)*/ 語句塊選擇基于成本的優(yōu)化方法,并獲得最佳響應(yīng)時間,使資源消耗最小化.
/*+ CHOOSE*/ 語句塊依賴統(tǒng)計信息來決定選擇CBO還是RBO
/*+ RULE*/ 語句塊選擇基于規(guī)則的優(yōu)化方法.
實(shí)例：
SQL> select /*+ALL_ROWS*/ * from emp,dept where emp.deptno=dept.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6(17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 6(17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 4(25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")SQL> select /*+ FIRST_ROWS(1)*/ * from emp,dept where emp.deptno=dept.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3625962092
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 58 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")Hints for Access Paths
/*+ FULL(TABLE)*/ 全表掃描
SQL> select empno from emp; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 179099197 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | PK_EMP | 1 | 4 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------
加hint后
SQL> select /*+ FULL(emp)*/ ename from emp; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMP | 1 | 6 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------
/*+ROWID(TABLE)*/
SQL> SELECT ROWID,EMPNO FROM EMP; ROWID EMPNO ------------------ ---------- AAASZHAAEAAAACXAAA 7369 AAASZHAAEAAAACXAAB 7499 AAASZHAAEAAAACXAAC 7521 AAASZHAAEAAAACXAAD 7566 AAASZHAAEAAAACXAAE 7654 AAASZHAAEAAAACXAAF 7698 AAASZHAAEAAAACXAAG 7782 AAASZHAAEAAAACXAAH 7788 AAASZHAAEAAAACXAAI 7839 AAASZHAAEAAAACXAAJ 7844 AAASZHAAEAAAACXAAK 7876 ROWID EMPNO ------------------ ---------- AAASZHAAEAAAACXAAL 7900 AAASZHAAEAAAACXAAM 7902 AAASZHAAEAAAACXAAN 7934 14 rows selected.
從上面的結(jié)果集中選取一個rowid，不加hint
SQL> SELECT * FROM EMP WHERE ROWID>='AAASZHAAEAAAACXAAA' AND EMPNO IN(7521,7654); Execution Plan ---------------------------------------------------------- Plan hash value: 2355049923 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 | | 1 | INLIST ITERATOR | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------
使用hint
SQL> SELECT /*+rowid(EMP)*/ * FROM EMP WHERE ROWID>='AAASZHAAEAAAACXAAA' AND EMPNO IN(7521,7654); Execution Plan ---------------------------------------------------------- Plan hash value: 2267975152 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY ROWID RANGE| EMP | 1 | 38 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------
/*+ INDEX(TABLE INDEX_NAME) */ 對表選擇索引的掃描方法. INDEX_NAME一定要大寫
SQL> select /*+INDEX(emp PK_EMP)*/ * from emp; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4170700152 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 | | 2 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------
/*+ INDEX_ASC(TABLE INDEX_NAME)*/ 表明對表選擇索引升序的掃描方法. 建立索引時如果沒有指定desc，那么INDEX_ASC和INDEX 提示表示相同意義。
SQL> select /*+INDEX_ASC(emp PK_EMP)*/ * from emp; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4170700152 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 | | 2 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------
/*+ INDEX_DESC(TABLE INDEX_NAME)*/ 表明對表選擇索引降序的掃描方法.
SQL> select /*+INDEX_DESC(emp PK_EMP)*/ * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ---------- 7934 MILLER CLERK 7782 1982/01/23 00:00:00 1300 10 7902 FORD ANALYST 7566 1981/12/03 00:00:00 3000 20 7900 JAMES CLERK 7698 1981/12/03 00:00:00 950 30 7876 ADAMS CLERK 7788 1987/05/23 00:00:00 1100 20 7844 TURNER SALESMAN 7698 1981/09/08 00:00:00 1500 0 30 7839 KING PRESIDENT 1981/11/17 00:00:00 5000 10 7788 SCOTT ANALYST 7566 1987/04/19 00:00:00 3000 20 7782 CLARK MANAGER 7839 1981/06/09 00:00:00 2450 10 7698 BLAKE MANAGER 7839 1981/05/01 00:00:00 2850 30 7654 MARTIN SALESMAN 7698 1981/09/28 00:00:00 1250 1400 30 7566 JONES MANAGER 7839 1981/04/02 00:00:00 2975 20 7521 WARD SALESMAN 7698 1981/02/22 00:00:00 1250 500 30 7499 ALLEN SALESMAN 7698 1981/02/20 00:00:00 1600 300 30 7369 SMITH CLERK 7902 1980/12/17 00:00:00 800 20 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3088625055 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 | | 2 | INDEX FULL SCAN DESCENDING| PK_EMP | 14 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------
上面的查詢結(jié)果是按照empno降序排列的。
/*+INDEX_COMBINE(TABLE INDEX1 INDEX2 ...)*/
SQL> create bitmap index bidx_emp_sal on emp(sal); Index created. SQL> create bitmap index bidx_emp_hiredate on emp(hiredate); Index created.
SQL> SELECT * FROM EMP WHERE SAL<1500 AND HIREDATE<'1981/06/09 00:00:00';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980/12/17 00:00:00 800 20
7521 WARD SALESMAN 7698 1981/02/22 00:00:00 1250 500 30
Execution Plan
----------------------------------------------------------
Plan hash value: 1384570463
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 2 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX RANGE SCAN | BIDX_EMP_HIREDATE | | | | |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL"<1500)
3 - access("HIREDATE"<TO_DATE(' 1981-06-09 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))
filter("HIREDATE"<TO_DATE(' 1981-06-09 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))使用hint后
SQL> SELECT /*+INDEX_COMBINE( EMP BIDX_EMP_HIREDATE BIDX_EMP_SAL)*/ * FROM EMP WHERE SAL<1500 AND HIREDATE<'1981/06/09 00:00:00';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980/12/17 00:00:00 800 20
7521 WARD SALESMAN 7698 1981/02/22 00:00:00 1250 500 30
Execution Plan
----------------------------------------------------------
Plan hash value: 1332639593
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 2 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP MERGE | | | | | |
|* 5 | BITMAP INDEX RANGE SCAN | BIDX_EMP_HIREDATE | | | | |
| 6 | BITMAP MERGE | | | | | |
|* 7 | BITMAP INDEX RANGE SCAN | BIDX_EMP_SAL | | | | |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("HIREDATE"<TO_DATE(' 1981-06-09 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))
filter("HIREDATE"<TO_DATE(' 1981-06-09 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))
7 - access("SAL"<1500)
filter("SAL"<1500)/*+ INDEX_JOIN(TABLE INDEX_NAME1 INDEX_NAME2) */
當(dāng)謂詞中引用的列都有索引的時候，可以通過指定采用索引關(guān)聯(lián)的方式，來訪問數(shù)據(jù).選擇列只能是索引中的列。
SQL> create index idx_emp_ename on emp(ename); Index created. SQL> select /*+ INDEX_JOIN( emp PK_EMP IDX_EMP_ENAME)*/ empno,ename from emp where ename='KING' and empno=7839; EMPNO ENAME ---------- ------------------------------ 7839 KING Execution Plan ---------------------------------------------------------- Plan hash value: 70197466 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 10 | 2 (50)| 00:00:01 | |* 1 | VIEW | index$_join$_001 | 1 | 10 | 2 (50)| 00:00:01 | |* 2 | HASH JOIN | | | | | | |* 3 | INDEX RANGE SCAN| PK_EMP | 1 | 10 | 0 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN| IDX_EMP_ENAME | 1 | 10 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------
/*+ INDEX_FFS(TABLE INDEX_NAME) */對指定的表執(zhí)行快速全索引掃描,而不是全表掃描的辦法
SQL> select empno from emp; Execution Plan ---------------------------------------------------------- Plan hash value: 179099197 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | PK_EMP | 1 | 4 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------
我們加上hint后
SQL> select /*+INDEX_FFS(emp PK_EMP)*/ empno from emp order by empno; Execution Plan ---------------------------------------------------------- Plan hash value: 3618959410 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 56 | 3 (34)| 00:00:01 | | 1 | SORT ORDER BY | | 14 | 56 | 3 (34)| 00:00:01 | | 2 | INDEX FAST FULL SCAN| PK_EMP | 14 | 56 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------
/*+NO_INDEX(TABLE INDEX_NAME)*/ 不使用索引
SQL> select /*+NO_INDEX(emp PK_EMP)*/ empno from emp; Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMP | 1 | 4 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------
/*+AND_EQUAL(TABLE INDEX1 INDEX2 ...)*/ index最少兩個，最多不超過5個。
這個和INDEX_JOIN有點(diǎn)類似，但是INDEX_JOIN只能指定兩個索引
SQL> CREATE INDEX IDX_EMP_JOB ON EMP(JOB); Index created. SQL> select /*+ AND_EQUAL(emp IDX_EMP_JOB IDX_EMP_ENAME)*/ empno,ename from emp where ENAME='KING' and JOB='SALESMAN'; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 1954919191 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 18 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 2 (0)| 00:00:01 | | 2 | AND-EQUAL | | | | | | |* 3 | INDEX RANGE SCAN | IDX_EMP_ENAME | 1 | | 1 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | IDX_EMP_JOB | 3 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------
Hints for Query Transformations
/*+USE_CONCAT*/ 將WHERE 子句中的or或者in 查詢轉(zhuǎn)換成UNION ALL查詢
SQL> SELECT /*+USE_CONCAT*/ * from emp where ename='KING' OR SAL>5000; Execution Plan ---------------------------------------------------------- Plan hash value: 1292243969 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 4(0)| 00:00:01 | | 1 | CONCATENATION | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 2(0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX_EMP_ENAME | 1 | | 1(0)| 00:00:01 | |* 4 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 2(0)| 00:00:01 | | 5 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 6 | BITMAP INDEX RANGE SCAN | BIDX_EMP_SAL | | | | | -----------------------------------------------------------------------------------------------
/*+NO_EXPAND*/ 與USE_CONCAT正好相反，就是阻止優(yōu)化器將條件中帶or或者in查詢轉(zhuǎn)換成UNION ALL
SQL> select * from emp where empno=7840 or ename='SCOTT'; Execution Plan ---------------------------------------------------------- Plan hash value: 2037299637 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 | | 1 | CONCATENATION | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX_EMP_ENAME | 1 | | 1 (0)| 00:00:01 | |* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------
加hint后
SQL> select /*+NO_EXPAND*/ * from emp where empno=7840 or ename='SCOTT'; Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 76 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 2 | 76 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------
/*+REWRITE（mview）*/ 使用物化視圖重寫sql
/*+NO_REWRITE*/ 不使用物化視圖重寫sql
/*+MERGE*/ 對視圖查詢進(jìn)行合并。
看如下例子：
SQL> SELECT e1.ename, e1.sal, v.avg_sal FROM emp e1, (SELECT deptno, avg(sal) avg_sal FROM emp e2 GROUP BY deptno) v WHERE e1.deptno = v.deptno AND e1.sal > v.avg_sal; Execution Plan ---------------------------------------------------------- Plan hash value: 269884559 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 29 | 8 (25)| 00:00:01 | |* 1 | HASH JOIN | | 1 | 29 | 8 (25)| 00:00:01 | | 2 | VIEW | | 3 | 48 | 4 (25)| 00:00:01 | | 3 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 | | 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------
先把v的結(jié)果集算出來，再和e1進(jìn)行join運(yùn)算。
如果使用hint呢。
SQL> SELECT /*+merge(v)*/e1.ename, e1.sal, v.avg_sal FROM emp e1, (SELECT deptno, avg(sal) avg_sal FROM emp e2 GROUP BY deptno) v WHERE e1.deptno = v.deptno AND e1.sal > v.avg_sal; Execution Plan ---------------------------------------------------------- Plan hash value: 2435006919 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 128 | 8 (25)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | HASH GROUP BY | | 4 | 128 | 8 (25)| 00:00:01 | |* 3 | HASH JOIN | | 65 | 2080 | 7 (15)| 00:00:01 | | 4 | TABLE ACCESS FULL| EMP | 14 | 350 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------
先將兩表進(jìn)行關(guān)聯(lián)，再進(jìn)行g(shù)roup by
/*NO_MERGE(VIEW)*/ 與MERGE操作正好相反。
Hints for Join Orders
/*+ORDERED*/ 根據(jù)表在FROM子句中的順序,依次對其連接.
SQL> select * from emp e,dept d where e.deptno=d.deptno; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 844388907 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 | | 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | |* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 | | 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------
雖然emp表寫在前面，但是優(yōu)化器并沒有先處理emp表。
添加hint后
SQL> select /*+ORDERED*/ * from emp e,dept d where e.deptno=d.deptno; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1123238657 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 812 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 14 | 812 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------
Hints for Join Operations
/*+USE_NL(TABLE1 TABLE2)*/ 使用循環(huán)嵌套進(jìn)行連接,并把指定的第一個表作為驅(qū)動表.
SQL> select /*+USE_NL(d e)*/ * from emp e,dept d where e.deptno=d.deptno; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4192419542 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 812 | 10 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 14 | 812 | 10 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 4 | 152 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------
/*+USE_MERGE(table1 table2)*/
SQL> alter session set optimizer_mode=first_rows_1; Session altered. SQL> select a.ename,b.ename from emp a,emp b where a.mgr=b.empno; 13 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3355052392 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 1 | 20 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL | EMP | 7 | 70 | 2 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------
SQL> select /*+USE_MERGE(a b)*/ a.ename,b.ename from emp a,emp b where a.mgr=b.empno; 13 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 992080948 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 6 (17)| 00:00:01 | | 1 | MERGE JOIN | | 1 | 30 | 6 (17)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 140 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 | |* 4 | SORT JOIN | | 13 | 130 | 4 (25)| 00:00:01 | |* 5 | TABLE ACCESS FULL | EMP | 13 | 130 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------
/*+USE_HASH(table1 table2)*/ 將指定的表與其他表通過哈希連接方式連接起來.
SQL> select /*+USE_HASH(a b)*/ a.ename,b.ename from emp a,emp b where a.mgr=b.empno; 13 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3638257876 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 1 | 20 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 7 | 70 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------
/*+DRIVING_SITE(TABLE)*/ 此hint在使用dblink時有用。我們看如下例子
SQL> conn / as sysdba Connected. SQL> grant create database link to scott; Grant succeeded. SQL> conn scott/tiger Connected. SQL> create shared database link "db1" authenticated by SCOTT identified by "tiger" using '192.168.199.216:1521/11GDG1';
進(jìn)行如下查詢
SQL> select * from emp@db1 e,dept d where e.deptno=d.deptno; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2705760024 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT| -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 | | | | 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 | | | | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 | | | | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | | | |* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 | | | | 5 | REMOTE | EMP | 14 | 532 | 3 (0)| 00:00:01 | DB1 | R->S | --------------------------------------------------------------------------------------------------------
Oracle是將db1上的emp的數(shù)據(jù)傳到本地，然后排序合并。如果emp的數(shù)據(jù)量非常大時，這樣無疑是非常耗時的。如果我們可以將dept傳給遠(yuǎn)端，在遠(yuǎn)端執(zhí)行，結(jié)果返回到本地，那么執(zhí)行的速度會比較快。
SQL> select /*+DRIVING_SITE(e)*/* from emp@db1 e,dept d where e.deptno=d.deptno; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2412741621 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT| ----------------------------------------------------------------------------------------------- |文章名稱：Oracle學(xué)習(xí)之性能優(yōu)化（九）使用hint
標(biāo)題來源：http://chinadenli.net/article8/gshiop.html成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信小程序、靜態(tài)網(wǎng)站、面包屑導(dǎo)航、Google、網(wǎng)站導(dǎo)航、品牌網(wǎng)站制作
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)
- 自適應(yīng)網(wǎng)站開發(fā)都有哪些優(yōu)勢？ 2016-11-12
- 自適應(yīng)網(wǎng)站建設(shè)-自適應(yīng)的網(wǎng)站頁面設(shè)計 2016-11-11
- 自適應(yīng)網(wǎng)站建設(shè)有哪些優(yōu)勢 2021-01-01
- 自適應(yīng)網(wǎng)站應(yīng)當(dāng)怎樣開展設(shè)計 2016-11-15
- 【南充網(wǎng)站建設(shè)】什么是自適應(yīng)網(wǎng)站？自適應(yīng)網(wǎng)站價格高嗎 2014-11-28
- 什么是自適應(yīng)網(wǎng)站？移動網(wǎng)站設(shè)計怎么做？ 2021-09-02
- 解讀自適應(yīng)網(wǎng)站有哪些優(yōu)缺點(diǎn) 2021-12-09
- 如何開發(fā)一個真正的自適應(yīng)網(wǎng)站？ 2016-11-11
- 自適應(yīng)網(wǎng)站和響應(yīng)式網(wǎng)站之間的區(qū)別？ 2015-07-26
- 自適應(yīng)網(wǎng)站制作過程中要注意的細(xì)節(jié) 2021-06-23
- 在網(wǎng)站開發(fā)中傳統(tǒng)的網(wǎng)站相對于自適應(yīng)網(wǎng)站有著什么樣的劣勢呢？ 2016-10-27
- 自適應(yīng)網(wǎng)站的優(yōu)勢及缺點(diǎn)解析，建站失敗的原因解析 2022-10-24