Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Using function slows query. How can I ensure it is called only once?

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

From: Romeo Olympia <rolympia_at_hotmail.com>
Date: 31 Aug 2004 20:01:42 -0700
Message-ID: <42fc55dc.0408311901.215d007e@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US