Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Query Problems Across Database Link
DDayDawg wrote:
> I am working with two Oracle 9i databases. One we are using as a data
> warehouse to hold selective information from the other (which of course
> is being pulled over the database link). The problem is my programmers
> are telling me that they cannot pull the data over as the normally
> would in a single database environment because the system is hanging.
>
> The primary table they are pulling from is large, roughly 750,000,000
> rows. That is on the foriegn side of the link. So if D1 is the Data
> Warehouse and D2 is the Production system the command they are using
> is:
>
> select a.*
> from table_a a, /*(in D1)*/
> table_b_at_d2 b
> where a.active_ind = 1
> and b.row_id = a.row_id
> and b.updt_dt_tm != a.updt_dt_tm
>
>
> What it looks like it's doing to me is it seems to be trying to pull
> the entire table_b across the database link into temp space so that it
> can do comparisons. I'm really hoping this isn't how the database link
> is supposed to work or we are going to have some real problems. Anyone
> have a clue of what we are doing wrong?
Before digging too deeply into why this SQL statement is performing slowly, double-check the SQL statement. Are you trying to compare the ROWID for one table in one database with a ROWID in another database. A ROWID that uniquely identifies a row in one database, will likely represent an entirely different schema and table in another database. As a quick example, if you use an ALTER table MOVE command to relocate a table in the same tablespace, you must rebuild the indexes on the table - why? The ROWIDs for each row very likely changed. For a discussion on the composition/use of ROWIDs: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:912210644860
If your column is actually named ROW_ID, please ignore the above.
I see in your query that you specified:
A.ACTIVE_IND = 1, would it also be true that B.ACTIVE_IND = 1?
What if you rewrite the query, does it help:
SELECT
A.*
FROM
TABLE_A A,
(SELECT
B.ROW_ID,
B.UPDT_DT_TM
FROM
TABLE_B_at_D2 B) B
WHERE
A.ACTIVE_IND = 1
AND A.ROW_ID = B.ROW_ID
AND A.UPDT_DT_TM != B.UPDT_DT_TM;
The above, if executed as I expect, should reduce the amount of data
sent between the two databases as only the UPDT_DT_TM and ROW_ID
columns should be retrieved from the remote database. This should also
reduce the demand for temp space unless table B only contains 2 or 3
columns.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Mon Oct 23 2006 - 19:31:09 CDT