User defined functions as columns in select

[Ed Sudit]

We are experiencing a serious problem with execution time of a query that is using an IN clause criteria on a column in a view that is defined as a stored function in the base select of the view. When we convert the IN clause to a series of OR clauses the query runs pretty quickly. When we change the OR clauses to the IN operator, the query execution time is being reported by Oracle as 18 Hours!!!

The system is an Oracle 8i (8.1.6) based data warehouse running on NT 4.0 Server.

[Shestakov]

Did u try to create function-based index, that use ur stored function in a base select
statment.
May be it help to this query.

[Ed Sudit]

The function based index may not help out in this situation. Let me give you more details on this by posting some sample code that represents our situation here:

Create or Replace View TestView AS
Select user_function() as uf1, data_column2
From TestTable
Where data_column2 = '9999';

Select * from TestView
Where uf1 in ('A', 'B', 'C');

The above query results in an 18 Hour execution time.

Select * from TestView
Where uf1 = 'A' OR uf1 = 'B' OR uf1 = 'C';

The above query results in a 20 minute execution time!

The Explain Plan for both queries is identical and so is the cost!

[Shestakov]

Did u decompoced view:

select * from
(Select user_function() as uf1, data_column2
From TestTable
Where data_column2 = '9999') v
where v.uf1 in (...);

and compared execution plans