RE: Physics of the FILTER operation within SQL

From: <J.Velikanovs_at_alise.lv>
Date: Wed, 30 Jun 2004 12:32:32 +0300
Message-ID: <OFAC373D00.BE7E134A-ONC2256EC3.00340C85-C2256EC3.0035178C@alise.lv>

Lex,
Thank for comment,
I use RBO just for proof of concept.
I am sure that the same situation can be emulated with CBO, but in this case it was easy for me to use RBO.

>> about the optimizer not being able to calculate differences
Even if would use CBO it will not be able to calculate difference in case of FILTER operation, was my point.

Jurijs

"Lex de Haan" <lex.de.haan_at_naturaljoin.nl> Sent by: oracle-l-bounce_at_freelists.org
30.06.2004 12:30
Please respond to oracle-l

        To:     <oracle-l_at_freelists.org>
        cc: 
        Subject:        RE: Physics of the FILTER operation within

SQL_PLANE. Hi Jurijs,
allow me one comment about your statement below, about the optimizer not being able to calculate differences:

you are forcing RBO behavior, a technology now obsolete in 10g and untouched by Oracle development for many, many years...

(interesting email thread, by the way)

Kind regards,
Lex.

visit my website at http://www.naturaljoin.nl

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of J.Velikanovs_at_alise.lv Sent: Wednesday, June 30, 2004 10:02
To: oracle-l_at_freelists.org
Subject: Re: Physics of the FILTER operation within SQL_PLANE.

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.

Jurijs

TESTCACE 1

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%')

TESTCACE 2

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%')

Binary/unsupported file stripped by Ecartis --
Type: text/x-vcard
File: Lex de Haan.vcf

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 Wed Jun 30 2004 - 04:36:35 CDT

RE: Physics of the FILTER operation within SQL_PLANE.