On Fri, 3 Aug 2001, gbarbour_at_csc.com wrote:
> 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.
--
Galen Boyer
It seems to me, I remember every single thing I know.
Received on Wed Aug 29 2001 - 21:29:19 CDT
