where条件中有权限校验的自定义函数优化方法

已于 2022-10-20 11:32:05 修改
阅读量438 收藏
点赞数
分类专栏： SQL TUNING 文章标签： sql mysql 数据库
于 2022-10-20 11:25:46 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/robinson1988/article/details/127423530
版权

下面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,
           OOH.ORDER_NUMBER SO_NUMBER,
           -1 * SUM(NVL(NH.UPDATE_AMOUNT, 0)) SO_COST_DIFFERENCE,
           '成本更新' LOT_NUMBER,
           (SELECT NVL(B.ATTRIBUTE4, B.ATTRIBUTE2)
              FROM T, B, HCA
             WHERE B.FLEX_VALUE_ID = T.FLEX_VALUE_ID
               AND T.LANGUAGE = 'US'
               AND B.FLEX_VALUE_SET_ID = 1014957
               AND B.FLEX_VALUE = HCA.ATTRIBUTE11
               AND OOL.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID
               AND ROWNUM = 1) SO_AREA,
           (SELECT JRSE.RESOURCE_NAME
              FROM JRS, JRSE
             WHERE JRS.SALESREP_ID = OOH.SALESREP_ID
               AND JRS.RESOURCE_ID = JRSE.RESOURCE_ID
               AND JRSE.LANGUAGE = 'US'
               AND ROWNUM = 1) SALES_PERSON,
           NULL SO_TYPE,
           (SELECT OTT.DESCRIPTION
              FROM OTT
             WHERE OOH.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID
               AND OTT.LANGUAGE = USERENV('LANG')
               AND ROWNUM = 1) RA_TRX_TYPE_NAME,
           (SELECT MSIB.DESCRIPTION
              FROM MSIB
             WHERE OOL.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
               AND OOL.SHIP_FROM_ORG_ID = MSIB.ORGANIZATION_ID
               AND ROWNUM = 1) INV_ITEM_DESCRIPTION,
           OOL.ORDERED_ITEM INV_ITEM_NUMBER,
           (SELECT MDEV.ELEMENT_VALUE
              FROM MSIB, MDEV
             WHERE OOL.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
               AND OOL.SHIP_FROM_ORG_ID = MSIB.ORGANIZATION_ID
               AND MDEV.ELEMENT_NAME(+) = '商品简称'
               AND MDEV.INVENTORY_ITEM_ID(+) = MSIB.INVENTORY_ITEM_ID
               AND ROWNUM = 1) ITEM_TYPE,
           (SELECT PAF.PER_NAME
              FROM PAF, JR
             WHERE JR.PERSON_ID = PAF.PERSON_ID
               AND NVL(OOH.ATTRIBUTE3, OOH.SALESREP_ID) = JR.SALESREP_ID(+)
               AND OOH.ORG_ID = JR.ORG_ID(+)
               AND ROWNUM = 1) ATTRIBUTE1,
           OOL.LINE_NUMBER || '.' || OOL.SHIPMENT_NUMBER SO_LINE_NUM,
           OOH.HEADER_ID SO_HEADER_ID,
           null SESSION_ID
            FROM NH, GCC, OOL, OOH, FLV, HOU
           WHERE NH.UPDATE_CCID = GCC.CODE_COMBINATION_ID
             AND OOL.LINE_ID = NH.SO_LINE_ID
             AND NH.SO_HEADER_ID = OOL.HEADER_ID
             AND OOL.HEADER_ID = OOH.HEADER_ID
             AND GCC.SEGMENT2 > '0'
             AND GCC.SEGMENT3 >= FLV.MEANING
             AND GCC.SEGMENT3 <= FLV.DESCRIPTION
             AND DECODE(null, NULL, '-1', GCC.SEGMENT7) =
                 DECODE(null, NULL, '-1', null)
             AND FLV.LOOKUP_TYPE = 'XY_AR_COST_SUBJECT'
             AND NVL(FLV.ENABLED_FLAG, 'N') = 'Y'
             AND SYSDATE BETWEEN NVL(FLV.START_DATE_ACTIVE, SYSDATE - 1) AND
                 NVL(FLV.END_DATE_ACTIVE, SYSDATE + 1)
             AND GCC.SUMMARY_FLAG = 'N'
             AND NH.GL_IF_FLAG = 'OK'
             AND HOU.SHORT_CODE = GCC.SEGMENT1
             AND HOU.SET_OF_BOOKS_ID = 2022
             AND (GCC.SEGMENT1 = null OR null IS NULL)
             AND NH.ATTRIBUTE2 >= '2022-01'
             AND NH.ATTRIBUTE2 <= '2022-01'
             AND (NH.INV_ORG_ID = null OR null IS NULL)
             AND OOH.ORG_ID = HOU.ORGANIZATION_ID
             AND OOL.ORG_ID = HOU.ORGANIZATION_ID
             AND XY_COM_DEPT_READ.COM_READ(OOH.ORG_ID) = 'Y'
             AND (OOH.ORG_ID = null OR null IS NULL)
             AND (OOL.ORG_ID = null OR null IS NULL)
           GROUP BY NH.INV_ORG_ID,
                    GCC.SEGMENT2,
                    GCC.SEGMENT6,
                    GCC.SEGMENT7,
                    GCC.SEGMENT1,
                    GCC.SEGMENT5,
                    OOH.ORDER_NUMBER,
                    OOL.SOLD_TO_ORG_ID,
                    OOH.SALESREP_ID,
                    OOH.ORDER_TYPE_ID,
                    OOL.INVENTORY_ITEM_ID,
                    OOL.SHIP_FROM_ORG_ID,
                    OOL.ORDERED_ITEM,
                    NVL(OOH.ATTRIBUTE3, OOH.SALESREP_ID),
                    OOH.ORG_ID,
                    OOL.LINE_NUMBER || '.' || OOL.SHIPMENT_NUMBER,
                    OOH.HEADER_ID)

