并行度对优化器的影响

当表的并行度被设置为大于1时，如果where条件为范围而不是=，优化器更倾向于使用全表扫描，而不是索引范围扫描。

SQL> alter table employees parallel(degree 5);
 
Table altered.
 
SQL> select * from employees where employee_id =100;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
 
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    68 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    68 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EMPLOYEE_ID"=100)

可见，如果where条件后面是=，优化器选择了索引唯一扫描，而没有使用全表扫描，而当where条件为一个范围时：
 
SQL> select * from employees where employee_id between 100 and 102;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 998304975
 
---------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |     3 |   204 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR      |           |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000  |     3 |   204 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |           |     3 |   204 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| EMPLOYEES |     3 |   204 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter("EMPLOYEE_ID"<=102 AND "EMPLOYEE_ID">=100)

此时，将并行度改为1，再次执行上述查询：
 
SQL> alter table employees parallel(degree 1);
 
Table altered.
 
SQL> select * from employees where employee_id between 100 and 102;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 603312277
 
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     3 |   204 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     3 |   204 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_EMP_ID_PK |     3 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EMPLOYEE_ID">=100 AND "EMPLOYEE_ID"<=102)

当并行度被设为1时，优化器选择了索引范围扫描。

通过此例可以看出并行度的设置对于优化器在选择执行计划时的影响。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-531815/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-531815/