oracle hint_no_parallel_no_parallel_index

oracle hint
1,no_parallel
  1,适用于create 或alter开启表的并行,禁用其并行功能

SQL> create table t_parallel(a int) parallel 3;

表已创建。

SQL> explain plan for select a from t_parallel;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2003410281

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

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

| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time
  |    TQ  |IN-OUT| PQ Distrib |

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |            |     1 |    13 |     2   (0)| 00:00:0

1 |        |      |            |

|   1 |  PX COORDINATOR      |            |       |       |            |
  |        |      |            |

|   2 |   PX SEND QC (RANDOM)| :TQ10000   |     1 |    13 |     2   (0)| 00:00:0

1 |  Q1,00 | P->S | QC (RAND)  |

|   3 |    PX BLOCK ITERATOR |            |     1 |    13 |     2   (0)| 00:00:0

1 |  Q1,00 | PCWC |            |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   4 |     TABLE ACCESS FULL| T_PARALLEL |     1 |    13 |     2   (0)| 00:00:0

1 |  Q1,00 | PCWP |            |

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

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

Note
-----
   - dynamic sampling used for this statement (level=2)

已选择15行。

SQL> alter table t_parallel  parallel 2;

表已更改。

SQL> explain plan for select a from t_parallel;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2003410281

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

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

| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time
  |    TQ  |IN-OUT| PQ Distrib |

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |            |     1 |    13 |     2   (0)| 00:00:0

1 |        |      |            |

|   1 |  PX COORDINATOR      |            |       |       |            |
  |        |      |            |

|   2 |   PX SEND QC (RANDOM)| :TQ10000   |     1 |    13 |     2   (0)| 00:00:0

1 |  Q1,00 | P->S | QC (RAND)  |

|   3 |    PX BLOCK ITERATOR |            |     1 |    13 |     2   (0)| 00:00:0

1 |  Q1,00 | PCWC |            |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   4 |     TABLE ACCESS FULL| T_PARALLEL |     1 |    13 |     2   (0)| 00:00:0

1 |  Q1,00 | PCWP |            |

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

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

Note
-----
   - dynamic sampling used for this statement (level=2)

已选择15行。

SQL>

SQL> explain plan for select /*+ no_parallel(t_parallel) */ a from t_parallel;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 1830942209

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

| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |            |     1 |    13 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T_PARALLEL |     1 |    13 |     2   (0)| 00:00:01 |

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

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   - dynamic sampling used for this statement (level=2)

已选择12行。

hint
  no_parallel_index
    1,同no_parallel,禁用create or alter索引的并行功能

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

转载于:http://blog.itpub.net/9240380/viewspace-751472/