User defined functions as columns in select
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: User defined functions as columns in select

  1. #1
    Join Date
    Mar 2002
    Posts
    14

    Exclamation

    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.

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  3. #3
    Join Date
    Mar 2002
    Posts
    14
    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!

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width