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: Michel Cadot <micadot{at}altern{dot}org>
Date: Tue, 31 Aug 2004 22:08:56 +0200
Message-ID: <4134da73$0$22030$626a14ce@news.free.fr>

"Don" <chambersdon_at_hotmail.com> a écrit dans le message de 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

Try:
SELECT /*+ NO_MERGE(range) */ serialNumber, MAX(hours)  FROM workorder,

    (SELECT util.getStartDate() sDate,

            util.getEndDate() eDate
      FROM dual) range

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

I think your inline query is merged into the main query and then the functions are call once for each row.

-- 
Regards
Michel Cadot
Received on Tue Aug 31 2004 - 15:08:56 CDT

Original text of this message

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