Oracle 通过数据库链接并行刷新物化视图

嗨，汤姆，

我的目标是使用并行提示使用来自远程数据库的数据填充本地表。
我的问题是，当查询是INSERT的一部分时，它不会并行执行...选择使用联接的块。

我的测试用例:

1) 连接到远程数据库并创建表:

CREATE TABLE REMOTE_TABLE AS SELECT OWNER,OBJECT_NAME FROM SYS.DBA_OBJECTS;

2) 连接到本地数据库，并创建到远程数据库的数据库链接:

CREATE DATABASE LINK TO_REMOTE CONNECT TO my_user IDENTIFIED BY my_pass USING 'remote_db';

3) 在本地数据库中，创建与远程数据库中的REMOTE_TABLE结构相同的空表:

CREATE TABLE LOCAL_TABLE AS SELECT * FROM REMOTE_TABLE@TO_REMOTE WHERE 1=2;

4) 简单查询并行运行:

SELECT /*+ PARALLEL(2) */ * FROM REMOTE_TABLE@TO_REMOTE;

这里是我看到正在远程数据库中执行的语句; 使用并行执行 (存在共享提示):

选择/* 共享 (2) */
"A1"。"所有者"，"A1"。"OBJECT_NAME"
从 “远程表” “A1”

5) INSERT inso using query不带联接也并行运行:

INSERT INTO LOCAL_TABLE SELECT /*+ PARALLEL(2) */ * FROM REMOTE_TABLE@TO_REMOTE;

这里是我看到正在远程数据库中执行的语句; 使用并行执行 (存在共享提示):

选择/* opque_transform共享 (2) */
“所有者” 、 “对象名称”
从 “远程表”

6) 使用join的查询也并行运行:

SELECT /*+ PARALLEL(2) */ A.* FROM REMOTE_TABLE@TO_REMOTE A, REMOTE_TABLE@TO_REMOTE B
 WHERE A.OBJECT_NAME = B.OBJECT_NAME
   AND A.OWNER       = B.OWNER;

这里是我看到正在远程数据库中执行的语句; 使用并行执行 (存在共享提示):

选择/* 共享 (2) */
"A2"。"所有者"，"A2"。"OBJECT_NAME"
从 “远程表” “A2”，“远程表” “A1”
其中 "A2"."OBJECT_NAME" = "A1"."OBJECT_NAME"
和 "A2"."OWNER" = "A1"."OWNER"

7) 但INSERT in使用query与join在单个线程中运行:

INSERT INTO LOCAL_TABLE
SELECT /*+ PARALLEL(2) */ A.* FROM REMOTE_TABLE@TO_REMOTE A, REMOTE_TABLE@TO_REMOTE B
 WHERE A.OBJECT_NAME = B.OBJECT_NAME
   AND A.OWNER       = B.OWNER

这是我看到的在远程数据库中执行的语句; 并行执行是NOT已使用 (无共享提示):

选择 “A2”。“所有者”，“A2”。“OBJECT_NAME”
从 “远程表” “A2”，“远程表” “A1”
其中 "A2"."OBJECT_NAME" = "A1"."OBJECT_NAME"
和 "A2"."OWNER" = "A1"."OWNER"

怎么了？如何强制查询在远程数据库中并行运行？
上面的问题是提供简单的测试用例。我的原始查询要复杂得多。

因为我们重新映射别名，所以我认为您需要明确指定表。当我这样做时，并行在所有情况下都按预期工作;

远程节点:

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table t2 as select * from dba_Objects;

Table created.

本地节点

select /*+ parallel */ t1.owner, count(*)
from t1@db19_pdb1
group by owner;

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE  |          |    32 |   192 |    40   (3)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |    32 |   192 |    40   (3)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY         |          |    32 |   192 |    40   (3)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          |    32 |   192 |    40   (3)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND HASH        | :TQ10000 |    32 |   192 |    40   (3)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   6 |       HASH GROUP BY      |          |    32 |   192 |    40   (3)| 00:00:01 |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          | 77667 |   455K|    39   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T1       | 77667 |   455K|    39   (0)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

Note
-----
   - fully remote statement
   

select /*+ parallel */ t1.owner, count(*)
from t1@db19_pdb1, t2@db19_pdb1
where t1.object_id = t2.object_id 
group by t1.owner;


