From oracle-l-bounce@freelists.org Sun Jun 27 18:06:34 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i5RN64P28615 for ; Sun, 27 Jun 2004 18:06:14 -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 i5RN5h628603 for ; Sun, 27 Jun 2004 18:06:03 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 58A9472C067; Sun, 27 Jun 2004 17:48:01 -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 05246-79; Sun, 27 Jun 2004 17:48:01 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EFB8072C4D0; Sun, 27 Jun 2004 17:47:28 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Sun, 27 Jun 2004 17:45:51 -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 AED3172C54D for ; Sun, 27 Jun 2004 17:45:49 -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 05332-29 for ; Sun, 27 Jun 2004 17:45:49 -0500 (EST) Received: from ross.alise.lv (ross.alise.lv [194.19.227.242]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 19E6F72C48A for ; Sun, 27 Jun 2004 17:45:48 -0500 (EST) In-Reply-To: <016201c458fa$35e20650$7102a8c0@Primary> To: oracle-l@freelists.org Subject: Re: Physics of the FILTER operation within SQL_PLANE. MIME-Version: 1.0 X-Mailer: Lotus Notes Release 6.5.1 January 21, 2004 Message-ID: From: J.Velikanovs@alise.lv Date: Mon, 28 Jun 2004 02:00:42 +0300 X-MIMETrack: Serialize by Router on ROSS/IT ALISE/LV(Release 5.0.11 |July 24, 2002) at 2004.06.28 02:00:45, Serialize complete at 2004.06.28 02:00:45 Content-type: text/plain X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 3778 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: J.Velikanovs@alise.lv Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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 TESTCASE ======================================== Preparation part (common for all tests) ------------------------------- drop table main_tab; drop table filter_tab; create table main_tab (n number, v varchar2(100)); create table filter_tab (n number, v varchar2(100)); begin for f in 1..20 loop insert into filter_tab values (f,'a'); end loop; commit; end; / create unique index filter_tab_i1 on filter_tab (n); ------------------------------ -------- First test -------- ------------------------------ truncate table main_tab; begin for f2 in 1..20 loop for f1 in 1..500 loop insert into main_tab values (f2,'a'); end loop; end loop; commit; end; / ------------------------------ SQL Trace 10046 (tkprof output) ------------------------------ select /*+ RULE */ count(m.v) from main_tab m where exists (select v from filter_tab f where f.n=m.n and f.v like 'a%') call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.10 0.11 0 63 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.10 0.11 0 63 0 1 Misses in library cache during parse: 1 Optimizer mode: RULE Parsing user id: SYS Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=63 r=0 w=0 time=111286 us) 10000 FILTER (cr=63 r=0 w=0 time=86030 us) 10000 TABLE ACCESS FULL MAIN_TAB (cr=23 r=0 w=0 time=28758 us) 20 TABLE ACCESS BY INDEX ROWID FILTER_TAB (cr=40 r=0 w=0 time=425 us) 20 INDEX UNIQUE SCAN FILTER_TAB_I1 (cr=20 r=0 w=0 time=194 us)(object id 9669) ------------------------------ -------- Second test ------- ------------------------------ truncate table main_tab; begin for f1 in 1..500 loop for f2 in 1..20 loop insert into main_tab values (f2,'a'); end loop; end loop; commit; end; / ------------------------------ SQL Trace 10046 (tkprof output) ------------------------------ select /*+ RULE */ count(m.v) from main_tab m where exists (select v from filter_tab f where f.n=m.n and f.v like 'a%') call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.12 0.12 0 1061 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.12 0.13 0 1061 0 1 Misses in library cache during parse: 1 Optimizer mode: RULE Parsing user id: SYS Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=1061 r=0 w=0 time=129048 us) 10000 FILTER (cr=1061 r=0 w=0 time=103463 us) 10000 TABLE ACCESS FULL MAIN_TAB (cr=23 r=0 w=0 time=28637 us) 519 TABLE ACCESS BY INDEX ROWID FILTER_TAB (cr=1038 r=0 w=0 time=8436 us) 519 INDEX UNIQUE SCAN FILTER_TAB_I1 (cr=519 r=0 w=0 time=3711 us)(object id 9666) ------------------------------ -------- Third test ------- ------------------------------ truncate table main_tab; begin for f1 in 1..1000 loop for f2 in 1..10 loop insert into main_tab values (f2,'a'); end loop; end loop; commit; end; / ------------------------------ SQL Trace 10046 (tkprof output) ------------------------------ select /*+ RULE */ count(m.v) from main_tab m where exists (select v from filter_tab f where f.n=m.n and f.v like 'a%') call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.11 0.11 0 43 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.11 0.11 0 43 0 1 Misses in library cache during parse: 1 Optimizer mode: RULE Parsing user id: SYS Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=43 r=0 w=0 time=115589 us) 10000 FILTER (cr=43 r=0 w=0 time=89627 us) 10000 TABLE ACCESS FULL MAIN_TAB (cr=23 r=0 w=0 time=29590 us) 10 TABLE ACCESS BY INDEX ROWID FILTER_TAB (cr=20 r=0 w=0 time=186 us) 10 INDEX UNIQUE SCAN FILTER_TAB_I1 (cr=10 r=0 w=0 time=82 us)(object id 9672) Jurijs 9268222 ============================================ http://otn.oracle.com/ocm/jvelikanovs.html "Jonathan Lewis" Sent by: oracle-l-bounce@freelists.org 23.06.2004 11:15 Please respond to oracle-l To: cc: Subject: Re: Physics of the FILTER operation within SQL_PLANE. This example of a FILTER is very similar to a nested loop join - for each row in the driving table, Oracle operates the filter condition to determine whether or not to keep a row. 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. I suspect that this is happening in this case. 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: "Daniel Fink" To: Sent: Tuesday, June 22, 2004 2:02 PM Subject: Re: Physics of the FILTER operation within SQL_PLANE. Jurijs, It is hard to determine exactly what is happenning without knowing the structure and relationship of the objects. For example, what is the mns_phs_pk index column(s)? Is unify only in the mns_pharmacies table? I would venture to say that the FILTER operation is not a JOIN operation. In the 1st query, the DKM_OUTBILL_RECEIPTS table is scanned and a list of PHS_IDs is the result set. These are then used to probe the MNS_PHARMACIES table via the MNS_PHS_PK index. When a PHS_ID is found where UNIFY != 'N', the PHS_ID is FILTERed (discarded). Since you are returning any data from the MNS_PHARMACIES table, there is no need to join the tables. The only thing that 'bothers' me is the low number of consistent reads in the first query against the MNS_PHARMACIES and MNS_PHS_PK objects. I suppose (really just a guess) that there are a small number of distinct PHS_ID values in the DKM_OUTBILL_RECEIPTS table, so the UNIQUE SCAN actually returns multiple values of ID, which oracle uses to FILTER. This is the best I can reason out. Perhaps the more learned colleagues on the list can shed more light on the inner workings of sql operations. Regards, Daniel J.Velikanovs@alise.lv wrote: > Daniel, thanks for the answer. > It is seams I miss some think. > Take a look on the plan bellow. > How FILTER operation can join to tables? > From plane it is seems that FILTER operation join tow tables > DKM_OUTBILL_RECEIPTS and MNS_PHARMACIES. > > > ---------------------------------------------------------------- > | Id | Operation | Name | > ---------------------------------------------------------------- > | 0 | SELECT STATEMENT | | > | 1 | SORT AGGREGATE | | > |* 2 | FILTER | | > | 3 | TABLE ACCESS FULL | DKM_OUTBILL_RECEIPTS | > |* 4 | TABLE ACCESS BY INDEX ROWID| MNS_PHARMACIES | > |* 5 | INDEX UNIQUE SCAN | MNS_PHS_PK | > ---------------------------------------------------------------- > > > 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------