OracleHint学习之二（忽略hint的情形）
在非分区索引上使用并行hint：
成都创新互联长期为上千客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为湖里企业提供专业的网站设计制作、成都网站制作，湖里网站改版等技术服务。拥有十多年丰富建站经验和众多成功案例,为您定制开发。
如下全表扫描并行hint可以生效：
SQL> 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 || 4 | 12 | 2 (0)| 00:00:01 | || | | 1 | PX COORDINATOR |||| || || | | 2 | PX SEND QC (RANDOM)| :TQ10000 | 4 | 12 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 3 | PX BLOCK ITERATOR || 4 | 12 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | | 4 | TABLE ACCESS FULL| DEPT| 4 | 12 | 2 (0)| 00:00:01 | Q1,00 | PCWP | | -------------------------------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 11 recursive calls 0 db block gets 5 consistent gets 6 physical reads 0 redo size 594 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 4 rows processed
非分区索引使用并行hint无效：
SQL> select /*+ index(dept pk_dept) parallel(dept 2) */deptno from dept; 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 | ---------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 62 recursive calls 0 db block gets 59 consistent gets 0 physical reads 0 redo size 594 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 4 rows processed
use_hash中指定被驱动表错误，hint无效：
SQL> select /*+ use_hash(t2) */ t1.empno,t1.ename,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno and t2.loc='CHICAGO'; 6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 844388907 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 120 | 6(17)| 00:00:01 | | 1 | MERGE JOIN | | 5 | 120 | 6(17)| 00:00:01 | |* 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | |* 4 | SORT JOIN | | 14 | 182 | 4(25)| 00:00:01 | | 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------
因hash连接只适用等值关联，所有hint无效：而走NL SQL> select /*+ use_hash(t1) */ t1.empno,t1.ename,t2.loc from emp t1,dept t2 where t1.deptno>=t2.deptno and t2.loc='CHICAGO'; 6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4192419542 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | |7 | 168 |6 (0)| 00:00:01 | | 1 | NESTED LOOPS | |7 | 168 |6 (0)| 00:00:01 | |* 2 | TABLE ACCESS FULL| DEPT |1 | 11 |3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| EMP |7 | 91 |3 (0)| 00:00:01 |
---------------------------------------------------------------------------
正确如下：
SQL> select /*+ use_hash(t1) */ t1.empno,t1.ename,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 | 120 |6 (0)| 00:00:01 | |* 1 | HASH JOIN | |5 | 120 |6 (0)| 00:00:01 | |* 2 | TABLE ACCESS FULL| DEPT |1 | 11 |3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 14 | 182 |3 (0)| 00:00:01 |
---------------------------------------------------------------------------
自相矛盾的hint会忽略掉：
SQL> select /*+ full(dept) index_ffs(dept pk_dept) */ deptno from dept;
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 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
594 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
文章名称：OracleHint学习之二（忽略hint的情形）
浏览地址：http://6mz.cn/article/ijhehi.html