Re: Remote query question.

"George Barbour" <gbarbour_at_csc.com> wrote in message news:<3b6a5678$1_at_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.

George, what happens if you move the view code directly into the SQL?

You might try adding the DRIVING_SITE( table ) hint to the select of the create table and see what the explain plan says.

If still no good you could try separating the create and the insert steps so that you first create the table then try an insert as select and see what the explain says.

If still no luck then add the hint into the insert as select.

Hints associated with views do not have to be imbedded in the view in order to work, but it helps insure that Oracle will use the hint if it is imbedded.

• Mark D Powell --