oracle hint_parallel_parallel_index

wisdomone1發表於2012-12-22
OracleParallelIndex
oracle hint
 parallel
   1,並行度如合理,啟用
   2,並行度如不合同不啟用
   3,default,啟用預設引數的配置值
SQL> create table t_parallel(a int) 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> explain plan for select /*+ parallel(t_parallel,default) */  a from t_paral
lel;
已解釋。
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>

hint
  parallel_index(table,index,parallel)
  1,啟用分割槽索引的並行模式
SQL> create table t_parallel(a int) parallel 3;
表已建立。
SQL> create index idx_t_parallel on t_parallel(a) parallel 2;
索引已建立。
SQL> insert into t_parallel values(1);
已建立 1 行。
SQL> insert into t_parallel values(12);
已建立 1 行。
SQL> insert into t_parallel values(123);
已建立 1 行。
SQL> commit;
提交完成。
SQL> explain plan for select a from t_parallel where a=1;
已解釋。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2359513259
--------------------------------------------------------------------------------
---
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time
  |
--------------------------------------------------------------------------------
---

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |     1 |    13 |     1   (0)| 00:00:0
1 |
|*  1 |  INDEX RANGE SCAN| IDX_T_PARALLEL |     1 |    13 |     1   (0)| 00:00:0
1 |
--------------------------------------------------------------------------------
---

Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
   1 - access("A"=1)
Note
-----
   - dynamic sampling used for this statement (level=2)
已選擇17行。
--說明即使對於啟用了並行模式的普通索引也不能啟用其索引的並行模式
SQL> explain plan for select /*+ parallel_index(t_parallel,idx_t_parallel,2) */
 a from t_parallel where a=1;
已解釋。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2359513259
--------------------------------------------------------------------------------
---
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time
  |
--------------------------------------------------------------------------------
---

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |     1 |    13 |     1   (0)| 00:00:0
1 |
|*  1 |  INDEX RANGE SCAN| IDX_T_PARALLEL |     1 |    13 |     1   (0)| 00:00:0
1 |
--------------------------------------------------------------------------------
---

Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
   1 - access("A"=1)
Note
-----
   - dynamic sampling used for this statement (level=2)
已選擇17行。
SQL>
 小結:看文件一定要仔細,往往一個小問題會浪費諸多時間
 
 請看下述的測試
 
--下述的唯一掃描不會啟用分割槽索引的並行功能
 SQL> explain plan for select /*+ parallel_index(t_parallel,idx_t_parallel,3) */
partition_id from t_parallel where partition_id=1;
已解釋。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3969493144
--------------------------------------------------------------------------------
---------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time
    | Pstart| Pstop |
--------------------------------------------------------------------------------
---------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |     1 |    13 |     3   (0)| 00:00
:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|            |     1 |    13 |     3   (0)| 00:00
:01 |     1 |     1 |
|*  2 |   TABLE ACCESS FULL    | T_PARALLEL |     1 |    13 |     3   (0)| 00:00
:01 |     1 |     1 |
--------------------------------------------------------------------------------
---------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("PARTITION_ID"=1)
Note
-----
   - dynamic sampling used for this statement (level=2)
已選擇18行。
---如果是範圍查詢,啟用分割槽索引的並行功能
SQL> explain plan for select /*+ parallel_index(t_parallel,idx_t_parallel,3) */
partition_id from t_parallel where partition_id between 1 and 2000;
已解釋。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1860071282
--------------------------------------------------------------------------------
--------------------------------------------------------
| Id  | Operation                | Name           | Rows  | Bytes | Cost (%CPU)|
 Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
--------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                |  2000 | 26000 |     4   (0)|
 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR          |                |       |       |            |
          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10000       |  2000 | 26000 |     4   (0)|
 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION RANGE ALL|                |  2000 | 26000 |     4   (0)|
 00:00:01 |     1 |     3 |  Q1,00 | PCWC |            |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|*  4 |     INDEX RANGE SCAN     | IDX_T_PARALLEL |  2000 | 26000 |     4   (0)|
 00:00:01 |     1 |     3 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------
--------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   4 - access("PARTITION_ID">=1 AND "PARTITION_ID"<=2000)
Note
-----
   - dynamic sampling used for this statement (level=2)
已選擇20行。

--不加hint oracle cbo也會啟用分割槽索引的並行功能
SQL> explain plan for select  partition_id from t_parallel where partition_id be
tween 1 and 2000;
已解釋。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1860071282
--------------------------------------------------------------------------------
--------------------------------------------------------
| Id  | Operation                | Name           | Rows  | Bytes | Cost (%CPU)|
 Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
--------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                |  2000 | 26000 |     4   (0)|
 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR          |                |       |       |            |
          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10000       |  2000 | 26000 |     4   (0)|
 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION RANGE ALL|                |  2000 | 26000 |     4   (0)|
 00:00:01 |     1 |     3 |  Q1,00 | PCWC |            |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|*  4 |     INDEX RANGE SCAN     | IDX_T_PARALLEL |  2000 | 26000 |     4   (0)|
 00:00:01 |     1 |     3 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------
--------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   4 - access("PARTITION_ID">=1 AND "PARTITION_ID"<=2000)
Note
-----
   - dynamic sampling used for this statement (level=2)
已選擇20行。
SQL>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-751532/，如需轉載，請註明出處，否則將追究法律責任。

相關文章