Re: External function and performance

In article <a7li0v$jih$1_at_news.tpi.pl>, Krzysztof says...
>
>Thomas Kyte wrote:
>
>>
>> I do not observe 10x, I see a hit but not 10x
>>
>
>Right, I've checked it once again - 4 times, not 10 - my fault, sorry.
>But it is still much to slow.
>Additionally, I noticed that external function used in WHERE clause works
>even worse.
>For instance:
>
>SELECT descr FROM table WHERE substr(descr,1,1)='N'
>
>is much faster (more than 10 times and I belive it depends on number of
>rows)
>

three words -- function based indexes:

http://osi.oracle.com/~tkyte/article1/index.html

and then: select descr from table where check_example( descr ) = -1

will be really fast (<> being not a good thing for indexes of course. have your function return a "positive" value for a hit like -1 or +1 instead of just "any number other than zero)

>SELECT descr FROM table WHERE check_example(descr)<>0
>
>where check_example function just checks whether first leter of parameter
>value is 'N'.
>
>> The code path is longer -- much so. You are going from SQL, outside to
>> another process -- and back again.
>>
>
>Is there any way to write a function and make it perform as fast as
>built-in functions? Maybe Java?
>
>I would appreciate any suggestion.
>

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp