Re: Remote view - who should create it? - discussion point!

If I had a database I certainly would not have people doing query's on them remotely: I'd instead ask them to specify what they want and write the query myself so they could use my query and I'd be in control about what is happening on my database.

Apart from that: there's is a column in PLAN_TABLE (I think it is OTHER) that shows you which SQL-statement is sent to the remote system. Maybe you could fiddle with your query a bit and see if you can get the bulk of the query performed on the remote database.

Jaap.

On Tue, 05 Feb 2002 13:27:00 GMT, smithp_at_ntlworld.com.notreally (Paul Smith) wrote:

>I have to create a COMPLEX view on a remote database, which links 6 different
>tables, and performs a subquery. My database is small, the remote database is
>large. I various theories about what is happening and where the query should be
>done, but I don't know enough about the internals of Oracle to know if my
>argument is correct. The administrator of the remote database won't let me
>create the view directly on his machine (which I think is the sensible way), but
>wants me to create it on MY machine....
>
>The view (if created on the remote machine) goes soemthing like....
>
>create or replace view xyz as
>select a, b, c, d
>least(expression)
>from
> e,f,g,
> (
> select h, i
> from
> (
> j, k
> )
> where
> ...
> group by
>}
>where
>....
>
>If I have to create the view then...
>
>create or replace view xyz as
>select a, b, c, d
>least(expression)
>from
> e_at_REMOTE,f_at_REMOTE,g_at_REMOTE,
> (
> select h, i
> from
> (
> j_at_REMOTE, k_at_REMOTE
> )
> where
> ...
> group by
>}
>where
>....
>
>
>1/ I am getting ORA-1652 errors - my TEMP tablespace is too small. - Since I
>don't know how much data will come back how can I sensibly pick a new datafile
>size?
>2/ Surely the remote database is sending most of the information back to me to
>do the hard work if it is written in the second form - putting lots of data on
>the network.
>3/ Would it not be sensible for the BIG database to handle the BIG query on it's
>tablespace as it would be better sized for it anyway.
>4/ With such a complex view, would it not be better on the remote machine for
>the purposes of the administration of bug fixes, changes etc. ( I didn't write
>the view in the first place! )
>5/ Surely the remote database won't have to do much extra work to give me the
>required view. (One of the arguments for doing it on my database is that the
>remote system is already heavily loaded.)
>
>
>I would be very interested to see what views (no pun!) you guys have on this
>issue. I you have probably guessed, I am trying to get the view made on the
>remote machine.
>
>
>Many Thanks
>Paul Smith
>
>
>
>
>
>--
>Paul Smith
Received on Thu Feb 07 2002 - 13:20:42 CST