與簡單的並行提示’/+parallel(4)/‘相比，’/+parallel(<Specific Table >,4)/‘對查詢性能有更好的影響嗎？

我有以下結構的三個表：

create table customer_info
(customer_num        number,
customer_firstName  varchar2(100),
customer_lastName   varchar2(100),
branch_code         varchar2(100));


create table branch_info
(branch_code   varchar2(100),
branch_name   varchar2(100));


create table customer_transaction
(transaction_date  date,
customer_num      number,
branch_code       varchar2(100),
transaction_count number
 )

在這些表中，數據量最大的一個是目前customer_transaction有近千萬條記錄。該表在列上有分區。transaction_date我想知道：

1）這兩個查詢在性能方面是否存在差異。

2）這兩個查詢以什麼方式彼此不同。

關鍵是在第一個查詢中我使用這個/*+parallel(4)*/，在第二個查詢中我引用了具有最大數據量的表/*+parallel(t2,4)*/。我必須說兩個查詢產生不同的執行計劃。

第一個查詢：

Select /*+parallel(4)*/
      t2.transaction_date,
      t1.customer_num,
      t1.customer_firstname,
      t1.customer_lastname,
      t3.branch_name,
      t2.transaction_count
 from customer_info t1
 left join customer_transaction t2
   on t1.customer_num = t2.customer_num
 left join branch_info t3
   on t2.branch_code = t3.branch_code;

第二個查詢：

Select /*+parallel(t2,4)*/ -----> t2 is "customer_transaction" 
      t2.transaction_date,
      t1.customer_num,
      t1.customer_firstname,
      t1.customer_lastname,
      t3.branch_name,
      t2.transaction_count
 from customer_info t1
 left join customer_transaction t2
   on t1.customer_num = t2.customer_num
 left join branch_info t3
   on t2.branch_code = t3.branch_code;

提前致謝。

1. 當然有，但這可能會或可能不會引起注意。我們不能僅通過查看您的表定義來知道這一點。
2. 見下文。

第一個查詢以 4 級並行執行所有操作，包括表訪問和連接：

Plan hash value: 2107928671

-----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                 |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                      |        |      |            |
|   1 |  PX COORDINATOR                   |                      |        |      |            |
|   2 |   PX SEND QC (RANDOM)             | :TQ10004             |  Q1,04 | P->S | QC (RAND)  |
|   3 |    HASH JOIN OUTER BUFFERED       |                      |  Q1,04 | PCWP |            |
|   4 |     PX RECEIVE                    |                      |  Q1,04 | PCWP |            |
|   5 |      PX SEND HASH (NULL RANDOM)   | :TQ10002             |  Q1,02 | P->P | HASH       |
|   6 |       HASH JOIN OUTER BUFFERED    |                      |  Q1,02 | PCWP |            |
|   7 |        PX RECEIVE                 |                      |  Q1,02 | PCWP |            |
|   8 |         PX SEND HASH (NULL RANDOM)| :TQ10000             |  Q1,00 | P->P | HASH       |
|   9 |          PX BLOCK ITERATOR        |                      |  Q1,00 | PCWC |            |
|  10 |           TABLE ACCESS FULL       | CUSTOMER_INFO        |  Q1,00 | PCWP |            |
|  11 |        PX RECEIVE                 |                      |  Q1,02 | PCWP |            |
|  12 |         PX SEND HASH              | :TQ10001             |  Q1,01 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR        |                      |  Q1,01 | PCWC |            |
|  14 |           TABLE ACCESS FULL       | CUSTOMER_TRANSACTION |  Q1,01 | PCWP |            |
|  15 |     PX RECEIVE                    |                      |  Q1,04 | PCWP |            |
|  16 |      PX SEND HASH                 | :TQ10003             |  Q1,03 | P->P | HASH       |
|  17 |       PX BLOCK ITERATOR           |                      |  Q1,03 | PCWC |            |
|  18 |        TABLE ACCESS FULL          | BRANCH_INFO          |  Q1,03 | PCWP |            |
-----------------------------------------------------------------------------------------------

Note
-----
  - dynamic statistics used: dynamic sampling (level=2)
  - Degree of Parallelism is 4 because of hint

第二個查詢僅CUSTOMER_TRANSACTION並行訪問。其他表不被並行訪問。然而，連接CUSTOMER_TRANSACTION是並行處理的。

Plan hash value: 1958681835

-----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                 |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                      |        |      |            |
|   1 |  PX COORDINATOR                   |                      |        |      |            |
|   2 |   PX SEND QC (RANDOM)             | :TQ10004             |  Q1,04 | P->S | QC (RAND)  |
|   3 |    HASH JOIN OUTER BUFFERED       |                      |  Q1,04 | PCWP |            |
|   4 |     PX RECEIVE                    |                      |  Q1,04 | PCWP |            |
|   5 |      PX SEND HASH (NULL RANDOM)   | :TQ10002             |  Q1,02 | P->P | HASH       |
|   6 |       HASH JOIN OUTER BUFFERED    |                      |  Q1,02 | PCWP |            |
|   7 |        PX RECEIVE                 |                      |  Q1,02 | PCWP |            |
|   8 |         PX SEND HASH (NULL RANDOM)| :TQ10000             |  Q1,00 | S->P | HASH       |
|   9 |          PX SELECTOR              |                      |  Q1,00 | SCWC |            |
|  10 |           TABLE ACCESS FULL       | CUSTOMER_INFO        |  Q1,00 | SCWP |            |
|  11 |        PX RECEIVE                 |                      |  Q1,02 | PCWP |            |
|  12 |         PX SEND HASH              | :TQ10001             |  Q1,01 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR        |                      |  Q1,01 | PCWC |            |
|  14 |           TABLE ACCESS FULL       | CUSTOMER_TRANSACTION |  Q1,01 | PCWP |            |
|  15 |     PX RECEIVE                    |                      |  Q1,04 | PCWP |            |
|  16 |      PX SEND HASH                 | :TQ10003             |  Q1,03 | S->P | HASH       |
|  17 |       PX SELECTOR                 |                      |  Q1,03 | SCWC |            |
|  18 |        TABLE ACCESS FULL          | BRANCH_INFO          |  Q1,03 | SCWP |            |
-----------------------------------------------------------------------------------------------

Note
-----
  - dynamic statistics used: dynamic sampling (level=2)
  - Degree of Parallelism is 4 because of table property

引用自：https://dba.stackexchange.com/questions/282935