Re: Problem with database link from Oracle Version 6 to Oracle 7
Date: 1995/04/18
Message-ID: <D78Fvw.1My_at_lanier.com>#1/1
In article <3mv7um$a3h_at_bbs.pnl.gov>, Varunee Buerkle <v_buerkle_at_pnl.gov> wrote:
>
>We have a medium size of production database version 6 that we are currenthly converted some of the
>application to Oracle 7.
> ...
>The queries is submitted from Oracle Version 6 with some of the table use in the
>query reside in Oracle 7. I created database link in Oracle Version 6 , so the queries
>can see the table that are in Oracle 7. Well, the queries can see fine, the problem is
>taking a very long time to produce a report. I ran an explain facility from Oracle 6 first
>with the copy of the table in Oracle 6, the plan show the query is using INDEX, and it ran
>very fast. Then I dropped the table from Oracle 6 and created the link point to Oracle 7,
>ran the explain and it show nothing about the table in Oracle 7, except saying it is remoted.
>I believe it is doing a full table scan for the table in Oracle 7.
>
You may be able to get some improvement by creating a view in the Oracle 7 instance and running your query against the view. (The view can be select * from <table>). This sometimes helps because the optimizer behaves differently when a view is used.
We have several applications where an Oracle 6 database accesses tables in an Oracle 7 database. Sometimes performance is OK with simple database links, sometimes we have to create a view in O7, and sometimes we have to do what you did -- copy the table back to O6. You may have to experiment to find out what works best in your situation.
Don
Donald E. Vick (dvick_at_lanier.com, dvick_at_crl.com) Voice: (404) 493-2194 Fax: (404) 493-2399 Received on Tue Apr 18 1995 - 00:00:00 CEST