Re: Physics of the FILTER operation within SQL

From: <J.Velikanovs_at_alise.lv>
Date: Mon, 28 Jun 2004 02:00:42 +0300
Message-ID: <OFEC6AAC6C.D021A005-ONC2256EC0.007E20A4-C2256EC0.007F13C8@alise.lv>

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.

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.
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" <jonathan_at_jlcomp.demon.co.uk> Sent by: oracle-l-bounce_at_freelists.org
23.06.2004 11:15
Please respond to oracle-l

        To:     <oracle-l_at_freelists.org>
        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" <Daniel.Fink_at_Sun.COM> To: <oracle-l_at_freelists.org> 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_at_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_at_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_at_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


-----------------------------------------------------------------

Received on Sun Jun 27 2004 - 18:06:34 CDT

Re: Physics of the FILTER operation within SQL_PLANE.