Re: External function and performance

On Sun, 24 Mar 2002 22:55:29 +0000, Krzysztof Ku¶nierz <kkrzysiek_at_supra.com.pl> wrote:

>Thomas Kyte wrote:
>
>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)
>
>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.

Yes you can write the function in PL/SQL. This will cut down on the time spent translating descr into an external string format, and the external call processing. As of 8i you can also write functions in Java and load them into the database. These get compiled in local code (not byte-code). Oracle uses internal formats that are portable, but not native to most if any hardware platforms.

You should probably code your function within a package. This allows you to change the implementation without invalidating any calling objects. Calling objects do get called when the package interface changes so split the interface definition and implementation into separate source files and only load the interface if it changes.

Bill Received on Mon Mar 25 2002 - 01:56:25 CET