SQL优化案例之exists中套or not exists
章晗日
又是EBS，一条SQL语句要跑16-30分钟，SQL语句如下：
SELECT GJH.DEFAULT_EFFECTIVE_DATE,
GJH.ATTRIBUTE1,
GJH.CURRENCY_CODE,
GJH.CURRENCY_CONVERSION_RATE,
GJH.JE_BATCH_ID,
GJH.JE_HEADER_ID,
GJH.LEDGER_ID,
GJH.JE_SOURCE,
GJH.JE_CATEGORY,
GJH.NAME
FROM GL_JE_HEADERS GJH
WHERE GJH.PERIOD_NAME >= '2011-07'
AND GJH.STATUS = DECODE('A', 'P', 'P', GJH.STATUS)
AND GJH.LEDGER_ID = 2022
AND GJH.DEFAULT_EFFECTIVE_DATE <= TO_DATE('2022-09-30', 'YYYY-MM-DD')
AND EXISTS
(SELECT 1
FROM GL_JE_LINES GJLS, GL_CODE_COMBINATIONS GCCS
WHERE GCCS.SEGMENT3 = '220202010301'
AND GCCS.SEGMENT1 = NVL('300', GCCS.SEGMENT1)
AND (GJH.JE_SOURCE IN ('Manual', 'AutoCopy', 'Revaluation') OR
(GJH.JE_SOURCE IN ('Payables') AND NOT EXISTS
(SELECT 1
FROM GL_IMPORT_REFERENCES GIR, XLA.XLA_AE_LINES XAL
WHERE GIR.JE_HEADER_ID = GJLS.JE_HEADER_ID
AND GIR.JE_LINE_NUM = GJLS.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE)))
AND GJLS.CODE_COMBINATION_ID = GCCS.CODE_COMBINATION_ID
AND GJLS.PERIOD_NAME = GJH.PERIOD_NAME
AND GJLS.JE_HEADER_ID = GJH.JE_HEADER_ID);GJH.JE_HEADER_ID 是主键
上面SQL要跑16分钟(有时候更慢)，SQL慢在OR NOT EXISTS，A-TIME执行计划如下：
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1509 |00:16:43.77 | 323M| 2449K|
|* 1 | FILTER | | 1 | | 1509 |00:16:43.77 | 323M| 2449K|
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED | GL_JE_HEADERS | 1 | 1376K| 21M|00:00:19.93 | 2147K| 0 |
|* 3 | INDEX RANGE SCAN | NGL_JE_HEADERS_N17 | 1 | 1396K| 21M|00:00:04.40 | 74631 | 0 |
|* 4 | FILTER | | 21M| | 1509 |00:12:57.83 | 321M| 2449K|
| 5 | NESTED LOOPS SEMI | | 21M| 1 | 211K|00:12:52.29 | 320M| 2449K|
|* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| GL_JE_LINES | 21M| 1 | 302M|00:08:46.16 | 165M| 2270K|
|* 7 | INDEX RANGE SCAN | GL_JE_LINES_N7 | 21M| 20 | 302M|00:02:04.23 | 51M| 777K|
|* 8 | INDEX RANGE SCAN | GL_CODE_COMBINATIONS_N12 | 70M| 458 | 100K|00:02:35.13 | 155M| 179K|
| 9 | NESTED LOOPS SEMI | | 157K| 1 | 157K|00:00:01.68 | 824K| 0 |
|* 10 | TABLE ACCESS BY INDEX ROWID BATCHED| GL_IMPORT_REFERENCES | 157K| 1 | 157K|00:00:00.54 | 529K| 0 |
|* 11 | INDEX RANGE SCAN | GL_IMPORT_REFERENCES_N1 | 157K| 2 | 157K|00:00:00.36 | 372K| 0 |
| 12 | PARTITION LIST ALL | | 157K| 1095K| 157K|00:00:00.44 | 294K| 0 |
|* 13 | INDEX RANGE SCAN | XLA_AE_LINES_N4 | 157K| 1095K| 157K|00:00:00.32 | 294K| 0 |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - filter("GJH"."DEFAULT_EFFECTIVE_DATE"<=TO_DATE(' 2022-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
3 - access("GJH"."LEDGER_ID"=2022 AND "GJH"."PERIOD_NAME">='2011-07')
4 - filter((:B1='AutoCopy' OR :B2='Manual' OR :B3='Revaluation' OR (:B4='Payables' AND IS NULL)))
6 - filter("GJLS"."PERIOD_NAME"=:B1)
7 - access("GJLS"."JE_HEADER_ID"=:B1)
8 - access("GJLS"."CODE_COMBINATION_ID"="GCCS"."CODE_COMBINATION_ID" AND "GCCS"."SEGMENT1"='300' AND
"GCCS"."SEGMENT3"='220202010301')
10 - filter(("GIR"."GL_SL_LINK_ID" IS NOT NULL AND "GIR"."GL_SL_LINK_TABLE" IS NOT NULL))
11 - access("GIR"."JE_HEADER_ID"=:B1 AND "GIR"."JE_LINE_NUM"=:B2)
13 - access("GIR"."GL_SL_LINK_ID"="XAL"."GL_SL_LINK_ID" AND "GIR"."GL_SL_LINK_TABLE"="XAL"."GL_SL_LINK_TABLE")添加HINT OR_EXPAND，尝试了各种办法，执行计划没有任何改变，放弃加HINT的优化方法
将SQL改成MINUS，这时候SQL就可以自动OR_EXPAND(本方法不一定对其他SQL100%有效)
SELECT GJH.DEFAULT_EFFECTIVE_DATE,
GJH.ATTRIBUTE1,
GJH.CURRENCY_CODE,
GJH.CURRENCY_CONVERSION_RATE,
GJH.JE_BATCH_ID,
GJH.JE_HEADER_ID,
GJH.LEDGER_ID,
GJH.JE_SOURCE,
GJH.JE_CATEGORY,
GJH.NAME
FROM GL_JE_HEADERS GJH
WHERE GJH.PERIOD_NAME >= '2011-07'
AND GJH.STATUS = DECODE('A', 'P', 'P', GJH.STATUS)
AND GJH.LEDGER_ID = 2022
AND GJH.DEFAULT_EFFECTIVE_DATE <= TO_DATE('2022-09-30', 'YYYY-MM-DD')
AND EXISTS
(SELECT 1
FROM GL_JE_LINES GJLS, GL_CODE_COMBINATIONS GCCS
WHERE GCCS.SEGMENT3 = '220202010301'
AND GCCS.SEGMENT1 = NVL('300', GCCS.SEGMENT1)
AND (GJH.JE_SOURCE IN ('Manual', 'AutoCopy', 'Revaluation') OR
(GJH.JE_SOURCE IN ('Payables') AND NOT EXISTS
(SELECT 1
FROM GL_IMPORT_REFERENCES GIR, XLA.XLA_AE_LINES XAL
WHERE GIR.JE_HEADER_ID = GJLS.JE_HEADER_ID
AND GIR.JE_LINE_NUM = GJLS.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE)))
AND GJLS.CODE_COMBINATION_ID = GCCS.CODE_COMBINATION_ID
AND GJLS.PERIOD_NAME = GJH.PERIOD_NAME
AND GJLS.JE_HEADER_ID = GJH.JE_HEADER_ID)
MINUS
SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM DUAL;12秒就能跑完，A-TIME执行计划如下：
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1509 |00:00:12.18 | 2819K| | | |
| 1 | MINUS | | 1 | | 1509 |00:00:12.18 | 2819K| | | |
| 2 | SORT UNIQUE | | 1 | 7 | 1509 |00:00:12.18 | 2819K| 302K| 302K| 268K (0)|
| 3 | VIEW | VW_ORE_96E2B5CB | 1 | 7 | 3577 |00:00:01.74 | 2819K| | | |
| 4 | UNION-ALL | | 1 | | 3577 |00:00:01.74 | 2819K| | | |
|* 5 | HASH JOIN | | 1 | 5 | 3577 |00:00:01.74 | 992K| 14M| 4590K| 21M (0)|
| 6 | NESTED LOOPS | | 1 | 53551 | 213K|00:00:00.29 | 145K| | | |
| 7 | NESTED LOOPS | | 1 | 53586 | 213K|00:00:00.06 | 46246 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED | GL_CODE_COMBINATIONS | 1 | 458 | 13875 |00:00:00.03 | 10257 | | | |
|* 9 | INDEX SKIP SCAN | GL_CODE_COMBINATION_N16 | 1 | 458 | 13875 |00:00:00.01 | 106 | 1025K| 1025K| |
|* 10 | INDEX RANGE SCAN | GL_JE_LINES_N1 | 13875 | 117 | 213K|00:00:00.06 | 35989 | 1025K| 1025K| |
| 11 | TABLE ACCESS BY INDEX ROWID | GL_JE_LINES | 213K| 117 | 213K|00:00:00.20 | 99416 | | | |
|* 12 | TABLE ACCESS BY INDEX ROWID BATCHED | GL_JE_HEADERS | 1 | 29297 | 1636K|00:00:06.40 | 847K| | | |
|* 13 | INDEX RANGE SCAN | IND_GJH_COMP | 1 | 89139 | 1636K|00:00:04.53 | 115K| 1025K| 1025K| |
|* 14 | FILTER | | 1 | | 0 |00:00:07.75 | 1826K| | | |
|* 15 | HASH JOIN | | 1 | 2 | 157K|00:00:06.31 | 972K| 607M| 19M| 610M (0)|
|* 16 | TABLE ACCESS BY INDEX ROWID BATCHED | GL_JE_HEADERS | 1 | 9156 | 3872K|00:00:04.71 | 826K| | | |
|* 17 | INDEX SKIP SCAN | IND_GJH_COMP | 1 | 29713 | 3872K|00:00:01.54 | 21313 | 1025K| 1025K| |
| 18 | NESTED LOOPS | | 1 | 53551 | 213K|00:00:00.39 | 145K| | | |
| 19 | NESTED LOOPS | | 1 | 53586 | 213K|00:00:00.08 | 46246 | | | |
| 20 | TABLE ACCESS BY INDEX ROWID BATCHED| GL_CODE_COMBINATIONS | 1 | 458 | 13875 |00:00:00.03 | 10257 | | | |
|* 21 | INDEX SKIP SCAN | GL_CODE_COMBINATION_N16 | 1 | 458 | 13875 |00:00:00.01 | 106 | 1025K| 1025K| |
|* 22 | INDEX RANGE SCAN | GL_JE_LINES_N1 | 13875 | 117 | 213K|00:00:00.07 | 35989 | 1025K| 1025K| |
| 23 | TABLE ACCESS BY INDEX ROWID | GL_JE_LINES | 213K| 117 | 213K|00:00:00.25 | 99416 | | | |
| 24 | NESTED LOOPS SEMI | | 157K| 1 | 157K|00:00:01.26 | 854K| | | |
|* 25 | TABLE ACCESS BY INDEX ROWID BATCHED | GL_IMPORT_REFERENCES | 157K| 1 | 157K|00:00:00.51 | 544K| | | |
|* 26 | INDEX RANGE SCAN | GL_IMPORT_REFERENCES_N1 | 157K| 2 | 157K|00:00:00.33 | 386K| 1025K| 1025K| |
| 27 | PARTITION LIST ALL | | 157K| 1095K| 157K|00:00:00.34 | 310K| | | |
|* 28 | INDEX RANGE SCAN | XLA_AE_LINES_N4 | 157K| 1095K| 157K|00:00:00.27 | 310K| 1025K| 1025K| |
| 29 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("GJLS"."PERIOD_NAME"="GJH"."PERIOD_NAME" AND "GJLS"."JE_HEADER_ID"="GJH"."JE_HEADER_ID")
9 - access("GCCS"."SEGMENT3"='220202010301' AND "GCCS"."SEGMENT1"='300')
filter(("GCCS"."SEGMENT3"='220202010301' AND "GCCS"."SEGMENT1"='300'))
10 - access("GJLS"."CODE_COMBINATION_ID"="GCCS"."CODE_COMBINATION_ID" AND "GJLS"."PERIOD_NAME">='2011-07')
12 - filter(("GJH"."STATUS"="GJH"."STATUS" AND "GJH"."DEFAULT_EFFECTIVE_DATE"<=TO_DATE(' 2022-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
13 - access("GJH"."LEDGER_ID"=2022 AND "GJH"."PERIOD_NAME">='2011-07')
filter(("GJH"."JE_SOURCE"='AutoCopy' OR "GJH"."JE_SOURCE"='Manual' OR "GJH"."JE_SOURCE"='Revaluation'))
14 - filter( IS NULL)
15 - access("GJLS"."PERIOD_NAME"="GJH"."PERIOD_NAME" AND "GJLS"."JE_HEADER_ID"="GJH"."JE_HEADER_ID")
16 - filter(("GJH"."STATUS"="GJH"."STATUS" AND "GJH"."DEFAULT_EFFECTIVE_DATE"<=TO_DATE(' 2022-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
17 - access("GJH"."LEDGER_ID"=2022 AND "GJH"."PERIOD_NAME">='2011-07' AND "GJH"."JE_SOURCE"='Payables')
filter(("GJH"."JE_SOURCE"='Payables' AND LNNVL("GJH"."JE_SOURCE"='AutoCopy') AND LNNVL("GJH"."JE_SOURCE"='Manual') AND
LNNVL("GJH"."JE_SOURCE"='Revaluation')))
21 - access("GCCS"."SEGMENT3"='220202010301' AND "GCCS"."SEGMENT1"='300')
filter(("GCCS"."SEGMENT3"='220202010301' AND "GCCS"."SEGMENT1"='300'))
22 - access("GJLS"."CODE_COMBINATION_ID"="GCCS"."CODE_COMBINATION_ID" AND "GJLS"."PERIOD_NAME">='2011-07')
25 - filter(("GIR"."GL_SL_LINK_ID" IS NOT NULL AND "GIR"."GL_SL_LINK_TABLE" IS NOT NULL))
26 - access("GIR"."JE_HEADER_ID"=:B1 AND "GIR"."JE_LINE_NUM"=:B2)
28 - access("GIR"."GL_SL_LINK_ID"="XAL"."GL_SL_LINK_ID" AND "GIR"."GL_SL_LINK_TABLE"="XAL"."GL_SL_LINK_TABLE")
用LEFT JOIN改写SQL
SELECT DEFAULT_EFFECTIVE_DATE,
ATTRIBUTE1,
CURRENCY_CODE,
CURRENCY_CONVERSION_RATE,
JE_BATCH_ID,
JE_HEADER_ID,
LEDGER_ID,
JE_SOURCE,
JE_CATEGORY,
NAME
FROM (with x as (select PERIOD_NAME, JE_HEADER_ID, JE_LINE_NUM
from GL_JE_LINES GJLS
WHERE GJLS.CODE_COMBINATION_ID IN
(SELECT GCCS.CODE_COMBINATION_ID
FROM GL_CODE_COMBINATIONS GCCS
WHERE GCCS.SEGMENT3 = '220202010301'
AND GCCS.SEGMENT1 = NVL('300', GCCS.SEGMENT1))
group by PERIOD_NAME, JE_HEADER_ID, JE_LINE_NUM)
SELECT GJH.DEFAULT_EFFECTIVE_DATE,
GJH.ATTRIBUTE1,
GJH.CURRENCY_CODE,
GJH.CURRENCY_CONVERSION_RATE,
GJH.JE_BATCH_ID,
GJH.JE_HEADER_ID,
GJH.LEDGER_ID,
GJH.JE_SOURCE,
GJH.JE_CATEGORY,
GJH.NAME,
ROW_NUMBER() OVER(PARTITION BY GJH.JE_HEADER_ID ORDER BY GJH.ROWID) RN
FROM GL_JE_HEADERS GJH,
X,
(SELECT JE_HEADER_ID, JE_LINE_NUM
FROM GL_IMPORT_REFERENCES GIR
WHERE EXISTS
(SELECT NULL
FROM XLA.XLA_AE_LINES XAL
WHERE GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE)) X2
WHERE GJH.PERIOD_NAME >= '2011-07'
AND GJH.STATUS = DECODE('A', 'P', 'P', GJH.STATUS)
AND GJH.LEDGER_ID = 2022
AND GJH.DEFAULT_EFFECTIVE_DATE <=
TO_DATE('2022-09-30', 'YYYY-MM-DD')
AND GJH.PERIOD_NAME = X.PERIOD_NAME
AND GJH.JE_HEADER_ID = X.JE_HEADER_ID
AND X.JE_HEADER_ID = X2.JE_HEADER_ID(+)
AND X.JE_LINE_NUM = X2.JE_LINE_NUM(+)
AND (GJH.JE_SOURCE IN ('Manual', 'AutoCopy', 'Revaluation') OR
(GJH.JE_SOURCE IN ('Payables') AND X2.JE_HEADER_ID IS NULL)))
WHERE RN = 1A-TIME执行计划如下：
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1509 |00:00:06.06 | 4764K| | | |
|* 1 | VIEW | | 1 | 22 | 1509 |00:00:06.06 | 4764K| | | |
|* 2 | WINDOW SORT PUSHED RANK | | 1 | 22 | 1509 |00:00:06.06 | 4764K| 832K| 832K| 739K (0)|
|* 3 | FILTER | | 1 | | 3577 |00:00:05.77 | 4764K| | | |
| 4 | NESTED LOOPS OUTER | | 1 | 22 | 347K|00:00:06.00 | 4764K| | | |
| 5 | NESTED LOOPS | | 1 | 341 | 213K|00:00:01.32 | 622K| | | |
| 6 | VIEW | | 1 | 53551 | 213K|00:00:00.50 | 145K| | | |
| 7 | HASH GROUP BY | | 1 | 53551 | 213K|00:00:00.48 | 145K| 14M| 3833K| 12M (0)|
| 8 | NESTED LOOPS | | 1 | 53551 | 213K|00:00:00.37 | 145K| | | |
| 9 | NESTED LOOPS | | 1 | 53586 | 213K|00:00:00.09 | 46246 | | | |
| 10 | TABLE ACCESS BY INDEX ROWID BATCHED| GL_CODE_COMBINATIONS | 1 | 458 | 13875 |00:00:00.03 | 10257 | | | |
|* 11 | INDEX SKIP SCAN | GL_CODE_COMBINATION_N16 | 1 | 458 | 13875 |00:00:00.01 | 106 | 1025K| 1025K| |
|* 12 | INDEX RANGE SCAN | GL_JE_LINES_N1 | 13875 | 117 | 213K|00:00:00.07 | 35989 | 1025K| 1025K| |
| 13 | TABLE ACCESS BY INDEX ROWID | GL_JE_LINES | 213K| 117 | 213K|00:00:00.23 | 99416 | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID | GL_JE_HEADERS | 213K| 1 | 213K|00:00:00.73 | 477K| | | |
|* 15 | INDEX UNIQUE SCAN | GL_JE_HEADERS_U1 | 213K| 1 | 213K|00:00:00.37 | 345K| 1025K| 1025K| |
| 16 | VIEW PUSHED PREDICATE | | 213K| 1 | 343K|00:00:04.83 | 4142K| | | |
| 17 | NESTED LOOPS SEMI | | 213K| 1 | 343K|00:00:04.76 | 4142K| | | |
|* 18 | TABLE ACCESS BY INDEX ROWID BATCHED | GL_IMPORT_REFERENCES | 213K| 1 | 343K|00:00:01.10 | 715K| | | |
|* 19 | INDEX RANGE SCAN | GL_IMPORT_REFERENCES_N1 | 213K| 2 | 343K|00:00:00.69 | 553K| 1025K| 1025K| |
| 20 | PARTITION LIST ALL | | 343K| 1095K| 343K|00:00:02.93 | 3426K| | | |
|* 21 | INDEX RANGE SCAN | XLA_AE_LINES_N4 | 1832K| 1095K| 343K|00:00:02.64 | 3426K| 1025K| 1025K| |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"=1)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "GJH"."JE_HEADER_ID" ORDER BY "GJH".ROWID)<=1)
3 - filter((INTERNAL_FUNCTION("GJH"."JE_SOURCE") OR ("GJH"."JE_SOURCE"='Payables' AND "X2"."JE_HEADER_ID" IS NULL)))
11 - access("GCCS"."SEGMENT3"='220202010301' AND "GCCS"."SEGMENT1"='300')
filter(("GCCS"."SEGMENT3"='220202010301' AND "GCCS"."SEGMENT1"='300'))
12 - access("GJLS"."CODE_COMBINATION_ID"="GCCS"."CODE_COMBINATION_ID" AND "PERIOD_NAME">='2011-07')
14 - filter(("GJH"."PERIOD_NAME"="X"."PERIOD_NAME" AND "GJH"."LEDGER_ID"=2022 AND "GJH"."DEFAULT_EFFECTIVE_DATE"<=TO_DATE(' 2022-09-30 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "GJH"."PERIOD_NAME">='2011-07'))
15 - access("GJH"."JE_HEADER_ID"="X"."JE_HEADER_ID")
18 - filter(("GIR"."GL_SL_LINK_ID" IS NOT NULL AND "GIR"."GL_SL_LINK_TABLE" IS NOT NULL))
19 - access("JE_HEADER_ID"="X"."JE_HEADER_ID" AND "JE_LINE_NUM"="X"."JE_LINE_NUM")
21 - access("GIR"."GL_SL_LINK_ID"="XAL"."GL_SL_LINK_ID" AND "GIR"."GL_SL_LINK_TABLE"="XAL"."GL_SL_LINK_TABLE")
SQL6秒能跑完，但是逻辑读有点大，将SQL改写为UNION
SELECT /*+ leading(x) use_nl(x,gjh) */
GJH.DEFAULT_EFFECTIVE_DATE,
GJH.ATTRIBUTE1,
GJH.CURRENCY_CODE,
GJH.CURRENCY_CONVERSION_RATE,
GJH.JE_BATCH_ID,
GJH.JE_HEADER_ID,
GJH.LEDGER_ID,
GJH.JE_SOURCE,
GJH.JE_CATEGORY,
GJH.NAME
FROM GL_JE_HEADERS GJH,
(SELECT JE_HEADER_ID, PERIOD_NAME
FROM GL_JE_LINES GJLS
WHERE EXISTS
(SELECT NULL
FROM GL_CODE_COMBINATIONS GCCS
WHERE GCCS.SEGMENT3 = '220202010301'
AND GCCS.SEGMENT1 = NVL('300', GCCS.SEGMENT1)
AND GJLS.CODE_COMBINATION_ID = GCCS.CODE_COMBINATION_ID)
GROUP BY JE_HEADER_ID, PERIOD_NAME) X
WHERE GJH.PERIOD_NAME >= '2011-07'
AND GJH.STATUS = DECODE('A', 'P', 'P', GJH.STATUS)
AND GJH.LEDGER_ID = 2022
AND GJH.DEFAULT_EFFECTIVE_DATE <= TO_DATE('2022-09-30', 'YYYY-MM-DD')
AND X.PERIOD_NAME = GJH.PERIOD_NAME
AND X.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJH.JE_SOURCE IN ('Manual', 'AutoCopy', 'Revaluation')
UNION
SELECT /*+ leading(x) use_nl(x,gjh) */
GJH.DEFAULT_EFFECTIVE_DATE,
GJH.ATTRIBUTE1,
GJH.CURRENCY_CODE,
GJH.CURRENCY_CONVERSION_RATE,
GJH.JE_BATCH_ID,
GJH.JE_HEADER_ID,
GJH.LEDGER_ID,
GJH.JE_SOURCE,
GJH.JE_CATEGORY,
GJH.NAME
FROM GL_JE_HEADERS GJH,
(SELECT /*+ NO_MERGE */
JE_HEADER_ID, PERIOD_NAME
FROM GL_JE_LINES GJLS
WHERE EXISTS
(SELECT NULL
FROM GL_CODE_COMBINATIONS GCCS
WHERE GCCS.SEGMENT3 = '220202010301'
AND GCCS.SEGMENT1 = NVL('300', GCCS.SEGMENT1)
AND GJLS.CODE_COMBINATION_ID = GCCS.CODE_COMBINATION_ID)
AND NOT EXISTS
(SELECT
/*+ NO_UNNEST */
NULL
FROM GL_IMPORT_REFERENCES GIR
WHERE GIR.JE_HEADER_ID = GJLS.JE_HEADER_ID
AND GIR.JE_LINE_NUM = GJLS.JE_LINE_NUM
AND EXISTS
(SELECT NULL
FROM XLA.XLA_AE_LINES XAL
WHERE GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE))
GROUP BY JE_HEADER_ID, PERIOD_NAME) X
WHERE GJH.PERIOD_NAME >= '2011-07'
AND GJH.STATUS = DECODE('A', 'P', 'P', GJH.STATUS)
AND GJH.LEDGER_ID = 2022
AND GJH.DEFAULT_EFFECTIVE_DATE <= TO_DATE('2022-09-30', 'YYYY-MM-DD')
AND GJH.JE_SOURCE IN ('Payables')
AND X.PERIOD_NAME = GJH.PERIOD_NAME
AND X.JE_HEADER_ID = GJH.JE_HEADER_IDA-TIME执行计划如下：
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1509 |00:00:03.93 | 2535K| | | |
| 1 | SORT UNIQUE | | 1 | 249 | 1509 |00:00:03.93 | 2535K| 267K| 267K| 237K (0)|
| 2 | UNION-ALL | | 1 | | 1509 |00:00:00.45 | 2535K| | | |
| 3 | NESTED LOOPS | | 1 | 187 | 1509 |00:00:00.45 | 350K| | | |
| 4 | NESTED LOOPS | | 1 | 53551 | 68483 |00:00:00.54 | 282K| | | |
| 5 | VIEW | | 1 | 53551 | 68483 |00:00:00.36 | 145K| | | |
| 6 | HASH GROUP BY | | 1 | 53551 | 68483 |00:00:00.36 | 145K| 6081K| 4231K| 11M (0)|
| 7 | NESTED LOOPS | | 1 | 53551 | 213K|00:00:00.29 | 145K| | | |
| 8 | NESTED LOOPS | | 1 | 53586 | 213K|00:00:00.07 | 46246 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED | GL_CODE_COMBINATIONS | 1 | 458 | 13875 |00:00:00.03 | 10257 | | | |
|* 10 | INDEX SKIP SCAN | GL_CODE_COMBINATION_N16 | 1 | 458 | 13875 |00:00:00.01 | 106 | 1025K| 1025K| |
|* 11 | INDEX RANGE SCAN | GL_JE_LINES_N1 | 13875 | 117 | 213K|00:00:00.06 | 35989 | 1025K| 1025K| |
| 12 | TABLE ACCESS BY INDEX ROWID | GL_JE_LINES | 213K| 117 | 213K|00:00:00.20 | 99416 | | | |
|* 13 | INDEX UNIQUE SCAN | GL_JE_HEADERS_U1 | 68483 | 1 | 68483 |00:00:00.15 | 136K| 1025K| 1025K| |
|* 14 | TABLE ACCESS BY INDEX ROWID | GL_JE_HEADERS | 68483 | 1 | 1509 |00:00:00.13 | 67550 | | | |
| 15 | NESTED LOOPS | | 1 | 62 | 0 |00:00:03.28 | 2185K| | | |
| 16 | NESTED LOOPS | | 1 | 53551 | 1552 |00:00:03.27 | 2183K| | | |
| 17 | VIEW | | 1 | 53551 | 1552 |00:00:03.27 | 2180K| | | |
| 18 | HASH GROUP BY | | 1 | 53551 | 1552 |00:00:03.27 | 2180K| 3174K| 3174K| 11M (0)|
|* 19 | FILTER | | 1 | | 4059 |00:00:07.37 | 2180K| | | |
| 20 | NESTED LOOPS | | 1 | 53551 | 213K|00:00:00.39 | 145K| | | |
| 21 | NESTED LOOPS | | 1 | 53586 | 213K|00:00:00.10 | 46246 | | | |
| 22 | TABLE ACCESS BY INDEX ROWID BATCHED| GL_CODE_COMBINATIONS | 1 | 458 | 13875 |00:00:00.03 | 10257 | | | |
|* 23 | INDEX SKIP SCAN | GL_CODE_COMBINATION_N16 | 1 | 458 | 13875 |00:00:00.01 | 106 | 1025K| 1025K| |
|* 24 | INDEX RANGE SCAN | GL_JE_LINES_N1 | 13875 | 117 | 213K|00:00:00.08 | 35989 | 1025K| 1025K| |
| 25 | TABLE ACCESS BY INDEX ROWID | GL_JE_LINES | 213K| 117 | 213K|00:00:00.22 | 99416 | | | |
| 26 | NESTED LOOPS SEMI | | 213K| 1 | 209K|00:00:02.65 | 2034K| | | |
|* 27 | TABLE ACCESS BY INDEX ROWID BATCHED | GL_IMPORT_REFERENCES | 213K| 1 | 209K|00:00:00.80 | 821K| | | |
|* 28 | INDEX RANGE SCAN | GL_IMPORT_REFERENCES_N1 | 213K| 2 | 210K|00:00:00.53 | 611K| 1025K| 1025K| |
| 29 | PARTITION LIST ALL | | 209K| 1095K| 209K|00:00:01.30 | 1213K| | | |
|* 30 | INDEX RANGE SCAN | XLA_AE_LINES_N4 | 636K| 1095K| 209K|00:00:01.18 | 1213K| 1025K| 1025K| |
|* 31 | INDEX UNIQUE SCAN | GL_JE_HEADERS_U1 | 1552 | 1 | 1552 |00:00:00.01 | 3106 | 1025K| 1025K| |
|* 32 | TABLE ACCESS BY INDEX ROWID | GL_JE_HEADERS | 1552 | 1 | 0 |00:00:00.01 | 1552 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("GCCS"."SEGMENT3"='220202010301' AND "GCCS"."SEGMENT1"='300')
filter(("GCCS"."SEGMENT3"='220202010301' AND "GCCS"."SEGMENT1"='300'))
11 - access("GJLS"."CODE_COMBINATION_ID"="GCCS"."CODE_COMBINATION_ID" AND "PERIOD_NAME">='2011-07')
13 - access("X"."JE_HEADER_ID"="GJH"."JE_HEADER_ID")
14 - filter(("X"."PERIOD_NAME"="GJH"."PERIOD_NAME" AND "GJH"."LEDGER_ID"=2022 AND "GJH"."STATUS"="GJH"."STATUS" AND
INTERNAL_FUNCTION("GJH"."JE_SOURCE") AND "GJH"."DEFAULT_EFFECTIVE_DATE"<=TO_DATE(' 2022-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"GJH"."PERIOD_NAME">='2011-07'))
19 - filter( IS NULL)
23 - access("GCCS"."SEGMENT3"='220202010301' AND "GCCS"."SEGMENT1"='300')
filter(("GCCS"."SEGMENT3"='220202010301' AND "GCCS"."SEGMENT1"='300'))
24 - access("GJLS"."CODE_COMBINATION_ID"="GCCS"."CODE_COMBINATION_ID" AND "PERIOD_NAME">='2011-07')
27 - filter(("GIR"."GL_SL_LINK_ID" IS NOT NULL AND "GIR"."GL_SL_LINK_TABLE" IS NOT NULL))
28 - access("GIR"."JE_HEADER_ID"=:B1 AND "GIR"."JE_LINE_NUM"=:B2)
30 - access("GIR"."GL_SL_LINK_ID"="XAL"."GL_SL_LINK_ID" AND "GIR"."GL_SL_LINK_TABLE"="XAL"."GL_SL_LINK_TABLE")
31 - access("X"."JE_HEADER_ID"="GJH"."JE_HEADER_ID")
32 - filter(("X"."PERIOD_NAME"="GJH"."PERIOD_NAME" AND "GJH"."JE_SOURCE"='Payables' AND "GJH"."LEDGER_ID"=2022 AND "GJH"."STATUS"="GJH"."STATUS"
AND "GJH"."DEFAULT_EFFECTIVE_DATE"<=TO_DATE(' 2022-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "GJH"."PERIOD_NAME">='2011-07'))
4秒左右跑完,逻辑读下降了很多，使用标量子查询再改写一版
SELECT /*+ USE_NL(X,GJH) LEADING(X) */
GJH.DEFAULT_EFFECTIVE_DATE,
GJH.ATTRIBUTE1,
GJH.CURRENCY_CODE,
GJH.CURRENCY_CONVERSION_RATE,
GJH.JE_BATCH_ID,
GJH.JE_HEADER_ID,
GJH.LEDGER_ID,
GJH.JE_SOURCE,
GJH.JE_CATEGORY,
GJH.NAME
FROM GL_JE_HEADERS GJH,
(SELECT GJLS.PERIOD_NAME,
GJLS.JE_HEADER_ID,
MAX((SELECT GIR.JE_LINE_NUM
FROM GL_IMPORT_REFERENCES GIR, XLA.XLA_AE_LINES XAL
WHERE GIR.JE_HEADER_ID = GJLS.JE_HEADER_ID
AND GIR.JE_LINE_NUM = GJLS.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
AND ROWNUM <= 1)) JE_LINE_NUM
FROM GL_JE_LINES GJLS, GL_CODE_COMBINATIONS GCCS
WHERE GCCS.SEGMENT3 = '220202010301'
AND GCCS.SEGMENT1 = NVL('300', GCCS.SEGMENT1)
AND GJLS.CODE_COMBINATION_ID = GCCS.CODE_COMBINATION_ID
GROUP BY GJLS.PERIOD_NAME, GJLS.JE_HEADER_ID) X
WHERE GJH.PERIOD_NAME >= '2011-07'
AND GJH.STATUS = DECODE('A', 'P', 'P', GJH.STATUS)
AND GJH.LEDGER_ID = 2022
AND GJH.DEFAULT_EFFECTIVE_DATE <= TO_DATE('2022-09-30', 'YYYY-MM-DD')
AND X.PERIOD_NAME = GJH.PERIOD_NAME
AND X.JE_HEADER_ID = GJH.JE_HEADER_ID
AND (GJH.JE_SOURCE IN ('Manual', 'AutoCopy', 'Revaluation') OR
(GJH.JE_SOURCE IN ('Payables') AND JE_LINE_NUM IS NULL));----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1509 |00:00:03.00 | 2384K| | | |
| 1 | NESTED LOOPS | | 1 | 22 | 1509 |00:00:03.00 | 2384K| | | |
|* 2 | COUNT STOPKEY | | 213K| | 209K|00:00:02.26 | 2034K| | | |
| 3 | NESTED LOOPS | | 213K| 1 | 209K|00:00:02.07 | 2034K| | | |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED | GL_IMPORT_REFERENCES | 213K| 1 | 209K|00:00:00.74 | 821K| | | |
|* 5 | INDEX RANGE SCAN | GL_IMPORT_REFERENCES_N1 | 213K| 2 | 210K|00:00:00.49 | 611K| 1025K| 1025K| |
| 6 | PARTITION LIST ALL | | 209K| 1 | 209K|00:00:01.26 | 1213K| | | |
|* 7 | INDEX RANGE SCAN | XLA_AE_LINES_N4 | 636K| 1 | 209K|00:00:01.13 | 1213K| 1025K| 1025K| |
| 8 | NESTED LOOPS | | 1 | 53551 | 68483 |00:00:02.99 | 2317K| | | |
| 9 | VIEW | | 1 | 53551 | 68483 |00:00:02.82 | 2180K| | | |
| 10 | HASH GROUP BY | | 1 | 53551 | 68483 |00:00:02.81 | 2180K| 7196K| 3031K| 11M (0)|
| 11 | NESTED LOOPS | | 1 | 53551 | 213K|00:00:00.37 | 145K| | | |
| 12 | NESTED LOOPS | | 1 | 53586 | 213K|00:00:00.09 | 46246 | | | |
| 13 | TABLE ACCESS BY INDEX ROWID BATCHED| GL_CODE_COMBINATIONS | 1 | 458 | 13875 |00:00:00.03 | 10257 | | | |
|* 14 | INDEX SKIP SCAN | GL_CODE_COMBINATION_N16 | 1 | 458 | 13875 |00:00:00.01 | 106 | 1025K| 1025K| |
|* 15 | INDEX RANGE SCAN | GL_JE_LINES_N1 | 13875 | 117 | 213K|00:00:00.07 | 35989 | 1025K| 1025K| |
| 16 | TABLE ACCESS BY INDEX ROWID | GL_JE_LINES | 213K| 117 | 213K|00:00:00.22 | 99416 | | | |
|* 17 | INDEX UNIQUE SCAN | GL_JE_HEADERS_U1 | 68483 | 1 | 68483 |00:00:00.15 | 136K| 1025K| 1025K| |
|* 18 | TABLE ACCESS BY INDEX ROWID | GL_JE_HEADERS | 68483 | 1 | 1509 |00:00:00.13 | 67487 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=1)
4 - filter(("GIR"."GL_SL_LINK_ID" IS NOT NULL AND "GIR"."GL_SL_LINK_TABLE" IS NOT NULL))
5 - access("GIR"."JE_HEADER_ID"=:B1 AND "GIR"."JE_LINE_NUM"=:B2)
7 - access("GIR"."GL_SL_LINK_ID"="XAL"."GL_SL_LINK_ID" AND "GIR"."GL_SL_LINK_TABLE"="XAL"."GL_SL_LINK_TABLE")
14 - access("GCCS"."SEGMENT3"='220202010301' AND "GCCS"."SEGMENT1"='300')
filter(("GCCS"."SEGMENT3"='220202010301' AND "GCCS"."SEGMENT1"='300'))
15 - access("GJLS"."CODE_COMBINATION_ID"="GCCS"."CODE_COMBINATION_ID" AND "GJLS"."PERIOD_NAME">='2011-07')
17 - access("X"."JE_HEADER_ID"="GJH"."JE_HEADER_ID")
18 - filter(("X"."PERIOD_NAME"="GJH"."PERIOD_NAME" AND "GJH"."LEDGER_ID"=2022 AND (INTERNAL_FUNCTION("GJH"."JE_SOURCE") OR
("GJH"."JE_SOURCE"='Payables' AND "JE_LINE_NUM" IS NULL)) AND "GJH"."DEFAULT_EFFECTIVE_DATE"<=TO_DATE(' 2022-09-30 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "GJH"."PERIOD_NAME">='2011-07'))3秒左右跑完，仔细分析上面的所有的写法，发现还能进一步优化
于是在LEFT JOIN写法的基础上进一步优化，改写为NOT EXISTS + NO_UNNEST
SELECT DEFAULT_EFFECTIVE_DATE,
ATTRIBUTE1,
CURRENCY_CODE,
CURRENCY_CONVERSION_RATE,
JE_BATCH_ID,
JE_HEADER_ID,
LEDGER_ID,
JE_SOURCE,
JE_CATEGORY,
NAME
FROM (with x as (select PERIOD_NAME, JE_HEADER_ID, JE_LINE_NUM
from GL_JE_LINES GJLS
WHERE GJLS.CODE_COMBINATION_ID IN
(SELECT GCCS.CODE_COMBINATION_ID
FROM GL_CODE_COMBINATIONS GCCS
WHERE GCCS.SEGMENT3 = '220202010301'
AND GCCS.SEGMENT1 = NVL('300', GCCS.SEGMENT1))
group by PERIOD_NAME, JE_HEADER_ID, JE_LINE_NUM)
SELECT GJH.DEFAULT_EFFECTIVE_DATE,
GJH.ATTRIBUTE1,
GJH.CURRENCY_CODE,
GJH.CURRENCY_CONVERSION_RATE,
GJH.JE_BATCH_ID,
GJH.JE_HEADER_ID,
GJH.LEDGER_ID,
GJH.JE_SOURCE,
GJH.JE_CATEGORY,
GJH.NAME,
ROW_NUMBER() OVER(PARTITION BY GJH.JE_HEADER_ID ORDER BY GJH.ROWID) RN
FROM GL_JE_HEADERS GJH, X
WHERE GJH.PERIOD_NAME >= '2011-07'
AND GJH.STATUS = DECODE('A', 'P', 'P', GJH.STATUS)
AND GJH.LEDGER_ID = 2022
AND GJH.DEFAULT_EFFECTIVE_DATE <=
TO_DATE('2022-09-30', 'YYYY-MM-DD')
AND GJH.PERIOD_NAME = X.PERIOD_NAME
AND GJH.JE_HEADER_ID = X.JE_HEADER_ID
AND (GJH.JE_SOURCE IN ('Manual', 'AutoCopy', 'Revaluation') OR (GJH.JE_SOURCE IN ('Payables') AND NOT EXISTS
(SELECT /*+ no_unnest */ NULL
FROM GL_IMPORT_REFERENCES GIR
WHERE
EXISTS
(SELECT NULL
FROM XLA.XLA_AE_LINES XAL
WHERE GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE)
AND X.JE_HEADER_ID = GIR.JE_HEADER_ID
AND X.JE_LINE_NUM = GIR.JE_LINE_NUM)))
)
WHERE RN = 1;---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1509 |00:00:02.10 | 1347K| | | |
|* 1 | VIEW | | 1 | 15 | 1509 |00:00:02.10 | 1347K| | | |
|* 2 | WINDOW NOSORT | | 1 | 15 | 3577 |00:00:02.10 | 1347K| 73728 | 73728 | |
| 3 | SORT GROUP BY | | 1 | 15 | 3577 |00:00:02.10 | 1347K| 832K| 832K| 739K (0)|
|* 4 | FILTER | | 1 | | 3577 |00:00:01.31 | 1347K| | | |
| 5 | NESTED LOOPS | | 1 | 236 | 213K|00:00:00.88 | 493K| | | |
| 6 | NESTED LOOPS | | 1 | 53551 | 213K|00:00:00.59 | 398K| | | |
| 7 | NESTED LOOPS | | 1 | 53551 | 213K|00:00:00.29 | 145K| | | |
| 8 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 1 | 458 | 13875 |00:00:00.03 | 10257 | | | |
|* 9 | INDEX SKIP SCAN | GL_CODE_COMBINATION_N16 | 1 | 458 | 13875 |00:00:00.01 | 106 | 1025K| 1025K| |
| 10 | TABLE ACCESS BY INDEX ROWID | GL_JE_LINES | 13875 | 117 | 213K|00:00:00.34 | 135K| | | |
|* 11 | INDEX RANGE SCAN | GL_JE_LINES_N1 | 13875 | 117 | 213K|00:00:00.07 | 35989 | 1025K| 1025K| |
|* 12 | INDEX UNIQUE SCAN | GL_JE_HEADERS_U1 | 213K| 1 | 213K|00:00:00.24 | 252K| 1025K| 1025K| |
|* 13 | TABLE ACCESS BY INDEX ROWID | GL_JE_HEADERS | 213K| 1 | 213K|00:00:00.23 | 95282 | | | |
| 14 | NESTED LOOPS SEMI | | 157K| 1 | 157K|00:00:01.07 | 854K| | | |
|* 15 | TABLE ACCESS BY INDEX ROWID BATCHED| GL_IMPORT_REFERENCES | 157K| 1 | 157K|00:00:00.42 | 544K| | | |
|* 16 | INDEX RANGE SCAN | GL_IMPORT_REFERENCES_N1 | 157K| 2 | 157K|00:00:00.27 | 386K| 1025K| 1025K| |
| 17 | PARTITION LIST ALL | | 157K| 1095K| 157K|00:00:00.28 | 310K| | | |
|* 18 | INDEX RANGE SCAN | XLA_AE_LINES_N4 | 157K| 1095K| 157K|00:00:00.23 | 310K| 1025K| 1025K| |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"=1)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "GJH"."JE_HEADER_ID" ORDER BY ROWID)<=1)
4 - filter((INTERNAL_FUNCTION("GJH"."JE_SOURCE") OR ("GJH"."JE_SOURCE"='Payables' AND IS NULL)))
9 - access("GCCS"."SEGMENT3"='220202010301' AND "GCCS"."SEGMENT1"='300')
filter(("GCCS"."SEGMENT3"='220202010301' AND "GCCS"."SEGMENT1"='300'))
11 - access("GJLS"."CODE_COMBINATION_ID"="GCCS"."CODE_COMBINATION_ID" AND "PERIOD_NAME">='2011-07')
12 - access("GJH"."JE_HEADER_ID"="JE_HEADER_ID")
13 - filter(("GJH"."PERIOD_NAME"="PERIOD_NAME" AND "GJH"."LEDGER_ID"=2022 AND "GJH"."DEFAULT_EFFECTIVE_DATE"<=TO_DATE(' 2022-09-30 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "GJH"."PERIOD_NAME">='2011-07'))
15 - filter(("GIR"."GL_SL_LINK_ID" IS NOT NULL AND "GIR"."GL_SL_LINK_TABLE" IS NOT NULL))
16 - access("GIR"."JE_HEADER_ID"=:B1 AND "GIR"."JE_LINE_NUM"=:B2)
18 - access("GIR"."GL_SL_LINK_ID"="XAL"."GL_SL_LINK_ID" AND "GIR"."GL_SL_LINK_TABLE"="XAL"."GL_SL_LINK_TABLE")
最终SQL2秒能跑完
优化完SQL之后，与EBS开发沟通，让EBS开发也从业务角度考虑怎么优化，而不仅仅只是DBA来优化
经过分析业务逻辑，开发增加了一个过滤条件，在改写的SQL上进一步做了优化，直接秒杀
搞优化还是得从架构，业务，模型，表设计入手这才是王道，其次是SQL改写，最次是建索引，加HINT
类似资料：