REMOTE DBLINK Scan

名称

远程连接扫描算子

介绍

当使用dblink进行跨库查询时，需要使用到REMOTE DBLINK Scan算子实现对远程表的扫描操作。

举例

--在第一台机器上建立一个数据库TEST，登录用户sysdba；
CONNECT sysdba/szoscar55

DROP TABLE t1;
CREATE TABLE t1(a int,b int);
INSERT INTO t1 VALUES(1,2);
INSERT INTO t1 VALUES(1,3);
INSERT INTO t1 VALUES(1,5);
INSERT INTO t1 VALUES(2,1);
ANALYZE t1;

--在第二台机器上创建一个dblink连接用户sysdba，并进行查询
DROP DATABASELINK db_oscar;
CREATE DATABASELINK db_oscar CONNECT TO sysdba IDENTIFIED BY 'szoscar55' USING '127.0.0.1' PORT 2022 DATABASE 'test';

DROP TABLE t2;
CREATE TABLE t2(a int);
INSERT INTO t2 VALUES(2);
EXPLAIN ANALYZE SELECT a FROM t1@db_oscar WHERE a IN (SELECT * FROM t2);
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 REMOTE DBLINK Scan   (cost=0.00..1.00 rows=100 width=31) (actual time=0.04..0.04 rows=1 loops=1)
   Remote SQL Information: SELECT  A."A", A."B" FROM "T1" A  (accessing 'DB_OSCAR')
   Scan Filter: (subplan)
   SubPlan
     ->  Materialize  (cost=89.92..89.92 rows=8192 width=4) (actual time=0.01..0.01 rows=1 loops=4)
           ->  Seq Scan on T2  (cost=0.00..89.92 rows=8192 width=4) (actual time=0.01..0.02 rows=1 loops=1)
 Planning Time: 0.15 msec
 Execution Time: 8.72 msec
(8 rows)

• Remote SQL Information 算子属性，表示远端要执行的查询语句
• Scan Filter 限定条件