Re: select via dblink does not use index
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 [EMAIL PROTECTED] Sql: select * from local_table , my_view where local_table.branch = 1 and my_view.customer = 200 + local_table.branch * 1 + 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: select via dblink does not use index
Solved. It was a hash join with the smaller table first but it pulled the whole 1M records for this. There was a: where local_table.branch = 1 in the query. I changed it into: where remote_table.branch = 1 (there is an index on remote_table.branch) and it came down to 2 seconds. Thanks all. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 9:49 PM I did a select like: select name from local_table , remote_table where local_table.account = remote_table.account. Remote table is a view with dblink. I select about 100 records out of about 1M records at the remote db. I found out that oracle does full table scan at the remote site. I will welcome ideas how to make oracle use the index on the remote side. Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Yechiel Adar INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: select via dblink does not use index
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, [EMAIL PROTECTED] where ...). Kick off the query from your source database using (select * from [EMAIL PROTECTED]). 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 [EMAIL PROTECTED] Sql: select * from local_table , my_view where local_table.branch = 1 and my_view.customer = 200 + local_table.branch * 1 + 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: select via dblink does not use index
What are the Oracle versions, settings for optimizer_mode and full execution plans. Can you clarify Remote table is a view with dblink. Do you mean your query references a local view which is a select from a remote table; or does your query reference a view at a remote site which is a simple select from a table at that site. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 7:49 PM I did a select like: select name from local_table , remote_table where local_table.account = remote_table.account. Remote table is a view with dblink. I select about 100 records out of about 1M records at the remote db. I found out that oracle does full table scan at the remote site. I will welcome ideas how to make oracle use the index on the remote side. Yechiel Adar Mehish -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
