From oracle-l-bounce@freelists.org Thu Sep 23 07:37:17 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i8NCbGO10174 for ; Thu, 23 Sep 2004 07:37:16 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i8NCbFI10143 for ; Thu, 23 Sep 2004 07:37:15 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B106672D84D; Thu, 23 Sep 2004 07:43:13 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 32630-89; Thu, 23 Sep 2004 07:43:13 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DDFA372D7AE; Thu, 23 Sep 2004 07:43:08 -0500 (EST) Message-ID: <0d6c01c4a171$12096b00$c701a8c0@sys3> From: "Yechiel Adar" To: "'adar76@inter.net.il'" , "Oracle-L (E-mail)" References: Subject: Re: DB Links Date: Thu, 23 Sep 2004 15:27:25 +0200 MIME-Version: 1.0 Content-type: text/plain; charset=windows-1255 Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-archive-position: 9964 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: adar76@inter.net.il Precedence: normal Reply-To: adar76@inter.net.il X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org I found this by searching metalink for driving_site: This also answer Kevin question about joins over dblink. From: Melanie Kiburtz 10-Aug-04 18:19 Subject: Re : performance of joins over a dblink The whole issue with remote joins has to do with the fact that oracle will NOT utilize remote indices if the join is executed locally. So, in the case where you have a local small table joined with a remote large table, the entire remote table (referenced columns) is sent to the local db for joining activity. Local indices can be used for the local tables. So this is why the 'remote reverse query' method is used. By setting up the appropriate views, synonyms and dblinks to force the query to actually be executed on the remote db performance can be dramatically improved. The forcing of the query to execute on the remote db, the small table is then sent to the remote db for the join function. Since the sql is actually executed on the remote db, the remote db is the 'local db' for the query execution and therefore can utilize the indices for table on that db. The small table would NOT utilize indices in this case. Does it work? Yes and extremely well as long as there is a significant difference in the sizes of the tables and indices are actually used. Keep in mind that either the optimizer or by virtue of the sql code itself, the 'desired' index may not be used unless hints are employed. You will need to carefully examine the explain plans. Another option with 9i is the following pasted info which eliminates the need to mess with views, synonyms etc yourself. For 3rd party software you could create a synonyms or view locally that induces the hint or you could use OUTLN to force the plan: =========================== DRIVING_SITE The DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization. driving_site_hint::= where table is the name or alias for the table at which site the execution should take place. For example: SELECT /*+DRIVING_SITE(dept)*/ * FROM emp, dept@rsite WHERE emp.deptno = dept.deptno; If this query is executed without the hint, then rows from dept are sent to the local site, and the join is executed there. With the hint, the rows from emp are sent to the remote site, and the query is executed there, returning the result to the local site. This hint is useful if you are using distributed query optimization. /*+ DRIVING_SITE ( table ) */ ================== the above was pasted from the 9i Database Performance Tuning Guide and Reference manual Melanie Kiburtz DBA Oregon, USA Yechiel Adar Mehish -- http://www.freelists.org/webpage/oracle-l