Database Reference
In-Depth Information
If the SQL statement is executed without hints and the degree of parallelism is set to 1, the query optimizer
chooses an index range scan:
SQL> SELECT * FROM t WHERE id > 93000;
---------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
---------------------------------------------------------
| 0 | SELECT STATEMENT | | 125 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 125 (0)|
|* 2 | INDEX RANGE SCAN | I | 17 (0)|
---------------------------------------------------------
2 - access("ID">93000)
Notice that the cost associated with the preceding execution plan (125) is lower than the cost of the full table scan
with a degree of parallelism up to 2. In contrast, with a degree of parallelism equal to or higher than 3, the full table
scan is cheaper.
Now, let's see what happens when only the
parallel
hint is added to the SQL statement—in other words, when
no access path hints are used. What happens is that the query optimizer picks out a serial index range scan when the
degree of parallelism is set to 2 but chooses a parallel full table scan when the degree of pallelism is set to 3:
SQL> SELECT /*+ parallel(t 2) */ * FROM t WHERE id > 93000;
---------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
---------------------------------------------------------
| 0 | SELECT STATEMENT | | 125 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 125 (0)|
|* 2 | INDEX RANGE SCAN | I | 17 (0)|
---------------------------------------------------------
2 - filter("ID">93000)
SQL> SELECT /*+ parallel(t 3) */ * FROM t WHERE id > 93000;
------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
------------------------------------------------------
| 0 | SELECT STATEMENT | | 110 (1)|
| 1 | PX COORDINATOR | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 110 (1)|
| 3 | PX BLOCK ITERATOR | | 110 (1)|
|* 4 | TABLE ACCESS FULL| T | 110 (1)|
------------------------------------------------------
4 - filter("ID">93000)
In summary, the
parallel
and
parallel_index
hints simply allow the query optimizer to consider parallel
processing; they don't force it.
