溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
基于代價的優化器是很聰明的，在絕大多數情況下它會選擇正確的優化器，減輕了DBA的負擔。但有時它也聰明反被聰明誤，選擇了很差的執行計劃，使某個語句的執行變得奇慢無比。此時就需要DBA進行人為的干預，告訴優化器使用我們指定的存取路徑或連接類型生成執行計劃，從 而使語句高效的運行。例如，如果我們認為對于一個特定的語句，執行全表掃描要比執行索引掃描更有效，則我們就可以指示優化器使用全表掃描。在Oracle 中，是通過為語句添加 Hints(提示)來實現干預優化器優化的目的。
不建議在代碼中使用hint，在代碼使用hint使得CBO無法根據實際的數據狀態選擇正確的執行計劃。畢竟 數據是不斷變化的， 10g以后的CBO也越來越完善，大多數情況下我們該讓Oracle自行決定采用什么執行計劃。Oracle Hints是一種機制，用來告訴優化器按照我們的告訴它的方式生成執行計劃。我們可以用Oracle Hints來實現：
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
實現提示的語法：
{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*/ 語句塊選擇基于成本的優化方法,并獲得最佳吞吐量,使資源消耗最小化.
/*+ FIRST_ROWS(n)*/ 語句塊選擇基于成本的優化方法,并獲得最佳響應時間,使資源消耗最小化.
/*+ CHOOSE*/ 語句塊依賴統計信息來決定選擇CBO還是RBO
/*+ RULE*/ 語句塊選擇基于規則的優化方法.
實例：
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.
從上面的結果集中選取一個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 | --------------------------------------------------------------------------------------
上面的查詢結果是按照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) */
當謂詞中引用的列都有索引的時候，可以通過指定采用索引關聯的方式，來訪問數據.選擇列只能是索引中的列。
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) */對指定的表執行快速全索引掃描,而不是全表掃描的辦法
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有點類似，但是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 查詢轉換成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正好相反，就是阻止優化器將條件中帶or或者in查詢轉換成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*/ 對視圖查詢進行合并。
看如下例子：
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的結果集算出來，再和e1進行join運算。
如果使用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 | -----------------------------------------------------------------------------
先將兩表進行關聯，再進行group by
/*NO_MERGE(VIEW)*/ 與MERGE操作正好相反。
Hints for Join Orders
/*+ORDERED*/ 根據表在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表寫在前面，但是優化器并沒有先處理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)*/ 使用循環嵌套進行連接,并把指定的第一個表作為驅動表.
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';
進行如下查詢
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的數據傳到本地，然后排序合并。如果emp的數據量非常大時，這樣無疑是非常耗時的。如果我們可以將dept傳給遠端，在遠端執行，結果返回到本地，那么執行的速度會比較快。
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| ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT REMOTE| | 14 | 812 | 7 (15)| 00:00:01 | | | |* 1 | HASH JOIN | | 14 | 812 | 7 (15)| 00:00:01 | | | | 2 | REMOTE | DEPT | 4 | 80 | 3 (0)| 00:00:01 | ! | R->S | | 3 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 | DGTST | | -----------------------------------------------------------------------------------------------
/*+LEADING(TABLE)*/ 將指定的表作為連接次序中的首表.
SQL> select /*+LEADING(e)*/* 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 | ---------------------------------------------------------------------------
/*+HASH_AJ*/ , /*+MERGE_AJ*/, and /*+NL_AJ*/ 將not in 改寫成反連接。 AJ = anti-join
SQL> select * from emp where empno not in (select /*+NL_AJ*/ mgr from emp where mgr is not null) ; 8 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3509159946 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 336 | 24 (0)| 00:00:01 | | 1 | NESTED LOOPS ANTI | | 8 | 336 | 24 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP | 6 | 24 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------
/*+HASH_SJ*/, /*+MERGE_SJ*/, and /*+NL_SJ*/ 將exists子句改寫成半連接 SJ = semi-join
(一對多，只要有一個record 就 join成功)
SQL> select * from dept where exists (select * from emp where deptno=dept.deptno and sal<1000); Execution Plan ---------------------------------------------------------- Plan hash value: 1946750470 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 27 | 4 (25)| 00:00:01 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 1 | 27 | 4 (25)| 00:00:01 | | 3 | SORT UNIQUE | | 1 | 7 | 2 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 7 | 2 (0)| 00:00:01 | | 5 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 6 | BITMAP INDEX RANGE SCAN | BIDX_EMP_SAL | | | | | |* 7 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | | 8 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------
添加hint
SQL> select * from dept where exists (select /*+HASH_SJ*/* from emp where deptno=dept.deptno and sal<1000); Execution Plan ---------------------------------------------------------- Plan hash value: 944460660 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 54 | 6 (17)| 00:00:01 | |* 1 | HASH JOIN SEMI | | 2 | 54 | 6 (17)| 00:00:01 | | 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 7 | 2 (0)| 00:00:01 | | 4 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 5 | BITMAP INDEX RANGE SCAN | BIDX_EMP_SAL | | | | | ----------------------------------------------------------------------------------------------
其他常用的hint
/*+ parallel(table_name n) */
在sql中指定執行的并行度，這個值將會覆蓋自身的并行度
select /*+ parallel(t 4) */ count(*) from emp t;
/*+ no_parallel(table_name) */
在sql中指定執行的不使用并行
select /*+ no_parallel(t) */ count(*) from emp t;
/*+ append */以直接加載的方式將數據加載入庫
insert into t /*+ append */ select * from t;
/*+ dynamic_sampling(table_name n) */
設置sql執行時動態采用的級別，這個級別為0~10
select /*+ dynamic_sampling(t 4) */ * from t where id > 1234
/*+ cache(table_name) */
進行全表掃描時將table置于LRU列表的最活躍端，類似于table的cache屬性
select /*+ full(employees) cache(employees) */ last_name from employees
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
