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

Hi Paul,

My advise depends on wether the CLient PC is pointing to your database or if it can point to the remote one.

If it is your database then create a DB link to the remote database and run yor query instead of selecting the columns, select a count(*) and try to estimate with the number of rows and subsequent describes on tables the size of the data coming back. This will help you to estimate the size of the new TEMP tablespace that you will require.

If you remote colleague is unwilling to play and you require this information then hey, you have no choice and it will be another learning experience.

If the DB link is called REMOTE then your query to help you estimate size of TEMP tablespace will be will be something like this

 select tab1.a, tab2.b, tab3.c, tab3.d
 least(expression)
 from
  e_at_REMOTE tab1 ,f_at_REMOTE tab2 ,g_at_REMOTE tab3 where tab1.a in
  (
   select tab4.h,
   from
   (
     j_at_REMOTE tab4, k_at_REMOTE tab5
   )

   ...
   group by

Hope this helps

regards

Tony Rees
ITD DMC Stratex Networks (Scotland)

"Paul Smith" <smithp_at_ntlworld.com.notreally> wrote in message news:3c5fdd99.15237510_at_news.ntlworld.com...
> 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 Tue Feb 05 2002 - 08:15:07 CST