RE: select via dblink does not use index

From: Poras, Henry R. <Henry_Poras_at_dfci.harvard.edu>
Date: Thu, 04 Dec 2003 07:34:34 -0800
Message-ID: <F001.005D8CB1.20031204073434@fatcity.com>

Yechial,
It's been a couple of years since I worked on tuning queries with db links, but a couple of issues come to mind:is the correct table being used for the inner table of the join, is too much data being sent over the network.

-is the correct table being used for the inner table: I remember in earlier
versions of Oracle, the local table was always the driving table. I don't know if that is stll the case, but it would be clear from the explain plan. The smaller table (local_table) should be the inner table of your hash join. Of course if this is the case, the full 1M records of the remote_table are being pulled across and compared to the hash table. ( a 10046 trace should help show if this is where the time is going)

-is too much data being sent over the network: Assuming your result set is much
smaller than the number of records in your remote table, you can run the query on the remote side and then bring back the result set. On the Target database create a view (create view my_remote_view as select * from target_table, source_table_at_source_dblink where ...). Kick off the query from your source database using (select * from my_remote_view_at_target_dblink). The query is run on the remote side with only the result set passed back. Of course you now have to check if the correct table is being used as the inner table for the hash join (see the first point). If not, a different execution plan might be necessary.

There also might be some newer features provided for distributed queries which I haven't had the chance to use yet.

Henry

-----Original Message-----

Adar
Sent: Thursday, December 04, 2003 8:29 AM To: Multiple recipients of list ORACLE-L

Here are all the details:

Source database 9.2.0.4 (upgrade from 8.1.6.3.4). Target database 8.1.6.3.4.

View definition: create view my_view as select * from xx.lak_at_target_dblink.

Sql: select * from local_table , my_view where local_table.branch = 1
and my_view.customer = 20000000000 + local_table.branch * 100000000 + local_table.customer;

All tables are analyzed.

There are about 300 records in local_table and 1M records in remote_table. My_view.customer is primary key of target_table.

Where branch =1 is a set of 65 records.

Optimizer_mode=choose in both databases.

Explain plan: Hash join between FTS on local table and remote (in/out = serial).

Yechiel Adar
Mehish

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Yechiel Adar
INET: adar76_at_inter.net.il

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Poras, Henry R.
INET: Henry_Poras_at_dfci.harvard.edu

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message