执行计划比较长就不贴了，这是一条INSERT INTO…SELECT…GROUP BY语句
一共要插入54316行数据，GROUP BY之前有2685697条数据 

SQL> SELECT /*+ leading(hou) no_merge(hou) full(nh)  */
  2                count(*)
  3                     FROM  NH,
  4                           GCC,
  5                           OOL,
  6                           OOH,
  7                           FLV,
  8                           HOU
  9                    WHERE NH.UPDATE_CCID = GCC.CODE_COMBINATION_ID
 10                      AND OOL.LINE_ID = NH.SO_LINE_ID
 11                      AND NH.SO_HEADER_ID = OOL.HEADER_ID
 12                      AND OOL.HEADER_ID = OOH.HEADER_ID
 13                      AND GCC.SEGMENT2 > '0'
 14                      AND GCC.SEGMENT3 >= FLV.MEANING
 15                      AND GCC.SEGMENT3 <= FLV.DESCRIPTION
 16                      AND DECODE(null, NULL, '-1', GCC.SEGMENT7) =
 17                          DECODE(null, NULL, '-1', null)
 18                      AND FLV.LOOKUP_TYPE = 'XY_AR_COST_SUBJECT'
 19                      AND NVL(FLV.ENABLED_FLAG, 'N') = 'Y'
 20                      AND SYSDATE BETWEEN
 21                          NVL(FLV.START_DATE_ACTIVE, SYSDATE - 1) AND
 22                          NVL(FLV.END_DATE_ACTIVE, SYSDATE + 1)
 23                      AND GCC.SUMMARY_FLAG = 'N'
 24                      AND NH.GL_IF_FLAG = 'OK'
 25                      AND HOU.SHORT_CODE = GCC.SEGMENT1
 26                      AND HOU.SET_OF_BOOKS_ID = 2022
 27                      AND (GCC.SEGMENT1 = null OR null IS NULL)
 28                      AND NH.ATTRIBUTE2 >= '2022-01'
 29                      AND NH.ATTRIBUTE2 <= '2022-01'
 30                      AND (NH.INV_ORG_ID = null OR null IS NULL)
 31                      AND OOH.ORG_ID = HOU.ORGANIZATION_ID
 32                      AND OOL.ORG_ID = HOU.ORGANIZATION_ID
 33                      AND XY_COM_DEPT_READ.COM_READ(OOH.ORG_ID) = 'Y'
 34                      AND (OOH.ORG_ID = null OR null IS NULL)
 35                      AND (OOL.ORG_ID = null OR null IS NULL)
 36  ;

