Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137

    Unanswered: About tuning and calling some user functions

    Talking about tuning and calling some user functions, which is better?

    Code:
    select *
      from table1 t1
     where t1.mydate between trunc(myfunction.getdate -30)
                         and trunc(myfunction.getdate)
    Or:

    Code:
    select *
      from table1 t1
         , (select trunc(myfunction.getdate) mydate from dual) t2
     where t1.mydate between (t2.mydate -30)
                         and t2.mydate
    This is part of a view so forget about binding a variable.

    Is it better to call the function from a select statement or just leave it in the where clause?
    I couldn't find an answer for this.

    Thanks,
    DKG.

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    There probably isn't one simple answer to this.

    AFAIK 10g R2 is the first release to optimize deterministic functions by caching and reusing the results of previous calls to them with identical arguments. Therefore if this is 10g and the function is created DETERMINISTIC, it may not matter which construction you use.

    Also, placing the function call in the inline view seems neater but the optimizer may choose to merge it, and the actual execution plan could end up with multiple copies of the expression.

  3. #3
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    Thanks, WilliamR!!

    I made some tests using "dbms_application_info.set_client_info();" as counter inside myfunction and it turns out that in both cases the function is being invoked exactly the same amount of times.

    I'm still working with 9.2, it seems that until 10g R2 there's no better way to do it.

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Deterministic functions has been on Oracle since 8i and up, if not earlier.

    DKG, did you created the function with the DETERMINISTIC hint on it ? I want to see some example how did you do it, if you can.

    It is probable because of the returned data, which might or not be deterministic. If you want to avoid repetitive calls, make sure the function returns deterministic results and create a function index on it. That way it will probably never be called and the optimizer will know its results once you call it.

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Yes, DETERMINISTIC has been around for a while but the optimisation feature did not arrive until 10g.

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Where did you read about this new optimization in 10g ?

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I thought it was on AskTom somewhere but I can't find it. All I could find was this.

  8. #8
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    It seems that DETERMINISTIC functions were created to use function based indexes, and only in 10g R2 this feature is being used as a cache by the optimizer.

    JMartinez, Do you think it would be a good idea to create a function based index on a date field?
    I'm not sure that would help that much.

  9. #9
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    WilliamR, you're right on the spot. You can find a link here about what Tom said. I still can't find any Oracle documentation backing up this.

    Thank you for the heads up!

Posting Permissions

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