Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Physics of the FILTER operation within SQL_PLANE.

Re: Physics of the FILTER operation within SQL_PLANE.

From: Jonathan Lewis <>
Date: Wed, 30 Jun 2004 11:36:28 +0100
Message-ID: <010e01c45e8e$1d7d89b0$7102a8c0@Primary>

You have to be careful with the word "cost".

In discussions like these, I try to stick with 'resource usage' as a description of how much work is done and 'cost' as the value predicted by the optimizer.

You are quite correct - there are many cases where the optimizer works out a cost by making some assumptions about the data distribution - but the actual resource usage can vary greatly for the same data 'content' if it is arranged in a different order.

I have just built a test case based on your test 1 test 2 (repeating the value vs. cycling the value) where the execution plan is identical, the cost is identical, the actual number of logical I/Os is identical - and the only difference is the CPU usage. Given the exact structure of the test, I think this confirms my hypothesis that Oracle remembers the last join key and value rather then visiting the hash table every time.


Jonathan Lewis The Co-operative Oracle Users' FAQ Optimising Oracle Seminar - schedule updated May 1st

If your hypnotize is true (regerding remembering last value), then I have one more interesting conclusion:
The cost of the same SQL with the same execution plans can differ depending on rows ordering in the driving rowset. Like with index range scan and clustering factor, but with one little difference - Oracle optimizer in case of FILTER operation can't evaluate this cost (in case of range scan - clustering factor statistics available).
Even more, if your hypnotize is true, then cost difference can appears not from bigger LIO count, but also because in one case Oracle need to manage hash table in other doesn't (just use last remembered value). For my point demonstrating purpose, I have a little bit modified my initial testcase (1,2).
Take a look: the same SQL, the same ExplPlan, time differs by 18% and Oracle optimizer can't caltulate this difference.


Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Wed Jun 30 2004 - 05:33:14 CDT

Original text of this message