Re: Query Question - Fetches

> I'd like to see the figures that demonstrate this.

In our case, we use some custom functions to avoid hard-coding some values. GetTranslationValue being an example, we replaced it with the hard coded literal of 4. The query used in this where clause went from 8 minutes to 5 seconds. The table has 530034 rows in it. The table within the function is only 100 rows so it is small. The SQLServer optimizer is able to handle this more efficiently with the inline function in place. SQLServer apparently recognizes that it only needs to call this function once instead of half a million. WHERE Action = GetTranslationEnum('Sign')

WHERE Action = 4

FUNCTION GetTranslationEnum(vcInternalValue in varchar2) RETURN varchar2 AS

vcValue varchar2(50);
BEGIN SELECT *
INTO vcValue
FROM Tbltranslations
WHERE upper(InternalValue) = upper(vcInternalValue);

RETURN(vcValue );

END GetTranslationEnum;

-- 


----------------------------------------------------
This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc. http://info.mailfrontier.com

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
news:419bba3c$0$16437$cc9e4d1f_at_news-text.dial.pipex.com...


> "KurtisK" <KJKYLE_at_COOLBLUENOSPAM.COM> wrote in message

> news:WuidnZJcV6rlNQbcRVn-vQ_at_telcove.net...

> > Ok, so the question should be asked why do inline function perform so

> > poorly

> > with Oracle.  I've encountered this problem.  In our case, it was often

> > *simple* user-defined functions used within the WHERE criteria.  (  as

> > opposed to built-in Oracle functions like RTRIM )

> >   SQLServer handled these

> > same functions without a performance problem, Oracle did not.

>


> I'd like to see the figures that demonstrate this.

>


> > It seems you just need to be careful when using inline function with

your


> > query statements, particularly in the WHERE clause.

>


> That is true, especially when the ideal path to the data is some sort of

> indexed access path. It is remarkably easy to preclude the use of a normal

> index by applying a function (including RTRIM or SUBSTR etc) to a column.

In


> Oracle the solution to this problem is not to index the column, but to

index


> the result of the function. our original post didn't include any SQL

> statement at all, or any explain plan output so we can't tell for sure

what


> was happening, but the most common reason for a change of the magnitude in

> elapsed time that is shown would be that the access path changed as a

result


> of the function.

>


> It is probably also worth stating that applying functions to data is not a

> zero-cost operation. There is a discussion of some relevance at

> http://www.oracle.com/technology/pub/articles/lewis_cbo.html (the article

is


> about system statistics, but the point that doing more operations takes

> longer is well made in the discussion about predicate order).

>
>


> -- 

> Niall Litchfield

> Oracle DBA

> http://www.niall.litchfield.dial.pipex.com

>
>