Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Using function slows query. How can I ensure it is called only once?
Do try using the ROWNUM > 0 trick to see if we could "materialize" the
inline view. Something like:
SELECT serialNumber, MAX(hours)
FROM workorder,
(SELECT util.getStartDate() sDate,
util.getEndDate() eDate
FROM dual
WHERE rownum > 0) range
WHERE workOrderDate >= range.sDate
AND workOrderDate <= range.eDate
-- missing GROUP BY here?
chambersdon_at_hotmail.com (Don) wrote in message news:<4efd4f65.0408310921.78bdca31_at_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 - 22:01:42 CDT