SQL> 
  COUNT(*)
----------
   2685697
Executed in 40.204 seconds

SQL语句中的where条件部分有个自定义函数，这个自定义函数是用来做权限校验的
XY_COM_DEPT_READ.COM_READ(OOH.ORG_ID) = 'Y'
去掉自定义函数看一下速度

SQL> SELECT /*+ leading(hou) no_merge(hou) full(nh)  */
  2                count(*)
  3                     FROM  NH,
  4                           GCC,
  5                           OOL,
  6                           OOH,
  7                           FLV,
  8                           HOU
  9                    WHERE NH.UPDATE_CCID = GCC.CODE_COMBINATION_ID
 10                      AND OOL.LINE_ID = NH.SO_LINE_ID
 11                      AND NH.SO_HEADER_ID = OOL.HEADER_ID
 12                      AND OOL.HEADER_ID = OOH.HEADER_ID
 13                      AND GCC.SEGMENT2 > '0'
 14                      AND GCC.SEGMENT3 >= FLV.MEANING
 15                      AND GCC.SEGMENT3 <= FLV.DESCRIPTION
 16                      AND DECODE(null, NULL, '-1', GCC.SEGMENT7) =
 17                          DECODE(null, NULL, '-1', null)
 18                      AND FLV.LOOKUP_TYPE = 'XY_AR_COST_SUBJECT'
 19                      AND NVL(FLV.ENABLED_FLAG, 'N') = 'Y'
 20                      AND SYSDATE BETWEEN
 21                          NVL(FLV.START_DATE_ACTIVE, SYSDATE - 1) AND
 22                          NVL(FLV.END_DATE_ACTIVE, SYSDATE + 1)
 23                      AND GCC.SUMMARY_FLAG = 'N'
 24                      AND NH.GL_IF_FLAG = 'OK'
 25                      AND HOU.SHORT_CODE = GCC.SEGMENT1
 26                      AND HOU.SET_OF_BOOKS_ID = 2022
 27                      AND (GCC.SEGMENT1 = null OR null IS NULL)
 28                      AND NH.ATTRIBUTE2 >= '2022-01'
 29                      AND NH.ATTRIBUTE2 <= '2022-01'
 30                      AND (NH.INV_ORG_ID = null OR null IS NULL)
 31                      AND OOH.ORG_ID = HOU.ORGANIZATION_ID
 32                      AND OOL.ORG_ID = HOU.ORGANIZATION_ID
 33                      --AND XY_COM_DEPT_READ.COM_READ(OOH.ORG_ID) = 'Y'
 34                      AND (OOH.ORG_ID = null OR null IS NULL)
 35                      AND (OOL.ORG_ID = null OR null IS NULL)
 36  ;

  COUNT(*)
----------
   2685697
Executed in 10.853 seconds

