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

It would always be better if you create a View on the remote database and then call that view through your query.

Talk your coutre part into it.

All the best ....

--
HTH

Additions and Corrections Welcome.

Regards,
Ganesh R


"Tony" <ar_at_hotmail.com> wrote in message
news:1012918363.28213.0.nnrp-07.d4f0f429_at_news.demon.co.uk...


> 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

>

>