使用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)
如果满就说明是连接的问题。
请看一下执行计划:
SQL> select * FROM test.BOX_LEG2USER A WHERE EXISTS
2 (SELECT B.BL_USERNAME FROM [email protected] 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
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
如果你在query使用database link的表,同时CBO选择了一个本地表做驱动表(driving table),那么database link的表的索引将不可用。具体描述看 9i文档
server.920\a96533.pdf 的"How the CBO Evaluates Remote Operations"
(以下我没有测试)
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)
就是dblink的原因,
我了遇到过
可能是每有一条记录,Oracle都走dblink吧
你看看那个数据比较少一点,然后在别一个地方建同样的表,导数据,再select可能会快一点,如果有可能要条件导尽可能少的数据
当然如果数据量太大就不可行了