Poor Sql Perf via DBLINK

From: Lee E Parsons <lparsons_at_world.std.com>
Date: Thu, 14 Jul 1994 22:43:10 GMT
Message-ID: <CsyCFz.14u_at_world.std.com>

                                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

Received on Fri Jul 15 1994 - 00:43:10 CEST