Using function slows query. How can I ensure it is called only once?

From: Don <chambersdon_at_hotmail.com>
Date: 31 Aug 2004 10:21:01 -0700
Message-ID: <4efd4f65.0408310921.78bdca31@posting.google.com>

I have a query that returns in less than a second when using bind variables but when these bind variables are replaced with a function call it takes well over 30 seconds to return. When the function is called alone in returns in less than a second. It seems that the function is called several times (once for each row?) even when I put it in an inline view.

I have the following query that retrieves data within a certain data range.
SELECT serialNumber, MAX(hours)
 FROM workorder
 WHERE workOrderDate >= :startDate
   AND workOrderDate <= :endDate

The workorder table has just over 1 million rows and this query takes less than 1 second. The rules for determining the start and end dates are complex so I created a package that includes functions for getting these dates. I re-wrote the query as follows: SELECT serialNumber, MAX(hours)
 FROM workorder
 WHERE workOrderDate >= util.getStartDate()    AND workOrderDate <= util.getEndDate()

This returns the proper data but now the query takes a lot longer -
>30 seconds. I assumed this is due to the overhead on the function
being called on every row so I rewrote the query to use an inline view, as follows:
SELECT serialNumber, MAX(hours)
 FROM workorder,
   (SELECT util.getStartDate() sDate,

           util.getEndDate() eDate
     FROM dual) range

 WHERE workOrderDate >= range.sDate
   AND workOrderDate <= range.eDate;

It still takes over 30 seconds to return.

I thought using an inline view would mean that my function is only called once. I altered the function to output a number selected from a sequence and I get many, many outputs. I got over 86,000 when I received a buffer overflow.

How can I improve response time for this query? This query will be used by another tool so I must have only one query.  It can be a complex query but I cannot use PL/SQL.

Is there a way to ensure that my function is called only once per query?

Thanks,

   Don Received on Tue Aug 31 2004 - 12:21:01 CDT