Remote query question.

From: George Barbour <gbarbour_at_csc.com>
Date: Fri, 3 Aug 2001 08:56:21 +0100
Message-ID: <3b6a5678$1@pull.gecm.com>

Hi,
I have created a view which accesses a remote instance over a database link. (lots of views actually).

If I execute the simple statement - 'select * from myview;' - it produces the following plan :-

select * from myview;
SELECT STATEMENT (REMOTE) Optimizer=CHOOSE (Cost=1645 Card=41 Bytes=46617)   NESTED LOOPS (OUTER) (Cost=1645 Card=41 Bytes=46617)     NESTED LOOPS (OUTER) (Cost=1642 Card=3 Bytes=3345)

      NESTED LOOPS (OUTER) (Cost=1636 Card=3 Bytes=3252)
        "

From the plan I assume that, as the first statement specifies 'REMOTE' the complete query will run, and be resolved, on the remote server, before the result
is returned to me on my server.
.
However if I add another statement to the script such as - 'create table as' - the plan will then display :-

create table mytable as select * from my view; CREATE TABLE STATEMENT Optimizer=CHOOSE (Cost=735 Card=2 Bytes=1642)   LOAD AS SELECT
    NESTED LOOPS (OUTER) (Cost=735 Card=2 Bytes=1642)

      NESTED LOOPS (OUTER) (Cost=733 Card=2 Bytes=1598)
        NESTED LOOPS (OUTER) (Cost=729 Card=2 Bytes=1536)
          NESTED LOOPS (OUTER) (Cost=725 Card=2 Bytes=1496)
            NESTED LOOPS (OUTER) (Cost=721 Card=2 Bytes=1458)
              NESTED LOOPS (OUTER) (Cost=717 Card=2 Bytes=1322)
                NESTED LOOPS (Cost=715 Card=2 Bytes=1218)
                  NESTED LOOPS (OUTER) (Cost=713 Card=2 Bytes=1126)
                    NESTED LOOPS (OUTER) (Cost=711 Card=2 Bytes=1010)
                      HASH JOIN (Cost=404 Card=4050 Bytes=1146150)
                        REMOTE* (Cost=23 Card=69 Bytes=3864)
                        REMOTE* (Cost=380 Card=21133 Bytes=4797191)
                      REMOTE* (Cost=17 Card=882 Bytes=45864)
                        "

The query has been 'chopped up' and now splits it time between the remote and local servers.
It takes a much longer time to run.
 I would like it to complete the query on the remote server, then return the result set, and only then populate my local table.

Is this behaviour normal? .. How do clever people handle these queries? Thanks in advance :-)
George Barbour. Received on Fri Aug 03 2001 - 02:56:21 CDT