From oracle-l-bounce@freelists.org Tue Jun 29 03:41:18 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i5T8f2n05157 for ; Tue, 29 Jun 2004 03:41:13 -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 i5T8eq605121 for ; Tue, 29 Jun 2004 03:41:02 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7FDA272C483; Tue, 29 Jun 2004 03:23:31 -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 03776-78; Tue, 29 Jun 2004 03:23:31 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BF18272C4B2; Tue, 29 Jun 2004 03:23:30 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 29 Jun 2004 03:22:07 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B38D472C448 for ; Tue, 29 Jun 2004 03:22:06 -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 03776-48 for ; Tue, 29 Jun 2004 03:22:06 -0500 (EST) Received: from smtp801.mail.ukl.yahoo.com (smtp801.mail.ukl.yahoo.com [217.12.12.138]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id F01D572C442 for ; Tue, 29 Jun 2004 03:22:05 -0500 (EST) Received: from unknown (HELO Primary) (oracle-l@freelists.org@217.42.79.68 with poptime) by smtp801.mail.ukl.yahoo.com with SMTP; 29 Jun 2004 08:44:34 -0000 Message-ID: <017a01c45db5$4dea7010$7102a8c0@Primary> From: "Jonathan Lewis" To: References: Subject: Re: Physics of the FILTER operation within SQL_PLANE. Date: Tue, 29 Jun 2004 09:44:34 +0100 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 3909 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jonathan@jlcomp.demon.co.uk Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org I've just done a little more work based on your test case, checking which rows in the filtering table get hit. Based on these observations, I think Oracle creates a hash table for the results of the FILTERing probe, using the probe "key" as the basis for the hashkey. On each probe, Oracle saves the result in the hash table, so long as there are no hash collisions. If a new probe key collides with an existing probe key, then the result is not saved. I also infer that in your test case, the size of the hash table is 128. I would guess, but have not yet confirmed, that the size of the hash table is set as the query starts, - but only when running the cost based optimizer, so that Oracle has an estimate of the number of probe keys that need to be stored. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st ----- Original Message ----- From: To: Sent: Monday, June 28, 2004 12:00 AM Subject: Re: Physics of the FILTER operation within SQL_PLANE. FILTER operation effectiveness depends on how (in which order) rows are inserted into driving table. Looks similar to clustering factor in index range scans ;) Take a look on simple TESTCASE I have made on 9.2.0.4 Win2000. As you can see LIO count defers by 17 times (First case 63 LIO, second 1061) depending on order how rows have been inserted. One more effect, if we reduce row count in filter table (third test), then Oracle execute filter operation more effective (LIO=43) independing of inserting order, due to "remembering results of previous probes" probably. 1. Any comments? >> [Jonathan Lewis] However, FILTER can be much more efficient than nested loop, because it can remember results of previous probes into the second table - effectively making the probe an in-memory lookup. 2. Any ideas how many "results of previous probes" Oracle can "remember" for next comparison? Best regards, Jurijs ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------