From oracle-l-bounce@freelists.org Wed May 18 18:01:56 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j4IN1tU6002022 for ; Wed, 18 May 2005 18:01:55 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j4IN1t4Z002018 for ; Wed, 18 May 2005 18:01:55 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8B17B195373; Wed, 18 May 2005 16:59:05 -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 26871-04; Wed, 18 May 2005 16:59:05 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0FA2C195505; Wed, 18 May 2005 16:59:05 -0500 (EST) Message-ID: <020e01c55bf4$12874550$3c02a8c0@JARAWIN> From: "jaromir nemec" To: , References: Subject: Re: Hash semi join with IN subquery Date: Wed, 18 May 2005 23:53:47 +0200 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-archive-position: 19897 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jaromir@db-nemec.com Precedence: normal Reply-To: jaromir@db-nemec.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL autolearn=ham version=2.63 Hi Josef, I guess the reason of the semi join is that the CBO doesn't get the right cardinality of the global temporary table (assuming there are much more records than 10 in your example). Use cardinality hint to check this UPDATE sessions SET expired =3D 'T' WHERE session_key IN (SELECT /*+ cardinality(temp_session_ids 10) */ session_id FROM temp_session_ids) See other possibilieties to set statistics for temporary table on http://asktom.oracle.com/pls/ask/f?p=4950:8:3276292286690697205::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:18734376046450 HTH Jaromir ----- Original Message ----- From: "Stalin" To: Sent: Wednesday, May 18, 2005 9:31 PM Subject: Hash semi join with IN subquery -- http://www.freelists.org/webpage/oracle-l