Re: Poor Sql Perf via DBLINK
Date: 15 Jul 94 21:31:27 EST
Message-ID: <1994Jul15.213127.1_at_corp02.d51.lilly.com>
In article <CsyCFz.14u_at_world.std.com>, lparsons_at_world.std.com (Lee E Parsons) writes:
> Problem:
>
> I have two systems on the network. Table SOC_NUM on system A contains a short
> list of social security numbers. I want to select the name of those
> individuals from the master employees list on system B. This list is called
> USERS and is naturally rather large. USERS is accessed from A by a database
> link.
>
> -----------------------------------------------------------------------
> | |
> | SYSTEM A SYSTEM B |
> | |
> | DB LINK |
> | TABLE SOC_NUM (SSN)------------------------>TABLE USERS (NAME, SSN) |
> | |
> | select USERS.name, SOC_NUM.ssn |
> | from SOC_NUM.a, USERS_at_B b |
> | where a.SSN = b.SSN; |
> -----------------------------------------------------------------------
>
> The problem is that when the above staement is executed from sys A
> the execution plan mandates that all of USERS be shipped from B to A. Only
> after this ton of data is shipped back does is the SOC_NUM table evaluated.
> The prefered method would be to scan SOC_NUM and do a index lookup on
> USERS.
>
> FYI - USERS is actually a pretty ugly view of 5 tables
>
> Question:
>
> When a database link is involved how do we control the execution plan?
>
> How do we make the optimizer understand that he needs use SOC_NUM as the
> driving table?
>
> Should I lose the dblink and go with a snap shot? (Of course since USERS
> is a view and not all of the data in the underlying tables can be viewed
> by sys A, I not sure what I would snapshot)
>
> Is all of life this hard?
>
> Thanx for you imput.
> --
> Regards,
>
> Lee E. Parsons
> Systems Oracle DBA lparsons_at_world.std.com
-- Bob Swisshelm | swisshelm_at_Lilly.com | 317 276 5472 Eli Lilly and Company | Lilly Corporate Center | Indianapolis, IN 46285Received on Sat Jul 16 1994 - 04:31:27 CEST