------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE        |          |    32 |   512 |    79   (3)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)          | :TQ10003 |    32 |   512 |    79   (3)| 00:00:01 |  Q1,03 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY               |          |    32 |   512 |    79   (3)| 00:00:01 |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE                 |          |    32 |   512 |    79   (3)| 00:00:01 |  Q1,03 | PCWP |            |
|   5 |      PX SEND HASH              | :TQ10002 |    32 |   512 |    79   (3)| 00:00:01 |  Q1,02 | P->P | HASH       |
|   6 |       HASH GROUP BY            |          |    32 |   512 |    79   (3)| 00:00:01 |  Q1,02 | PCWP |            |
|*  7 |        HASH JOIN               |          | 77663 |  1213K|    78   (2)| 00:00:01 |  Q1,02 | PCWP |            |
|   8 |         PX RECEIVE             |          | 77668 |   379K|    39   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   9 |          PX SEND HYBRID HASH   | :TQ10000 | 77668 |   379K|    39   (0)| 00:00:01 |  Q1,00 | P->P | HYBRID HASH|
|  10 |           STATISTICS COLLECTOR |          |       |       |            |          |  Q1,00 | PCWC |            |
|  11 |            PX BLOCK ITERATOR   |          | 77668 |   379K|    39   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  12 |             TABLE ACCESS FULL  | T2       | 77668 |   379K|    39   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  13 |         PX RECEIVE             |          | 77667 |   834K|    39   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  14 |          PX SEND HYBRID HASH   | :TQ10001 | 77667 |   834K|    39   (0)| 00:00:01 |  Q1,01 | P->P | HYBRID HASH|
|  15 |           PX BLOCK ITERATOR    |          | 77667 |   834K|    39   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  16 |            TABLE ACCESS FULL   | T1       | 77667 |   834K|    39   (0)| 00:00:01 |  Q1,01 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------

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

   7 - access("A2"."OBJECT_ID"="A1"."OBJECT_ID")

Note
-----
   - fully remote statement
   
   
SQL> create table results ( o varchar2(200), c int );

Table created.

insert into results
select /*+ parallel(t1) */ t1.owner, count(*)
from t1@db19_pdb1
group by owner;

-------------------------------------------------------------------------
| Id  | Operation                | Name    | Cost (%CPU)| Inst   |IN-OUT|
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |         |     0   (0)|        |      |
|   1 |  LOAD TABLE CONVENTIONAL | RESULTS |            |        |      |
|   2 |   REMOTE                 |         |            | DB19_~ | R->S |
-------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   2 - EXPLAIN PLAN SET STATEMENT_ID='a1' INTO "PLAN_TABLE"@! FOR
       SELECT /*+ PARALLEL ("A1",DEFAULT) */ "A1"."OWNER",COUNT(*) FROM "T1"
       "A1" GROUP BY "A1"."OWNER" (accessing 'DB19_PDB1' )
       
       

insert into results
select /*+ parallel(t1) parallel(t2) */ t1.owner, count(*)
from t1@db19_pdb1, t2@db19_pdb1
where t1.object_id = t2.object_id 
group by t1.owner;


-------------------------------------------------------------------------
| Id  | Operation                | Name    | Cost (%CPU)| Inst   |IN-OUT|
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |         |     0   (0)|        |      |
|   1 |  LOAD TABLE CONVENTIONAL | RESULTS |            |        |      |
|   2 |   REMOTE                 |         |            | DB19_~ | R->S |
-------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   2 - EXPLAIN PLAN SET STATEMENT_ID='a1' INTO "PLAN_TABLE"@! FOR
       SELECT /*+ PARALLEL ("A1",DEFAULT) PARALLEL ("A2",DEFAULT) */
       "A2"."OWNER",COUNT(*) FROM "T1" "A2","T2" "A1" WHERE
       "A2"."OBJECT_ID"="A1"."OBJECT_ID" GROUP BY "A2"."OWNER" (accessing
       'DB19_PDB1' )

另请注意，您可以拥有一个或另一个，但不能同时拥有两个，即，跨数据库链接的传输将串行完成。

https://asktom.oracle.com/pls/asktom/asktom.search?tag=parallel-select-from-remote-site

https://asktom.oracle.com/pls/apex/asktom.search?tag=ctas-dblink-parallel-hints

https://asktom.oracle.com/pls/asktom/asktom.search?tag=parallel-insertdelete-and-database-links

根据上面的链接，如果您需要在本地和远程 (也就是操作中的多个数据库链接) 上进行并行会话，那么您将需要自己进行操作，例如dbms_parallel_execute。