Oracle 驾驶 _ 站点提示

嗨，团队，

在处理数据库链接时，您能帮助理解提示吗？

当前，我们正在使用下面的命令将数据从源移动到目标，是否可以在下面的MERGE语句中使用此提示 (表TB_STATEMENT_X保存数十亿条记录)。

合并到TB_STATEMENT_X @ connect_tsys tgt
使用 (
从TB_STATEMENT_X中选择 *，其中ROWID在 (从tb_archive_staging中选择row_id)
) src
ON (src.CARD_NO = tgt.CARD_NO和src.STMT_BEGIN_DATE = tgt.STMT_BEGIN_DATE)
当匹配时，然后
更新
设置tgt.ACC_NO = src.ACC_NO
,tgt.ACC_STATUS = src.ACC_STATUS
,....
,....
当不匹配时
插入 (tgt.CARD_NO，tgt.STMT_BEGIN_DATE，tgt.ACC_NO，tgt.PRIMARY_CARD_NO，..，..)
值 (src.CARD_NO，src.STMT_BEGIN_DATE，src.ACC_NO，src.PRIMARY_CARD_NO，..，..);

driving_site并不真正适合合并 (或更改表的内容)，因为合并 * 必须 * 在表所在的站点上完成。

我在两个数据库上做了这个

SQL> create table t as select OWNER
  2  ,OBJECT_NAME
  3  ,SUBOBJECT_NAME
  4  ,OBJECT_ID
  5  ,DATA_OBJECT_ID
  6  ,OBJECT_TYPE
  7  ,CREATED
  8  ,LAST_DDL_TIME
  9  ,TIMESTAMP
 10  ,STATUS
 11  ,TEMPORARY
 12  ,GENERATED
 13  ,SECONDARY
 14  ,NAMESPACE
 15  ,EDITION_NAME
 16  ,SHARING
 17  ,EDITIONABLE
 18  ,ORACLE_MAINTAINED from dba_objects;

Table created.
复制

然后我们可以在 * select * 中看到driving_site提示的影响

select /*+ driving_site(src) */ *
from t@np121 tgt,
      (SELECT * FROM t WHERE ROWID IN (SELECT rowid FROM t where rownum < 1000)
 ) src
 where src.object_id = tgt.object_id;

---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |   249 |   288   (2)| 00:00:01 |        |      |
|*  1 |  HASH JOIN                   |          |     1 |   249 |   288   (2)| 00:00:01 |        |      |
|   2 |   NESTED LOOPS               |          |     1 |   134 |     9  (12)| 00:00:01 |        |      |
|   3 |    VIEW                      | VW_NSO_1 |   999 | 11988 |     7   (0)| 00:00:01 |        |      |
|   4 |     HASH UNIQUE              |          |     1 | 11988 |            |          |        |      |
|*  5 |      COUNT STOPKEY           |          |       |       |            |          |        |      |
|   6 |       TABLE ACCESS FULL      | T        |   999 | 11988 |     7   (0)| 00:00:01 |        |      |
|   7 |    TABLE ACCESS BY USER ROWID| T        |     1 |   122 |     1   (0)| 00:00:01 |        |      |
|   8 |   REMOTE                     | T        | 92273 |    10M|   279   (2)| 00:00:01 |  NP121 | R->S |
---------------------------------------------------------------------------------------------------------

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

   1 - access("T"."OBJECT_ID"="TGT"."OBJECT_ID")
   5 - filter(ROWNUM<1000)

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

   8 - SELECT "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","
       CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPACE","ED
       ITION_NAME","SHARING","EDITIONABLE","ORACLE_MAINTAINED" FROM "T" "TGT" (accessing 'NP121' )


Note
-----
   - this is an adaptive plan

select /*+ driving_site(tgt) */ *
 from t@np121 tgt,
      (SELECT * FROM t WHERE ROWID IN (SELECT rowid FROM t where rownum < 1000)
 ) src
 where src.object_id = tgt.object_id;

---------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|          |   999 |   242K|   935   (1)| 00:00:01 |        |      |
|*  1 |  HASH JOIN             |          |   999 |   242K|   935   (1)| 00:00:01 |        |      |
|*  2 |   HASH JOIN            |          |   999 |   130K|   503   (1)| 00:00:01 |        |      |
|   3 |    VIEW                | VW_NSO_1 |   999 | 11988 |   252   (1)| 00:00:01 |  NP121 |      |
|*  4 |     COUNT STOPKEY      |          |       |       |            |          |        |      |
|   5 |      REMOTE            | T        |   999 | 11988 |   252   (1)| 00:00:01 |      ! | R->S |
|   6 |    REMOTE              | T        | 78131 |  9308K|   252   (1)| 00:00:01 |      ! | R->S |
|   7 |   TABLE ACCESS FULL    | T        | 92273 |    10M|   431   (1)| 00:00:01 |  NP121 |      |
---------------------------------------------------------------------------------------------------

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

   1 - access("A1"."OBJECT_ID"="A2"."OBJECT_ID")
   2 - access("A1".ROWID="$kkqu_col_1")
   4 - filter(ROWNUM<1000)

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

   5 - SELECT ROWID FROM "T" "A3" (accessing '!' )

   6 - SELECT ROWID,"OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OB
       JECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDAR
       Y","NAMESPACE","EDITION_NAME","SHARING","EDITIONABLE","ORACLE_MAINTAINED" FROM "T" "A1"
       (accessing '!' )


Note
-----
   - fully remote statement

36 rows selected.
复制