Re: Remote query question.

Interesting,
 Thanks I will let you know what happens.

George Barbour.

"Mark D Powell" <mark.powell_at_eds.com> wrote in message news:178d2795.0108300540.200c0afe_at_posting.google.com... > "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 --