Re: How does the cost based optimiser evaluate in-line functions

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 28 Oct 1999 08:42:54 -0400
Message-ID: <j0EYOEYM41lihVM9TcGLKkWI+61J@4ax.com>

A copy of this was sent to "Rod Stewart" <rod.stewart_at_afp.gov.au> (if that email address didn't require changing) On Thu, 28 Oct 1999 15:31:52 +1000, you wrote:

>Colleagues,
>
>I have a query that looks something like this:
>
>1) select count(*) from blah where
>2) and col_a = 39
>3) and col_b is null
>4) (security_admin.some_function(param1, user)) > 0
>
>table blah has 432 records in it, line 2 reduced the result set to 280
>records, line 3 to 21 records. The combination of lines 2 and 3 reduced the
>result set to 4. I had a general understanding that the cost based
>optimiser would basically work from top to bottom and so I hoped to reduce
>the result set as much as possible before having line 4, the more costly
>security checking function, apply to the result set. As it turns out, it
>didn't work that way. I put a trace on the query and found that the
>security function was in fact being executed 280 times. So I moved line 4
>to line 2 and now it flies, that is, it takes 60ms instead of the three
>seconds it took originally.
>
>So, my question is, how does the cost based optimiser determine its
>execution path, particulary in relation to in-line functions as I have here?
>

it was the RBO that would tend to evaluate from the bottom up.

If I wanted to influence the order of predicate evaluation, i would use an inline view. For example -- if I wrote

select count(*)
  from ( select *

           from blah
          where col_a = 39
            and col_b is null
            and rownum > 0 )

 where security_admin.some_function( param1, user ) > 0 /

using the rownum predicate prevents us from pushing the predicate "where security_admin.some_function() > 0" into the inline view -- we must resolve the inline view and then apply.

>Any ideas would be appreciated.
>
>Rod J. Stewart
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Oct 28 1999 - 07:42:54 CDT