oracle dblink

create public database link MSTR
connect to 用户名 identified by 密码

using '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.100)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (FAIL_OVER = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = linuxidc.com)
    )
)';

 如果报账号密码错误，先用plsql试试你自己的账号密码看看能否登录，如果可以，吧创建dblink脚本的的密码用双引号括起来

如(其实我也忘记了是否需要单引号了，太久了)

create public database link MSTR
connect to 用户名 identified by "密码"

using '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.100)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (FAIL_OVER = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = linuxidc.com)
    )
)';

 

 

 

1、所有的表都是DBLINK得到数据的远端表 优化语句（  /*+ driving_site(a) */ ）
2、既包含本地表，又包含远端表

在http://space6212.itpub.net/post/12157/304213中提到如果所有表都是远端表的话，那么该语句在远端执行，在得到结果后返回调用端。但是随便找了一个机器上做了几个测试就匆匆下结论了，今天遇到一个案例，推翻了这个结论。

SQL> SELECT A.*,B.PRODUCT_ID,B.SOURCE_ID FROM TEST.ORD_HIT_PRICE_HISTORY@TEST.US.ORACLE.COM A,TEST.ORD_HIT_COMM@TEST.US.ORACLE.COM B WHERE A.HIT_COMM_ID=B.RECORD_ID
UNION ALL
SELECT 
A.*,B.PRODUCT_ID,B.SOURCE_ID FROM TEST.ORD_HIT_PRICE_HISTORY@TEST.US.ORACLE.COM A,
(SELECT *
FROM (SELECT B.RECORD_ID,
B.PRODUCT_ID,
B.SOURCE_ID,
ROW_NUMBER() OVER(PARTITION BY RECORD_ID ORDER BY B.CREATE_DATE DESC) RN
FROM TEST.ORD_LOG_HIT_COMM@TEST.US.ORACLE.COM B) WHERE RN = 1) B 
2 3 4 5 6 7 8 9 10 11 WHERE A.HIT_COMM_ID = B.RECORD_ID;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34580 Card=13861 Byt
es=3589029)

1 0 UNION-ALL
2 1 NESTED LOOPS (Cost=19433 Card=6472 Bytes=1630944)
3 2 REMOTE* (Cost=17 Card=6472 Bytes=1126128) TEST.U
S.ORACLE
.COM

4 2 REMOTE* (Cost=3 Card=1 Bytes=78) TEST.U
S.ORACLE
.COM

5 1 MERGE JOIN (Cost=15147 Card=7389 Bytes=1958085)
6 5 VIEW (Cost=14963 Card=566129 Bytes=51517739)
7 6 WINDOW (SORT PUSHED RANK) (Cost=14963 Card=566129 By
tes=49253223)

8 7 REMOTE* (Cost=7366 Card=566129 Bytes=49253223) TEST.U
S.ORACLE
.COM

9 5 SORT (JOIN) (Cost=184 Card=6472 Bytes=1126128)
10 9 REMOTE* (Cost=17 Card=6472 Bytes=1126128) TEST.U
S.ORACLE
.COM

3 SERIAL_FROM_REMOTE SELECT "ID","HIT_COMM_ID","LAST_HIT_PRICE","
HIT_PRICE","LAST_RETAIL_PRICE","RETA

4 SERIAL_FROM_REMOTE SELECT "RECORD_ID","PRODUCT_ID","SOURCE_ID"
FROM "TEST"."ORD_HIT_COMM" "B" WHE

8 SERIAL_FROM_REMOTE SELECT "RECORD_ID","PRODUCT_ID","SOURCE_ID",
"CREATE_DATE" FROM "TEST"."ORD_LOG

