Function used in where clause - executed too many times

[Christian]

HI,
I have a function that is used to constrain a query:

Select COl1, Col2
From MyTable
WHERE col1 = ...
AND col2 = ...
And MyFunction(col1) = ...

My problem is that MyFunction is executed as many times that there are
rows in MyTable. I would like that it is being eecuted only when the
MyTable data has been filtered by two previous where conditions. This
way, MyFunction would be executed minimal times. So I did this:

Select a.*
From (Select COl1, Col2
From MyTable
WHERE col1 = ...
AND col2 = ...)
Where MyFunction(a.col1) =...
With no success. MyFunction is executed as many times as there are
rows into MyTable.

Is There a way to ensure that a function is being executed at the end
of the where clause, when the data is filtered by previous conditions
as much as possible?

Thank you for your help,
Christian

[sybrandb]

cc*******@yahoo.com (Christian) wrote in message news:<99**************************@posting.google. com>...

HI,
I have a function that is used to constrain a query:

Select COl1, Col2
From MyTable
WHERE col1 = ...
AND col2 = ...
And MyFunction(col1) = ...

My problem is that MyFunction is executed as many times that there are
rows in MyTable. I would like that it is being eecuted only when the
MyTable data has been filtered by two previous where conditions. This
way, MyFunction would be executed minimal times. So I did this:

Select a.*
From (Select COl1, Col2
From MyTable
WHERE col1 = ...
AND col2 = ...)
Where MyFunction(a.col1) =...
With no success. MyFunction is executed as many times as there are
rows into MyTable.

Is There a way to ensure that a function is being executed at the end
of the where clause, when the data is filtered by previous conditions
as much as possible?

Thank you for your help,
Christian

There is not. In Oracle all predicates will always be evaluated.
The only 'solution' is to use Function Based Indexes (Enterprise Edition required).
The best advice is to avoid functions with embedded selects in them like hell.

Sybrand Bakker,

Senior Oracle DBA

[KHURRAM]

cc*******@yahoo.com (Christian) wrote in message news:<99**************************@posting.google. com>...

HI,
I have a function that is used to constrain a query:

Select COl1, Col2
From MyTable
WHERE col1 = ...
AND col2 = ...
And MyFunction(col1) = ...

My problem is that MyFunction is executed as many times that there are
rows in MyTable. I would like that it is being eecuted only when the
MyTable data has been filtered by two previous where conditions. This
way, MyFunction would be executed minimal times. So I did this:

Select a.*
From (Select COl1, Col2
From MyTable
WHERE col1 = ...
AND col2 = ...)
Where MyFunction(a.col1) =...
With no success. MyFunction is executed as many times as there are
rows into MyTable.

Is There a way to ensure that a function is being executed at the end
of the where clause, when the data is filtered by previous conditions
as much as possible?

Thank you for your help,
Christian

Hi Christian,
Try this way..

Select a.*
From MyTable
Where (COl1,Col2) In (Select COl1, Col2
From MyTable
WHERE col1 = ...
AND col2 = ...
)
And MyFunction(a.col1) =...

I think it help you.

Thanks and Regards
Khurram Naseem

[Mark C. Stock]

<sy******@yahoo.com> wrote in message
news:a1**************************@posting.google.c om...
| cc*******@yahoo.com (Christian) wrote in message
news:<99**************************@posting.google. com>...
| > HI,
| > I have a function that is used to constrain a query:
| >
| > Select COl1, Col2
| > From MyTable
| > WHERE col1 = ...
| > AND col2 = ...
| > And MyFunction(col1) = ...
| >
| > My problem is that MyFunction is executed as many times that there are
| > rows in MyTable. I would like that it is being eecuted only when the
| > MyTable data has been filtered by two previous where conditions. This
| > way, MyFunction would be executed minimal times. So I did this:
| >
| > Select a.*
| > From (Select COl1, Col2
| > From MyTable
| > WHERE col1 = ...
| > AND col2 = ...)
| > Where MyFunction(a.col1) =...
| > With no success. MyFunction is executed as many times as there are
| > rows into MyTable.
| >
| > Is There a way to ensure that a function is being executed at the end
| > of the where clause, when the data is filtered by previous conditions
| > as much as possible?
| >
| > Thank you for your help,
| > Christian
|
| There is not. In Oracle all predicates will always be evaluated.
| The only 'solution' is to use Function Based Indexes (Enterprise Edition
required).
| The best advice is to avoid functions with embedded selects in them like
hell.
|
| Sybrand Bakker,
|
| Senior Oracle DBA
not that simple

in 8.1.7.0.0, for this query:

select ename
from emp
where deptno = 20
and fnc(ename,'test5') = 'TRUE'

the function generally got executed once per employee in department 20, not
once per row in the table -- but as long as i had and index on deptno, or
had statistics on the table

with no index on deptno and no statistics, the function got evaluated for
each row -- unless i reversed the order of the predicates, then the
optimizer only executed the function for each deptno 20 row