不加自定义函数10秒跑完，加了40秒
因为原始SQL有GROUP BY，而且GROUP BY的列有OOH.ORG_ID，所以可以将
XY_COM_DEPT_READ.COM_READ(OOH.ORG_ID) = 'Y’放到GROUP BY之后再过滤
于是原始SQL可以改写为：

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,
           OOH.ORDER_NUMBER SO_NUMBER,
           -1 * SUM(NVL(NH.UPDATE_AMOUNT, 0)) SO_COST_DIFFERENCE,
           '成本更新' LOT_NUMBER,
           (SELECT NVL(B.ATTRIBUTE4, B.ATTRIBUTE2)
              FROM T, B, HCA
             WHERE B.FLEX_VALUE_ID = T.FLEX_VALUE_ID
               AND T.LANGUAGE = 'US'
               AND B.FLEX_VALUE_SET_ID = 1014957
               AND B.FLEX_VALUE = HCA.ATTRIBUTE11
               AND OOL.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID
               AND ROWNUM = 1) SO_AREA,
           (SELECT JRSE.RESOURCE_NAME
              FROM JRS, JRSE
             WHERE JRS.SALESREP_ID = OOH.SALESREP_ID
               AND JRS.RESOURCE_ID = JRSE.RESOURCE_ID
               AND JRSE.LANGUAGE = 'US'
               AND ROWNUM = 1) SALES_PERSON,
           NULL SO_TYPE,
           (SELECT OTT.DESCRIPTION
              FROM OTT
             WHERE OOH.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID
               AND OTT.LANGUAGE = USERENV('LANG')
               AND ROWNUM = 1) RA_TRX_TYPE_NAME,
           (SELECT MSIB.DESCRIPTION
              FROM MSIB
             WHERE OOL.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
               AND OOL.SHIP_FROM_ORG_ID = MSIB.ORGANIZATION_ID
               AND ROWNUM = 1) INV_ITEM_DESCRIPTION,
           OOL.ORDERED_ITEM INV_ITEM_NUMBER,
           (SELECT MDEV.ELEMENT_VALUE
              FROM MSIB, MDEV
             WHERE OOL.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
               AND OOL.SHIP_FROM_ORG_ID = MSIB.ORGANIZATION_ID
               AND MDEV.ELEMENT_NAME(+) = '商品简称'
               AND MDEV.INVENTORY_ITEM_ID(+) = MSIB.INVENTORY_ITEM_ID
               AND ROWNUM = 1) ITEM_TYPE,
           (SELECT PAF.PER_NAME
              FROM PAF, JR
             WHERE JR.PERSON_ID = PAF.PERSON_ID
               AND NVL(OOH.ATTRIBUTE3, OOH.SALESREP_ID) = JR.SALESREP_ID(+)
               AND OOH.ORG_ID = JR.ORG_ID(+)
               AND ROWNUM = 1) ATTRIBUTE1,
           OOL.LINE_NUMBER || '.' || OOL.SHIPMENT_NUMBER SO_LINE_NUM,
           OOH.HEADER_ID SO_HEADER_ID,
           null SESSION_ID
            FROM NH, GCC, OOL, OOH, FLV, HOU
           WHERE NH.UPDATE_CCID = GCC.CODE_COMBINATION_ID
             AND OOL.LINE_ID = NH.SO_LINE_ID
             AND NH.SO_HEADER_ID = OOL.HEADER_ID
             AND OOL.HEADER_ID = OOH.HEADER_ID
             AND GCC.SEGMENT2 > '0'
             AND GCC.SEGMENT3 >= FLV.MEANING
             AND GCC.SEGMENT3 <= FLV.DESCRIPTION
             AND DECODE(null, NULL, '-1', GCC.SEGMENT7) =
                 DECODE(null, NULL, '-1', null)
             AND FLV.LOOKUP_TYPE = 'XY_AR_COST_SUBJECT'
             AND NVL(FLV.ENABLED_FLAG, 'N') = 'Y'
             AND SYSDATE BETWEEN NVL(FLV.START_DATE_ACTIVE, SYSDATE - 1) AND
                 NVL(FLV.END_DATE_ACTIVE, SYSDATE + 1)
             AND GCC.SUMMARY_FLAG = 'N'
             AND NH.GL_IF_FLAG = 'OK'
             AND HOU.SHORT_CODE = GCC.SEGMENT1
             AND HOU.SET_OF_BOOKS_ID = 2022
             AND (GCC.SEGMENT1 = null OR null IS NULL)
             AND NH.ATTRIBUTE2 >= '2022-01'
             AND NH.ATTRIBUTE2 <= '2022-01'
             AND (NH.INV_ORG_ID = null OR null IS NULL)
             AND OOH.ORG_ID = HOU.ORGANIZATION_ID
             AND OOL.ORG_ID = HOU.ORGANIZATION_ID
             --AND XY_COM_DEPT_READ.COM_READ(OOH.ORG_ID) = 'Y'
             AND (OOH.ORG_ID = null OR null IS NULL)
             AND (OOL.ORG_ID = null OR null IS NULL)
           GROUP BY NH.INV_ORG_ID,
                    GCC.SEGMENT2,
                    GCC.SEGMENT6,
                    GCC.SEGMENT7,
                    GCC.SEGMENT1,
                    GCC.SEGMENT5,
                    OOH.ORDER_NUMBER,
                    OOL.SOLD_TO_ORG_ID,
                    OOH.SALESREP_ID,
                    OOH.ORDER_TYPE_ID,
                    OOL.INVENTORY_ITEM_ID,
                    OOL.SHIP_FROM_ORG_ID,
                    OOL.ORDERED_ITEM,
                    NVL(OOH.ATTRIBUTE3, OOH.SALESREP_ID),
                    OOH.ORG_ID,
                    OOL.LINE_NUMBER || '.' || OOL.SHIPMENT_NUMBER,
                    OOH.HEADER_ID)
   WHERE XY_COM_DEPT_READ.COM_READ(ORG_ID) = 'Y'
