Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query Problems Across Database Link

Re: Query Problems Across Database Link

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 23 Oct 2006 17:31:09 -0700
Message-ID: <1161649869.477491.288320@k70g2000cwa.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US