查远程数据库，建了同义词，却发现奇慢呀，救命呀

[shuangyu]

我现在要在二台服务器上同步数据，如果A服务器上的数据在B服务器存在就查出来，结果通过dblink好慢，但把B上的数据一样的换到A本地来执行却好快，难道DBLINK的效果这么差吗！！！！！
select * FROM BOX_LEG2USER A WHERE EXISTS
(SELECT B.BL_USERNAME FROM BOX_LEG2USER_SYN B
WHERE A.BL_USERNAME = B.BL_USERNAME AND A.BL_ADDRCODE = B.BL_ADDRCODE AND
A.BL_CALLED = B.BL_CALLED AND A.BL_CALLING = B.BL_CALLING AND
A.BL_SESSIONID = B.BL_SESSIONID AND A.BL_ACCTBEGIN = B.BL_ACCTBEGIN)
上面是用了同义词BOX_LEG2USER_SYN （用了dblink）来做为B服务器中的表，执行了35秒
但当我把B上的数据导出来放在A本地上执行只用了0.5秒，这是为什么
是网络有问题吗，但是内网的二台机呀，有好的解决办法吗

做过这方面的朋友提供好方案呀

[zp21th]

使用materialized view

[drabit]

使用hint
(以下我没有测试)
select /*+ use_nl(A B) */ * FROM BOX_LEG2USER A WHERE EXISTS
(SELECT B.BL_USERNAME FROM BOX_LEG2USER_SYN B
WHERE A.BL_USERNAME = B.BL_USERNAME AND A.BL_ADDRCODE = B.BL_ADDRCODE AND
A.BL_CALLED = B.BL_CALLED AND A.BL_CALLING = B.BL_CALLING AND
A.BL_SESSIONID = B.BL_SESSIONID AND A.BL_ACCTBEGIN = B.BL_ACCTBEGIN)

[shuangyu]

drabit(square) 我看过了，是要在后面加一个远程的不影响查询的条件，让他使用远程索引，用hash join

[guangli_zhang]

不是网络的问题，
就是dblink的原因，
我了遇到过
可能是每有一条记录，Oracle都走dblink吧
你看看那个数据比较少一点，然后在别一个地方建同样的表，导数据，再select可能会快一点，如果有可能要条件导尽可能少的数据
当然如果数据量太大就不可行了

[dlmaomao]

关注

[LGQDUCKY]

首先你在SQLPLUS下连接到第二个数据库的处理的速度如何？
如果满就说明是连接的问题。

[shuangyu]

如果用table@dblink联一个表的直接查询还是很快的，如table1@dblinkname

[shuangyu]

后面的条件字段在二边的表都是合起来做一个主键的
请看一下执行计划：
SQL> select * FROM test.BOX_LEG2USER A WHERE EXISTS
2 (SELECT B.BL_USERNAME FROM [e-mail] B
3 WHERE A.BL_USERNAME = B.BL_USERNAME AND A.BL_ADDRCODE = B.BL_ADDRCODE AND
4 A.BL_CALLED = B.BL_CALLED AND A.BL_CALLING = B.BL_CALLING AND
5 A.BL_SESSIONID = B.BL_SESSIONID AND A.BL_ACCTBEGIN = B.BL_ACCTBEGIN)
6 ;

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'BOX_LEG2USER'
3 1 REMOTE* ORA11.DO
NG.COM



3 SERIAL_FROM_REMOTE SELECT "BL_USERNAME","BL_ADDRCODE","BL_CALLE
D","BL_CALLING","BL_SESSIONID","BL_A



Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1697 consistent gets
1619 physical reads
0 redo size
2190 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

[shuangyu]

本地计划：
box_leg2user1 这个表的数据和远程的一样
二边的索引，结构是一模一样的
select * FROM test.BOX_LEG2USER A WHERE EXISTS
2 (SELECT B.BL_USERNAME FROM box_leg2user1 B
3 WHERE A.BL_USERNAME = B.BL_USERNAME AND A.BL_ADDRCODE = B.BL_ADDRCODE AND
4 A.BL_CALLED = B.BL_CALLED AND A.BL_CALLING = B.BL_CALLING AND
A.BL_SESSIONID = B.BL_SESSIONID AND A.BL_ACCTBEGIN = B.BL_ACCTBEGIN) 5 ;

no rows selected

Elapsed: 00:00:00.30

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=164 Card=1 Bytes=310
)

1 0 NESTED LOOPS (SEMI) (Cost=164 Card=1 Bytes=310)
2 1 TABLE ACCESS (FULL) OF 'BOX_LEG2USER' (Cost=164 Card=477
92 Bytes=10848784)

3 1 INDEX (UNIQUE SCAN) OF 'PK_LEG2USER1' (UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1699 consistent gets
1621 physical reads
0 redo size
2190 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

[drabit]

CBO的问题
如果你在query使用database link的表，同时CBO选择了一个本地表做驱动表(driving table）,那么database link的表的索引将不可用。

具体描述看 9i文档
server.920\a96533.pdf 的"How the CBO Evaluates Remote Operations"

[beckhambobo]

数据量不大物化视图意义不大，既然如此慢
可否先create table table_name as select ...;
再select ....from table_name
效果明显

[shuangyu]

drabit(square）要如何解决这个问题呢，现在看文档没时间了，要事后看 了