10 SERIAL_FROM_REMOTE SELECT "ID","HIT_COMM_ID","LAST_HIT_PRICE","
HIT_PRICE","LAST_RETAIL_PRICE","RETA
这是一个真实的案例，只是把用户名替换称TEST。
从执行计划可以看出，这个语句是把数据从远端拉到本地再进行过滤的，由于源表比较大，使得这个查询执行起来非常缓慢。测试了一下，按照这种方式，需要50分钟才能出来结果。
通过trace看到，时间都消耗在网络传输上了：
WAIT #14: nam='SQL*Net more data from dblink' ela= 20 p1=675562835 p2=12 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 22 p1=675562835 p2=12 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 24 p1=675562835 p2=12 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 17 p1=675562835 p2=13 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 2077 p1=675562835 p2=13 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 18 p1=675562835 p2=13 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 23 p1=675562835 p2=14 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 21 p1=675562835 p2=14 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 17 p1=675562835 p2=14 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 4157 p1=675562835 p2=15 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 20 p1=675562835 p2=15 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 19 p1=675562835 p2=15 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 23 p1=675562835 p2=16 p3=0
WAIT #14: nam='SQL*Net more data from dblink' ela= 17 p1=675562835 p2=16 p3=0

幸运的是，这个语句返回结果很少，我们可以通过把驱动库设置为远端库的方式减少网络消耗，提高效率。

SQL> SELECT /*+ driving_site(a) */ A.*,B.PRODUCT_ID,B.SOURCE_ID FROM TEST.ORD_HIT_PRICE_HISTORY@TEST.US.ORACLE.COM A,TEST.ORD_HIT_COMM@TEST.US.ORACLE.COM B WHERE A.HIT_COMM_ID=B.RECORD_ID
UNION ALL
SELECT 
2 3 4 A.*,B.PRODUCT_ID,B.SOURCE_ID FROM TEST.ORD_HIT_PRICE_HISTORY@TEST.US.ORACLE.COM A,
(SELECT *
5 6 FROM (SELECT B.RECORD_ID,
B.PRODUCT_ID,
B.SOURCE_ID,
7 8 9 ROW_NUMBER() OVER(PARTITION BY RECORD_ID ORDER BY B.CREATE_DATE DESC) RN
FROM TEST.ORD_LOG_HIT_COMM@TEST.US.ORACLE.COM B) WHERE RN = 1) B 
WHERE A.HIT_COMM_ID = B.RECORD_ID 10 11 ;

5638 rows selected.

Elapsed: 00:01:08.38

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE (Cost=34344 Card=
13861 Bytes=3569613)

1 0 UNION-ALL
2 1 NESTED LOOPS (Cost=19433 Card=6472 Bytes=1611528)
3 2 TABLE ACCESS (FULL) OF 'ORD_HIT_PRICE_HISTORY' (Cost=1 TEST.U
7 Card=6472 Bytes=1126128) S.ORACLE
.COM

4 2 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'ORD_HIT_COMM' TEST.U
(Cost=3 Card=1 Bytes=75) S.ORACLE
.COM

5 4 INDEX (UNIQUE SCAN) OF 'PK_ORD_HIT_COMM' (UNIQUE) (C TEST.U
ost=2 Card=1) S.ORACLE
.COM

6 1 MERGE JOIN (Cost=14911 Card=7389 Bytes=1958085)
7 6 VIEW (Cost=14721 Card=566129 Bytes=51517739)
8 7 WINDOW (SORT PUSHED RANK) (Cost=14721 Card=566129 By
tes=46422578)

9 8 PARTITION RANGE (ALL)
10 9 TABLE ACCESS (FULL) OF 'ORD_LOG_HIT_COMM' (Cost= TEST.U
7366 Card=566129 Bytes=46422578) S.ORACLE
.COM

11 6 SORT (JOIN) (Cost=190 Card=6472 Bytes=1126128)
12 11 TABLE ACCESS (FULL) OF 'ORD_HIT_PRICE_HISTORY' (Cost TEST.U
=17 Card=6472 Bytes=1126128) S.ORACLE
.COM

修改后，只需要1分多钟SQL就执行完了。

从SELECT STATEMENT (REMOTE)也可以看出，修改后的SQL语句是先在远端库得到结果后再返回给发起端的。