On My LOCAL database I have the following table. It is a small table, a few rows and has a PK on ACCOUNT_CATEGORY.
CREATE TABLE ACCOUNT_CATEGORY_VALUES
(
ACCOUNT_CATEGORY NUMBER(3) NOT NULL,
LANGUAGE_CODE NUMBER(6) NOT NULL,
SHORT_DISPLAY VARCHAR2(15 BYTE),
DISPLAY_VALUE VARCHAR2(240 BYTE) NOT NULL
);
On My REMOTE database I have the following table, It is a large table with millions of rows.
CREATE TABLE CUSTACCTS
(
ACCOUNT_NO NUMBER(10) NOT NULL,
PARENT_ID NUMBER(10),
CHILD_COUNT NUMBER(10) NOT NULL,
HIERARCHY_ID NUMBER(10),
BILL_SEQUENCE_NUM NUMBER(10) NOT NULL,
CURRENCY_CODE NUMBER(6) DEFAULT 1 NOT NULL,
LANGUAGE_CODE NUMBER(6) DEFAULT 1 NOT NULL,
ACCOUNT_TYPE NUMBER(3) DEFAULT 1 NOT NULL,
ACCOUNT_CATEGORY NUMBER(3) NOT NULL,
NEXT_BILL_DATE DATE
);
I have the following SQL on the LOCAL database (Explain Plan is shown)...
select
acc.*
from CUSTACCTS@cus01 acc, account_category_values ac
where acc.account_category = ac.account_category
and acc.account_category = 2;
Plan
SELECT STATEMENT ALL_ROWS Cost: 7 K Bytes: 404 Cardinality: 1
4 MERGE JOIN CARTESIAN Cost: 7 K Bytes: 404 Cardinality: 1
1 REMOTE REMOTE SERIAL_FROM_REMOTE CUSTACCTS CUS01 Cost: 7 K Bytes: 401 Cardinality: 1
3 BUFFER SORT Cost: 1 Bytes: 3 Cardinality: 1
2 INDEX RANGE SCAN INDEX (UNIQUE) BILLSC.ACCOUNT_CATEGORY_VALUES_PK Cost: 1 Bytes: 3 Cardinality: 1
I see this is not so good so, rather than pull the entire dataset from ACC across the DB link, I set the driving site.
select --+ driving_site (acc)
acc.*
from CUSTACCTS@cus01 acc, account_category_values ac
where acc.account_category = ac.account_category
and acc.account_category = 2;
Plan
SELECT STATEMENT REMOTE ALL_ROWS Cost: 31 K Bytes: 414 Cardinality: 1
4 MERGE JOIN CARTESIAN Cost: 31 K Bytes: 414 Cardinality: 1
1 TABLE ACCESS FULL TABLE BILLSC.CUSTACCTS DCU021N Cost: 31 K Bytes: 401 Cardinality: 1
3 BUFFER SORT Cost: 1 Bytes: 13 Cardinality: 1
2 REMOTE REMOTE SERIAL_FROM_REMOTE ACCOUNT_CATEGORY_VALUES !Cost: 1 Bytes: 13 Cardinality: 1
This Works fine, performance is much improved.. BUT what if I have several DB links and I want the account data returned from all of them? so I try the following....
select --+ driving_site (acc)
acc.*
from CUSTACCTS@cus01 acc, account_category_values ac
where acc.account_category = ac.account_category
and acc.account_category = 2
UNION ALL
select --+ driving_site (acc)
acc.*
from CUSTACCTS@cus02 acc, account_category_values ac
where acc.account_category = ac.account_category
and acc.account_category = 2;
Plan
SELECT STATEMENT ALL_ROWS Cost: 14 K Bytes: 810 Cardinality: 2
9 UNION-ALL
4 MERGE JOIN CARTESIAN Cost: 7 K Bytes: 404 Cardinality: 1
1 REMOTE REMOTE SERIAL_FROM_REMOTE CUSTACCTS CUS01 Cost: 7 K Bytes: 401 Cardinality: 1
3 BUFFER SORT Cost: 1 Bytes: 3 Cardinality: 1
2 INDEX RANGE SCAN INDEX (UNIQUE) BILLSC.ACCOUNT_CATEGORY_VALUES_PK Cost: 1 Bytes: 3 Cardinality: 1
8 MERGE JOIN CARTESIAN Cost: 7 K Bytes: 406 Cardinality: 1
5 REMOTE REMOTE SERIAL_FROM_REMOTE CUSTACCTS CUS02 Cost: 7 K Bytes: 403 Cardinality: 1
7 BUFFER SORT Cost: 1 Bytes: 3 Cardinality: 1
6 INDEX RANGE SCAN INDEX (UNIQUE) BILLSC.ACCOUNT_CATEGORY_VALUES_PK Cost: 1 Bytes: 3 Cardinality: 1
We can see that as soon as I do this, the UNION ALL is forcing the data from each DB link to be brought back again, rather than the work being done on the remote sites. How can the driving_site hint be employed with multiple Database Links?
] 
Blog
Search
Help
Members
Register
Login
Home
