Re: Physics of the FILTER operation within SQL_PLANE.
- From: J.Velikanovs@xxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 30 Jun 2004 01:36:47 +0300
Jonathan,
thank you for your interest in this issue
>> If a new probe key collides with an existing probe key, then the result
is not saved.
I suspect this assumption may be not true.
Take a look on first and second test in my example:
They are different jus by inserted rows order in main_tab table.
First one:
1, 1, 1, ? 500 times, 2, 2, 2, ? , 500 times ?. 20, 20, 20, ? 500 times
63 LIO = 23 FullScan + 40 IQS+TA
Second:
1,2,3,?20, 1,2,3?20, 1,2,3?20 ? 500 times
1061 LIO = 23 FS + 1038 IS+TA
If mentioned assumption true, then even incase of first testcase we will
not get just 40 LIO for FILTER values.
May be algorithm is:
If a new probe key collides with an existing probe key, then the new probe
is saved in place of existing one.
Jurijs
"Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
29.06.2004 11:44
Please respond to oracle-l
To: <oracle-l@xxxxxxxxxxxxx>
cc:
Subject: Re: Physics of the FILTER operation within
SQL_PLANE.
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: <J.Velikanovs@xxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
