From oracle-l-bounce@freelists.org Wed Oct 20 15:35:38 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i9KKZcE24063 for ; Wed, 20 Oct 2004 15:35:38 -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 i9KKZbI24058 for ; Wed, 20 Oct 2004 15:35:37 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 09B5372DF85; Wed, 20 Oct 2004 15:41:46 -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 22509-03; Wed, 20 Oct 2004 15:41:45 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5FBD472DF91; Wed, 20 Oct 2004 15:41:40 -0500 (EST) DomainKey-Signature: a=rsa-sha1; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=P8HmIFN9+gjT7RspQi9VgZOIqkQwSfbRcCankLRSIp2doUcT0RSUzRArh0UjJ2wYQUY1by4nn5KfkoSc8+3pGOyWce1492kKSY5k3RvqWRjlMehB/bhP9Y8xsK2K55cYGv4UxSiCvTEMengyuO1eTkaDOhzDGCzEcymXUFKmfIw Message-ID: Date: Wed, 20 Oct 2004 16:39:40 -0400 From: Sami Seerangan To: Antonio Belloni Subject: Re: Why the optimizer is not choosing the best plan? Cc: Oracle Discussion List In-Reply-To: <20041020201838.55460.qmail@web20103.mail.yahoo.com> Mime-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit References: <20041020201838.55460.qmail@web20103.mail.yahoo.com> X-archive-position: 11328 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: dba.orcl@gmail.com Precedence: normal Reply-To: dba.orcl@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org I have the values like below, but still it is going for NL_JOIN. SQL> show parameter hash NAME TYPE VALUE ------------------------------------ ------- ------------------------------ hash_area_size integer 2000000 hash_join_enabled boolean TRUE hash_multiblock_io_count integer 0 SQL> show parameter join NAME TYPE VALUE ------------------------------------ ------- ------------------------------ always_anti_join string HASH always_semi_join string HASH hash_join_enabled boolean TRUE SQL> On Wed, 20 Oct 2004 17:18:38 -0300 (ART), Antonio Belloni wrote: > Hi, > > If you want that Oracle consider hash joins , you > should set the following parameters in the init.ora: > > HASH_JOIN_ENABLED = TRUE > HASH_AREA_SIZE = n , where n is the amount of memory > allocated for hash joins. > > Also , consider setting the following parameters: > ALWAYS_SEMI_JOIN = HASH > ALWAYS_ANTI_JOIN = HASH > > So , semi and anti join operations will be using hash > join. -- http://www.freelists.org/webpage/oracle-l