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.co l1) =...
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@yahoo.com]

Re: Function used in where clause - executed too many times

ccote_msl@yahoo .com (Christian) wrote in message news:<992a9b5a. 0404201837.3689 d0b5@posting.go ogle.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.co l1) =...
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]

Re: Function used in where clause - executed too many times

ccote_msl@yahoo .com (Christian) wrote in message news:<992a9b5a. 0404201837.3689 d0b5@posting.go ogle.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.co l1) =...
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.co l1) =...

I think it help you.

Thanks and Regards
Khurram Naseem

[Ed prochak]

Re: Function used in where clause - executed too many times

ccote_msl@yahoo .com (Christian) wrote in message news:<992a9b5a. 0404201837.3689 d0b5@posting.go ogle.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]

Re: Function used in where clause - executed too many times

ccote_msl@yahoo .com (Christian) wrote in message news:<992a9b5a. 0404201837.3689 d0b5@posting.go ogle.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.co l1) =...
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]

Re: Function used in where clause - executed too many times

ccote_msl@yahoo .com (Christian) wrote in message news:<992a9b5a. 0404201837.3689 d0b5@posting.go ogle.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.co l1) =...
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_applicatio n_info.set_clie nt_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_applicatio n_info.set_clie nt_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