分布式查询优化一例

在分布式查询中，对于分布在两个节点上的两张表进行嵌套循环连接将会非常慢，这是由于Oracle将
所有的远程数据传到本地来处理。所以对于这类查询最好换成排序合并连接或者hash连接。[@more@]

下面例子中的表aaa、bbb分别位于两个库,aaa所在库通过数据库链db244访问bbb表：

create table aaa as select * from dba_objects;
create table bbb as select * from dba_objects;

1、嵌套循环连接

SQL> select count(*) from aaa a
      where exists
           (select * from bbb@db244 b where a.object_id=b.object_id); 

  COUNT(*)
----------
     27165

Elapsed: 00:04:04.74

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'AAA'
   4    2       REMOTE* DB244.US.ORACLE.COM
   4 SERIAL_FROM_REMOTE   SELECT "OBJECT_ID" FROM "BBB" "B" WHERE "OBJECT_ID"=:1

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

2、排序合并连接

SQL> select count(*) from aaa a
      where a.object_id in
            (select object_id from bbb@db244 b); 

  COUNT(*)
----------
     27165

Elapsed: 00:00:00.59

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     MERGE JOIN
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'AAA'
   5    2       SORT (JOIN)
   6    5         VIEW OF 'VW_NSO_1'
   7    6           SORT (UNIQUE)
   8    7             REMOTE* DB244.US.ORACLE.COM
   8 SERIAL_FROM_REMOTE    SELECT "OBJECT_ID" FROM "BBB" "B"

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        437  consistent gets
          0  physical reads
          0  redo size
        381  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

3、通过提示改用hash连接

SQL> select /*+ use_hash(a,b) */count(*) from aaa a
 where exists
       (select * from bbb@db244 b
         where a.object_id=b.object_id);

  COUNT(*)
----------
     27165

Elapsed: 00:00:00.51

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=91 Card=1 Bytes=26)
   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (SEMI) (Cost=91 Card=27172 Bytes=706472)
   3    2       TABLE ACCESS (FULL) OF 'AAA' (Cost=47 Card=27172 Bytes=353236)

   4    2       VIEW OF 'VW_SQ_1' (Cost=42 Card=32509 Bytes=422617)
   5    4         REMOTE* (Cost=42 Card=32509 Bytes=422617)  
                          DB244.US.ORACLE.COM
   5 SERIAL_FROM_REMOTE       SELECT "OBJECT_ID" FROM "BBB" "B"

Statistics
----------------------------------------------------------
          3  recursive calls
          0  db block gets
        468  consistent gets
          0  physical reads
          0  redo size
        381  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/3898/viewspace-799313/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/3898/viewspace-799313/