Oracle Hint Learning II (ignoring the hint scenario)

1. To use parallel hint on a non-partitioned index:

   The following full table scan parallel hint can take effect:

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  Size594  bytes sent via sql*net to client523  bytes received  via sql*net from client  2  sql*net roundtrips to/from  client  0  sorts  (memory)   0  sorts  (disk)    4  rows processed

Non-partitioned indexes are not valid using parallel 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 size594  bytes sent via SQL*Net to client523   bytes received via sql*net from client  2  sql*net  roundtrips to/from client  7  sorts  (memory)   0  sorts   (disk)   4  rows processed

The driver table error is specified in the

Use_hash, hint is invalid:

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 (| 00:00:01 |) |    1 |  merge join     |       |      5 |   120 |     6 (| 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 (| 00:00:01 |) |    5 |    table access full     |  emp     |    14 |   182 |      3  (0) | 00:00:01 |------------------------------------------------------------ ----------------------------

Because the hash connection only applies the equivalence association, all hint are invalid: while walking Nlsql> 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 |

---------------------------------------------------------------------------

Correct as follows:

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 |

---------------------------------------------------------------------------

The paradox of hint will be ignored:

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

Oracle Hint Learning II (ignoring the hint scenario)