oracle hint_no_parallel_no_parallel_index

wisdomone1發表於2012-12-21
OracleParallelIndex
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章