54316 rows inserted
Executed in 17.684 seconds

改写之后从47秒降低到17秒，有的读者可能会有疑问，这个SQL恰好是GROUP BY的列有OOH.ORG_ID
如果GROUP BY的列中没有有OOH.ORG_ID，怎么改写呢？其实也很简单，可以这样改写

SQL> SELECT /*+ leading(hou) no_merge(hou) full(nh)  */
  2                count(*)
  3                     FROM  NH,
  4                           GCC,
  5                           OOL,
  6                           OOH,
  7                           FLV,
  8                           HOU
  9                    WHERE NH.UPDATE_CCID = GCC.CODE_COMBINATION_ID
 10                      AND OOL.LINE_ID = NH.SO_LINE_ID
 11                      AND NH.SO_HEADER_ID = OOL.HEADER_ID
 12                      AND OOL.HEADER_ID = OOH.HEADER_ID
 13                      AND GCC.SEGMENT2 > '0'
 14                      AND GCC.SEGMENT3 >= FLV.MEANING
 15                      AND GCC.SEGMENT3 <= FLV.DESCRIPTION
 16                      AND DECODE(null, NULL, '-1', GCC.SEGMENT7) =
 17                          DECODE(null, NULL, '-1', null)
 18                      AND FLV.LOOKUP_TYPE = 'XY_AR_COST_SUBJECT'
 19                      AND NVL(FLV.ENABLED_FLAG, 'N') = 'Y'
 20                      AND SYSDATE BETWEEN
 21                          NVL(FLV.START_DATE_ACTIVE, SYSDATE - 1) AND
 22                          NVL(FLV.END_DATE_ACTIVE, SYSDATE + 1)
 23                      AND GCC.SUMMARY_FLAG = 'N'
 24                      AND NH.GL_IF_FLAG = 'OK'
 25                      AND HOU.SHORT_CODE = GCC.SEGMENT1
 26                      AND HOU.SET_OF_BOOKS_ID = 2022
 27                      AND (GCC.SEGMENT1 = null OR null IS NULL)
 28                      AND NH.ATTRIBUTE2 >= '2022-01'
 29                      AND NH.ATTRIBUTE2 <= '2022-01'
 30                      AND (NH.INV_ORG_ID = null OR null IS NULL)
 31                      AND OOH.ORG_ID = HOU.ORGANIZATION_ID
 32                      AND OOL.ORG_ID = HOU.ORGANIZATION_ID
 33                      --AND XY_COM_DEPT_READ.COM_READ(OOH.ORG_ID) = 'Y'
 34                      AND EXISTS (SELECT /*+ NO_UNNEST */ NULL FROM DUAL WHERE XY_COM_DEPT_READ.COM_READ(OOH.ORG_ID) = 'Y')
 35                      AND (OOH.ORG_ID = null OR null IS NULL)
 36                      AND (OOL.ORG_ID = null OR null IS NULL)
 37  ;

  COUNT(*)
----------
   2685697
Executed in 15.281 seconds

改写为EXISTS(SELECT /*+ NO_UNNEST */)让其走FILTER，因为FILTER有去重功能，所以走FILTER也会减少函数被调用的次数
总结：最优的方法应该是将自定义函数中的SQL提取出来，改写为表连接，但是有时候自定义函数特别复杂，改写为表连接相比减少函数被调用次数性能提升也没有特别大的提升，所以就不必要去折腾