it also appears that the ORDERED_PREDICATES hint works to force evaluation
of predicates in WHERE-clause order, thus preventing extra comparisons
(there are limitations -- see the docs)

btw: a function based index is only good advise if the index is going to be
selective enough (bad idea to create an index that is never used, just adds
DML overhead)

i would suggest posting version info and the explain plan. you've got some
work to do with indexes or statistics or hints

;-{ mcs

[Ed prochak]

cc*******@yahoo.com (Christian) wrote in message news:<99**************************@posting.google. com>...

HI,
I have a function that is used to constrain a query:

Select COl1, Col2
From MyTable
WHERE col1 = ...
AND col2 = ...
And MyFunction(col1) = ...

My problem is that MyFunction is executed as many times that there are
rows in MyTable. I would like that it is being eecuted only when the
MyTable data has been filtered by two previous where conditions. [] Is There a way to ensure that a function is being executed at the end
of the where clause, when the data is filtered by previous conditions
as much as possible?

Thank you for your help,
Christian

The real question is why do you care?
What side effect are you trying to avoid?
There is always a side effect, even if it is only the response time of the query.

[dx]

cc*******@yahoo.com (Christian) wrote in message news:<99**************************@posting.google. com>...

HI,
I have a function that is used to constrain a query:

Select COl1, Col2
From MyTable
WHERE col1 = ...
AND col2 = ...
And MyFunction(col1) = ...

Suppose the original query is:

select col1, col2
from mytable
where col1 = 'abc' and col2 = 'def' and myfunction(col1) = 'xyz';

Suppose no appropriate index on col1 and/or col2 can be used, you can
use decode to filter like this:

select col1, col2
from mytable
where decode(col1, 'abc', decode(col2, 'def', myfunction(col1), null),
null) = 'xyz';

My problem is that MyFunction is executed as many times that there are
rows in MyTable. I would like that it is being eecuted only when the
MyTable data has been filtered by two previous where conditions. This
way, MyFunction would be executed minimal times. So I did this:

Select a.*
From (Select COl1, Col2
From MyTable
WHERE col1 = ...
AND col2 = ...)
Where MyFunction(a.col1) =...
With no success. MyFunction is executed as many times as there are
rows into MyTable.

Is There a way to ensure that a function is being executed at the end
of the where clause, when the data is filtered by previous conditions
as much as possible?

Thank you for your help,
Christian

[Douglas Hawthorne]

cc*******@yahoo.com (Christian) wrote in message news:<99**************************@posting.google. com>...

HI,
I have a function that is used to constrain a query:

Select COl1, Col2
From MyTable
WHERE col1 = ...
AND col2 = ...
And MyFunction(col1) = ...

My problem is that MyFunction is executed as many times that there are
rows in MyTable. I would like that it is being eecuted only when the
MyTable data has been filtered by two previous where conditions. This
way, MyFunction would be executed minimal times. So I did this:

Select a.*
From (Select COl1, Col2
From MyTable
WHERE col1 = ...
AND col2 = ...)
Where MyFunction(a.col1) =...
With no success. MyFunction is executed as many times as there are
rows into MyTable.

Is There a way to ensure that a function is being executed at the end
of the where clause, when the data is filtered by previous conditions
as much as possible?

Thank you for your help,
Christian

Christian,

In 10.1.0.2 on WinXP Pro, the function is only called for the number of rows
that pass filtering on the two prior predicates.

To demonstrate this, I shall the method outlined by Thomas Kyte in
"Effective Oracle by Design" (Oracle Press:2003) on p. 493. This method
uses the CLIENT_INFO functionality.

First, I create some test data with just two (2) columns. This creates a
table with 39,196 rows:
CREATE TABLE mytable AS
SELECT
MOD( rownum, 100 ) AS col1,
SUBSTR( object_name, 1, 1 ) AS col2
FROM
all_objects
;

Second, I coded the following function to record how many times this
function is called by incrementing the value in the CLIENT_INFO variable
(Yes I know I am relying on implicit type conversions!). The function
simply returns the parameter unchanged:

CREATE OR REPLACE FUNCTION myfunction
( p_col1 IN NUMBER )
RETURN NUMBER
AS
BEGIN
dbms_application_info.set_client_info(
USERENV( 'client_info' ) + 1
);
RETURN p_col1;
END;
/

Third, I count how many rows match the first two predicates:

SELECT
COUNT(*) match_count
FROM
mytable
WHERE
col1 >= 10
AND
col2 = 'j'
/

MATCH_COUNT
-----------
3056

Fourth, I run the whole query after setting the CLIENT_INFO variable to '0'.

exec dbms_application_info.set_client_info(0)

SELECT
COUNT(*) final_count
FROM
mytable
WHERE
col1 >= 10
AND
col2 = 'j'
AND
myfunction( col1 ) = 10
/

FINAL_COUNT
-----------
34

Lastly, I find how many times the function was called:

SELECT USERENV( 'client_info' ) num_calls FROM dual;

NUM_CALLS
----------------------------------------------------------------
3056

This count matches the count from the query using the first two predicates.
This is what you want.

Douglas Hawthorne