Re: Physics of the FILTER operation within SQL

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 30 Jun 2004 09:28:07 +0100
Message-ID: <008201c45e7c$2f052b00$7102a8c0@Primary>

Jurijs

Good point - I forgot to explain how I had discounted that theory and what I concluded instead.

First: if the 'new probe' replaced existing value, then you would have seen 1,018 rows in the probe lines of the plan where the 20 values were cycling instead of repeating - because the two colliding values would keep replacing each other.

So add in the mechanism:

Oracle also 'remembers', without putting it into the hash table, the most recent key value - and if the next row is the same key value then the last value can be re-used without revisiting the hash table. In fact, it is possible that Oracle only tries to put an item into the hash table if the next key value does not match the current key value.

In passing:

The hash table seems to be 256 entries for 9.2 and 1024 entries for 10.1; and the presence of the cost-based optimizer doesn't seem to make any different to the size of the table.

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_at_alise.lv> To: <oracle-l_at_freelists.org> Cc: <oracle-l_at_freelists.org>; <oracle-l-bounce_at_freelists.org> Sent: Tuesday, June 29, 2004 11:36 PM Subject: Re: Physics of the FILTER operation within SQL_PLANE.

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

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 Wed Jun 30 2004 - 03:24:56 CDT

Re: Physics of the FILTER operation within SQL_PLANE.