Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: April 26, 2017 - 11:41 am UTC

Answered by: Connor McDonald - Last updated: April 27, 2017 - 8:25 am UTC

Category: SQL Developer - Version: 11.2.0

Viewed 10K+ times! This question is

You Asked

Hi Team,

Can you please help in understanding hint while dealing with DB links?

Currently we are using below command to move data from source to destination, can this hint be used in below MERGE statement (table TB_STATEMENT_X holds billions of records).

MERGE INTO TB_STATEMENT_X@connect_tsys tgt
USING (
SELECT * FROM TB_STATEMENT_X WHERE ROWID IN (SELECT row_id FROM tb_archive_staging)
) src
ON (src.CARD_NO = tgt.CARD_NO AND src.STMT_BEGIN_DATE = tgt.STMT_BEGIN_DATE)
WHEN MATCHED THEN
UPDATE
SET tgt.ACC_NO = src.ACC_NO
, tgt.ACC_STATUS = src.ACC_STATUS
, ....
, ....
WHEN NOT MATCHED THEN
INSERT (tgt.CARD_NO, tgt.STMT_BEGIN_DATE, tgt.ACC_NO, tgt.PRIMARY_CARD_NO, .. , ..)
VALUES (src.CARD_NO, src.STMT_BEGIN_DATE, src.ACC_NO, src.PRIMARY_CARD_NO, .. , ..);


and we said...

driving_site isn't really appropriate for a MERGE (or something that changes a table) because the MERGE *must* be done on the site where table sits.

I did this on two db's

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.


and then we can see the impact of the driving_site hint in a *select*

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.




Is this answer out of date? If it is, please let us know via a Review