How does the cost based optimiser evaluate in-line functions

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?

Any ideas would be appreciated.

Rod J. Stewart Received on Thu Oct 28 1999 - 00:31:52 CDT