robinson1988
关注
专栏目录
【D3S】数据权限 - 基于Mybatis的数据权限拦截器实现
罗小爬的技术宝书
07-02 1519
目录一、背景二、动机三、实现思路3.1 权限类型、操作类型3.2 统一用户及数据权限集合模型3.3 定义数据权限拦截注解3.4 提取配置属性3.5 数据权限拦截器实现四、集成方式五、关于D3S 一、背景 最近一直在做RBAC相关的架构设计与实现，传统的RBAC的权限控制只是控制到REST接口（url）、具体方法（权限码）等，而通常实际业务场景还需要对数据权限进行控制，例如： 用户仅允许查询自己的用户信息，不允许查询其他人的用户信息 用户仅被允许查询 同部门 或 同部门及子部门 的用户信息 用户仅允许查询指
Mybatis中利用动态SQL对数据库进行优化和安全检查
程序员光剑
07-29 1921
MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。但是 MyBatis 也存在着不少性能和安全方面的问题。Mybatis 本身功能强大且易用，但同时也可能给系统带来很多隐患和风险。为了更好地保障系统的安全性和运行效率，本篇文章将结合 Mybatis 的一些特性，通过动态 SQL 对数据库进行优化和安全检查的方法进行探讨。什么是动态 SQL？
针对where条件中日期型字段前面加函数的慢查询的优化
雅冰石的专栏
08-05 9579
当字段上创建了索引，但是where条件中该列前面加了函数时，会使索引失效。有时候可以通过改写sql来优化。 如： select count(*) from T_SYNCLOG where TO_DAYS(NOW()) - TO_DAYS(CREATE_TIME) = 0 and STATUS=0 and ENTITY_TYPE='org'; 推断该sql含义是查看创建日期为今天的记录条数
Oracle - 如何提高SQL的查询效率（where条件优化）
崔向阳@李晓的博客
08-27 7562
在Oracle数据库中编写查询语句的时候，经常碰到的一个问题就是SQL优化，那么今天就总结一下Where之后，如何进行条件的优化 优化规则： 一、选择合适的基础表 Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。如果有3个以上...
MySQL语句优化之Where优化
圆蟀的专栏
01-24 1274
SQL语句之Where表达式的优化
sql 优化
wzs535131的博客
05-15 100
1.通过explain 解释执行计划，查看sql相应的索引有没有执行，尤其是where条件后面的条件，和通过join连接表时的关联条件有没有添加索引，如果没有添加索引，就加上索引。 2.如果有索引但是没有走，可以使用强制索引试试。 3.join后的关联条件，有索引但是没有走，可能有以下几种情况，2个字段的字段类型不一致，比如一个是int类型，一个是String类型，或者2个字段的字符集编码不一...
golang gin——controller 模型绑定与参数校验
最新发布
qq_43058348的博客
10-06 722
gin框架提供了多种方法可以将请求体的内容绑定到对应struct上，并且提供了一些预置的参数校验。gin提供了一系列预置的参数校验，可以参考官方文档。用binding 标签。根据数据源和类型的不同，gin提供了不同的绑定方法。omitempty 允许为空。required 必须参数。number 要求数字。email 邮件格式。
SpringBoot基于RBAC自制简易的权限验证（AOP自定义注解）
m0_61820867的博客
08-26 1676
SpringBoot基于RBAC自制简易的权限验证（AOP自定义注解）
帆软填报-根据角色/权限设置校验规则（含开关配置表校验）
u012234486的博客
09-01 1915
一、校验公式 用户假设：①填报角色为"A项目-填报"；②核对角色为"A项目-核对"；③数据链接为"data_link"； 用户假设：③设有个开关表可决定该补录表是否可用，is_able='Y’表示该表可进行填报 系统参数：帆软角色参数：$fine_role 校验规则：只允许在"A项目-填报"或"A项目-核对"两个角色中的用户在该表可用时进行填报 if( --如果该用户角色名中包含"A项目-填报"字符串，且开关表配置该补录表可用，则返回1 and(find("A项目-填报", JOINARRAY($fi.
存储过程与函数性能优化实战
在数据库管理系统中，存储过程和函数是两种重要的数据库对象，它们包含了一系列的SQL语句和逻辑处理，并经过编译后存储在数据库中，可以被多次调用执行。存储过程和函数可以提高数据库的性能和安全性，同时也可以...
oracle userenv('language'),USERENV()获取Oracle环境信息
weixin_29955689的博客
04-12 573
一般通过USERENV()函数获取SELECT USERENV('ISDBA'),USERENV('LANGUAGE'),USERENV('ENTRYID'),USERENV('LANG'),USERENV('INSTANCE')FROM DUAL也可以通过以下函数获取SELECT SYS_CONTEXT('USERENV', 'TERMINAL') TERMINAL,SYS_CONTEXT('U...
oracle查询nls_language,NLS_LANGUAGE配置导致应用查询不到数据
weixin_32957761的博客
04-10 1863
一、故障现象反馈开发人员反馈系统查询不显示数据，但是使用PL/SQL developer调用相同的SQL可以查到数据。二、问题排查1.咨询开发人员是否执行相关事务未提交，确认未有相关事务执行。2.使用SQLPlus在数据库后台查询反馈的SQL，同样无法查询到数据。SQL调用的是一个视图，在用SQLPlus查询，该视图返回0条数据，但是PL/SQL Developer返回6万多条数据。SQL>...
oracle中subject是什么意思,Oracle EBS工作流表Subject等字段取值为空解决方法
weixin_35204634的博客
04-09 399
本文来源：http://blog.csdn.net/sunansheng/article/details/49620789问题描述在做Oracle EBS工作流列表开发的时候，可以使用如下SQL获取系统工作流列表：--sql已做精简SELECT /*+ ORDERED PUSH_SUBQ USE_NL (WN WL WIT) index(WN WF_NOTIFICATIONS_N1)*/wn.no...
【Mysql性能优化系列】MySQL优化WHERE语句18个硬核技巧
7me
09-17 1259
要让慢SELECT … WHERE查询更快，首先要检查是否可以添加索引。在WHERE子句中使用的列上设置索引，以加快评估、过滤和最终检索结果的速度。本文将学习MySQL对Where语句的优化处理规则。
在 SQL Server 中授予行级权限
云中客的专栏
06-13 1761
某些方案中会要求更为细致地控制访问，而不仅仅是授予、撤消或拒绝对数据的权限。 例如，医院数据库应用程序可能会将患者信息存储在单个表中。 可能需要限制医生仅查看与他们自己的患者相关的信息。 在很多环境中（包括财务、法律、政府和军事应用程序）都存在类似的方案。 不过，SQL Server 并不支持实现行级安全性。 您必须在表中创建附加列以定义行筛选机制。  实现行级权限  
oracle的userenv和nls_lang详解
职场亮哥
09-19 2596
oracle的userenv和nls_lang详解 1、userenv最常见的使用 userenv函数返回当前会话（session）的相关信息。以下sql语句可以查询当前会话连接的数据库字符集 select userenv('language') from dual; 有关userenv('parameter')返回值的官网介绍如下 意思就是：返回的是当前会话使用的language和territory。characterset是数据库的字符集。 下面我们就去验证这种情况
JDBC userenv('LANG')
08-25 745
select userenv('LANGUAGE') ,userenv('LANG') from dual; SIMPLIFIED CHINESE_CHINA.UTF8 ZHS     1.想修改JDBC所在tomcat端的值userenv('LANG')为 US; 2.在tomcat 启动脚本中，设置  NLS_LANG=AMERICAN_AMERICA.UTF8 没有什么作用 3...
oracle中lang的作用,oracle的userenv和nls_lang详解
weixin_34430676的博客
04-02 556
oracle的userenv和nls_lang详解1、userenv最常见的使用userenv函数返回当前会话(session)的相关信息。以下sql语句可以查询当前会话连接的数据库字符集select userenv('language') from dual;有关userenv('parameter')返回值的官网介绍如下意思就是：返回的是当前会话使用的language和territory。ch...
where条件中，索引字段使用函数
05-12
在where条件中，索引字段使用函数通常会导致索引失效，从而导致查询变慢。因为函数会对索引字段进行计算，而不是直接使用索引中的值来进行匹配。这样就会使得数据库无法使用索引来加速查询，而需要进行全表扫描来...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值