问题概述

SQL语句中如果有自定义函数，可能会引起SQL性能问题，自定义函数可能会出现在SELECT和FROM之间，也可能出现在WHERE条件中，不同的写法优化，优化思路不一样

问题原因

自定义函数引起SQL性能缓慢的原因是：SQL每返回一行数据，就会调用一次函数代码，如果查询结果据量太大，自定义函数就会严重影响SQL性能。

解决方案

方法一：改写SQL，将自定义函数中的SQL提取出来，改写为表连接 方法二：如果方法一比较麻烦，设法减少自定义函数的调用次数

优化案例

下面SQL要跑47秒(SQL已做脱敏处理，最原始SQL要跑几十分钟，添加HINT修正执行计划后跑47秒) 54316 rows inserted Executed in 47.031 seconds

INSERT INTO TMP
  SELECT 1,
         ORG_ID,
         SESSION_ID,
         NULL,
         NULL,
         NULL,
         DEPARTMENT_CODE,
         NULL,
         DEPARTMENT_NAME,
         NULL,
         SALES_PERSON,
         NULL,
         PROJECT_NUMBER,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         ITEM_CATEGORY,
         ITEM_CATEGORY_DESC,
         NULL,
         SO_TYPE,
         SO_AREA,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         SO_COST,
         INTERNAL_SO_COST,
         SO_COST_DIFFERENCE,
         NULL,
         EXPENSE_AMOUNT,
         NULL,
         NULL,
         NULL,
         LOT_NUMBER,
         SO_HEADER_ID,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         SEGMENT1,
         SEGMENT2,
         NULL,
         NULL,
         SEGMENT5,
         SEGMENT6,
         SEGMENT7,
         NULL,
         ITEM_TYPE,
         CREATION_DATE,
         CREATED_BY,
         LAST_UPDATED_BY,
         LAST_UPDATE_DATE,
         LAST_UPDATE_LOGIN,
         NULL,
         ATTRIBUTE1,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         ATTRIBUTE15,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         SO_NUMBER,
         SO_LINE_NUM,
         NULL,
         NULL,
         NULL,
         NULL,
         INV_ITEM_DESCRIPTION,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         INV_ITEM_NUMBER,
         RA_TRX_TYPE_NAME,
         NULL
    FROM (SELECT /*+ leading(hou) no_merge(hou) full(nh)  */
           (SELECT HOU.ORGANIZATION_ID
              FROM HOU, FSP
             WHERE HOU.ORGANIZATION_ID = FSP.ORG_ID
               AND FSP.INVENTORY_ORGANIZATION_ID = NH.INV_ORG_ID
               AND ROWNUM = 1) ORG_ID,
           GCC.SEGMENT2 ATTRIBUTE15,
           GCC.SEGMENT6 ITEM_CATEGORY,
           (SELECT T.DESCRIPTION
              FROM T, B
             WHERE B.FLEX_VALUE_ID = T.FLEX_VALUE_ID
               AND T.LANGUAGE = 'US'
               AND B.FLEX_VALUE_SET_ID = 1014879
               AND B.FLEX_VALUE = GCC.SEGMENT6) ITEM_CATEGORY_DESC,
           GCC.SEGMENT2 DEPARTMENT_CODE,
           (SELECT FFV.DESCRIPTION
              FROM FFV
             WHERE FFV.FLEX_VALUE_SET_ID = 1014875
               AND FFV.FLEX_VALUE = GCC.SEGMENT2) DEPARTMENT_NAME,
           GCC.SEGMENT7 PROJECT_NUMBER,
           SUM(NVL(NH.UPDATE_AMOUNT, 0)) SO_COST,
           DECODE(GCC.SEGMENT5, '0', NULL, SUM(NVL(NH.UPDATE_AMOUNT, 0))) INTERNAL_SO_COST,
           GCC.SEGMENT1,
           GCC.SEGMENT2,
           GCC.SEGMENT5,
           GCC.SEGMENT6,
           GCC.SEGMENT7,
           SYSDATE CREATION_DATE,
           FND_GLOBAL.USER_ID CREATED_BY,
           FND_GLOBAL.USER_ID LAST_UPDATED_BY,
           SYSDATE LAST_UPDATE_DATE,
           FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN,
           0 EXPENSE_AMOUNT,