Re: Performance using db_link

In article <37B07082.9512C748_at_merck.com>,   Clifford Buetikofer <clifford_buetikofer_at_merck.com> wrote:
> I have a schema which will be migrating to a new data model and even
to
> a new instance on a different operating system. Because we have many
> legacy applications accessing this schema, we still need to support
the
> old table objects.
>
> Right now I have 2 options on the table:
>
> 1 - create a public synonym, "old_schema.table", in the old instance
> which points to:

new_schema.view_at_db_link_to_new_instance
>
> OR
>
> 2 - create a view in the old instance which is:
> select * from new_schema.view_at_db_link_to_new_instance
>
> If I do a simple select of 1 column using both options, it takes more
> than 5 minutes to retrieve the 1 record. The views in the new schema
> are very complex and I believe that is where I should focus my tuning.
> Which option listed above is "standard" ?
>
> TIA,
> Cliff
>
> The contents of this message express only the sender's opinion.
> This message does not necessarily reflect the policy or views
of
> my employer, Merck & Co., Inc. All responsibility for the
statements
> made in this Usenet posting resides solely and completely with
the
> sender.
>

Do the contents of the old objects change frequently? If not, you could create a simple snapshot of the table(s) and define it to refresh every X hours or overnight. This would improve performance since access would not rely on the network performance that the db link must contend with.

As far as I have experienced, there was no great performance difference in the use of the pub syn or the view. However, if the table is altered in the new schema, the view would need to be recompiled, whereas the public synonym would not care - so the maintenance may be easier with a public synonym. The view would still be necessary, however, if you wanted only a subset of the columns to be visible.

• Lisa

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Aug 12 1999 - 04:27:52 CDT