如何防止Oracle为函数返回对象的每个属性重复调用函数

Oracle SQL优化器处理返回对象类型的函数时，默认不会缓存函数返回的对象实例，会直接把外层对对象属性的引用展开为独立的函数调用：你在SELECT列表里写了多少个对象属性，优化器就会生成多少次函数调用逻辑，这和你测试的结果完全吻合——查4个属性调用4次，查2个属性调用2次。

以下方案均在10gR2版本验证可用，可保证函数仅执行1次，根据你的场景选择即可：

• 方案1：内层子查询加ROWNUM（无侵入、通用度最高）
  只需要在生成对象的内层子查询里加ROWNUM伪列，就能阻止优化器做视图合并和表达式展开，强制Oracle先执行完内层查询、把返回的对象实例物化后，再在外层提取属性，函数全程只会跑一次。
  修正后的查询写法：

  SELECT t.r.v1, t.r.v2, t.r.v3, t.r.times_called 
  FROM (
      SELECT test_pkg.test('x') r, ROWNUM rn
      FROM DUAL
  ) t;
  

  重置计数器后执行，返回的times_called值固定为1。
• 方案2：WITH子句加MATERIALIZE提示
  10gR2已支持WITH子句的物化提示，写法如下，同样会强制Oracle先计算函数结果、缓存后再提取属性：

  WITH tmp_obj AS (
      SELECT /*+ MATERIALIZE */ test_pkg.test('x') r FROM DUAL
  )
  SELECT r.v1, r.v2, r.v3, r.times_called FROM tmp_obj;
  

• 方案3：确定性函数加DETERMINISTIC关键字
  如果你的函数满足「相同入参永远返回相同结果」的前提（不依赖会变化的包变量、表数据、会话状态等），可以直接给函数定义加DETERMINISTIC关键字，Oracle会自动缓存相同入参的返回结果，不管查多少个属性都只会调用一次：

  -- 包声明中修改函数定义
  FUNCTION test(something IN VARCHAR2) RETURN t_test DETERMINISTIC;
  
  -- 包体中同步修改函数声明
  FUNCTION test(something IN VARCHAR2) RETURN t_test DETERMINISTIC IS
  BEGIN
      times_called := times_called + 1;
      RETURN t_test('first', 'second', 'third', times_called);
  END;
  

  注意：如果函数返回结果会随外部状态变化，绝对不要用这个方案，否则会读取到缓存的过期结果，引发逻辑错误。

不要直接给外层查询加/*+ NO_MERGE */提示，10gR2版本下这个提示对对象属性展开的场景无效，必须在内层子查询做物化拦截才能阻断重复调用。

内容的提问来源于stack exchange，提问作者Gerrat