Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Query Question - Fetches

Re: Query Question - Fetches

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Wed, 17 Nov 2004 20:53:16 -0000
Message-ID: <419bba3c$0$16437$cc9e4d1f@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
Received on Wed Nov 17 2004 - 14:53:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US