-
where条件中有权限校验的自定义函数优化方法
下面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提取出来，改写为表连接，但是有时候自定义函数特别复杂，改写为表连接相比减少函数被调用次数性能提升也没有特别大的提升，所以就不必要去折腾 -
相关阅读:
江苏建筑模板厂家-建筑模板批发供应商
基于 Scriptable 从零开始美化iOS桌面（集合篇）
Linux 同步管理（上）
黑马程序员C++类和对象【5】 —— 运算符重载（蓝桥杯必备知识）万字超详解
批量上传图片
Java 复习笔记 - 集合进阶篇：数据结构
1.Zookeeper理论基础
A*(A星,Astar)路径规划算法
STM32串口通信
学习笔记-java代码审计-表达式注入
- 原文地址：https://blog.csdn.net/robinson1988/article/details/127423530
