Hi,
I trying to execute one select statement and it is taking so much of time.
Can anyone please check and improve the performance of the below SQL?
SELECT
RP_INSTRUMENT_BO.PRODUCT_SDESC,
RP_INSTRUMENT_BO.PRODUCT_TYPE_SDESC,
RP_INSTRUMENT_BO.INSTRUMENT_ID,
DECODE(RP_TERMS_BO.PURPOSE_CODE,NULL,RP_TERMS_BO.A_ADV_PURPOSE_TYPE,RP_TERMS_BO.PURPOSE_CODE),
decode(RP_TERMS_BO.A_OUR_CONFIRM_TYPE,'ACON', 'Y', 'SCON', 'Y','N'),
RP_INSTRUMENT_BO.A_CURRENCY,
RP_INSTRUMENT_BO.AVAL_COI,
RP_INSTRUMENT_BO.A_CURRENCY_BASE,
RP_INSTRUMENT_BO.AVAL_BASE,
POS_ACTIVE.COUT_BASE,
CUST_RELATIONSHIP.CUSTOMER_ID,
CUST_RELATIONSHIP.NAME,
RCUST_RISK_COUNTRY.DESCRIPTION,
CUSTOMER_LIMIT.CUSTOMER_ID,
CUSTOMER_LIMIT.NAME,
LCUST_RISK_COUNTRY.DESCRIPTION,
RP_INSTRUMENT_BO.DATE_START,
RP_INSTRUMENT_BO.DATE_END,
USER_DEF_TERMS.USER_DEF_FIELD_2,
RP_GL_NUMBER_BO.POSTING_TYPE_SDSC,
RP_GL_NUMBER_BO.GL_NUM,
RP_GL_NUMBER_BO.LIABILITY_AMOUNT,
RP_GL_NUMBER_BO.LIABILITY_AMT_BASE,
CUSTOMER_PRT_SYND.CUSTOMER_ID,
PARTY_PART_SYND.LIABILITY_PERCENT,
CUSTOMER_PRT_SYND.NAME,
PARTY_PART_SYND.DATE_EFFECTIVE,
PARTY_TYPE_PRT_SYN.SHORT_DESCRIPTION,
CUSTOMER_PRT_SYND.A_CUSTOMER_TYPE,
PARTY_PART_SYND.PARTICIPANT_ORDER_NUM,
LIMIT_CUST_ORG.BANK_ORG_ID,
ACCOUNT_OFFICER_LM1.ACCOUNT_OFFICER_ID,
ACCOUNT_OFFICER_LM1.LAST_NAME,
ACCOUNT_OFFICER_LM1.FIRST_NAME
FROM
OTL_BOUSER A_SECURITY_INSTRUMENT,
RP_INSTRUMENT_BO,
RP_TERMS_BO,
POSITION POS_ACTIVE,
CUSTOMER CUST_RELATIONSHIP,
COUNTRY_TYPE RCUST_RISK_COUNTRY,
CUSTOMER CUSTOMER_LIMIT,
COUNTRY_TYPE LCUST_RISK_COUNTRY,
USER_DEF_TERMS,
RP_GL_NUMBER_BO,
CUSTOMER CUSTOMER_PRT_SYND,
PARTY PARTY_PART_SYND,
PARTY_TYPE PARTY_TYPE_PRT_SYN,
RP_MNGL_ORG_BO LIMIT_CUST_ORG,
ACCOUNT_OFFICER ACCOUNT_OFFICER_LM1,
OTL_BOGROUP GMT_DIFF_INSTRUMENT
WHERE
( RP_INSTRUMENT_BO.UOID=RP_GL_NUMBER_BO.P_INSTRUMENT(+) )
AND ( RP_INSTRUMENT_BO.A_TERMS_ACTIVE=RP_TERMS_BO.U_TERMS(+) )
AND ( RP_TERMS_BO.C_USER_DEF_TERMS=USER_DEF_TERMS.UOID(+) )
AND ( RP_INSTRUMENT_BO.A_CLIENT_BANK=CUSTOMER_LIMIT.A_CLIENT_BANK(+) and RP_INSTRUMENT_BO.A_CUSTOMER_LIMIT=CUSTOMER_LIMIT.UOID(+) )
AND ( RP_INSTRUMENT_BO.A_POSITION_ACTIVE=POS_ACTIVE.UOID(+) )
AND ( RP_INSTRUMENT_BO.A_CUST_RELATIONSHP=CUST_RELATIONSHIP.UOID )
AND ( CUSTOMER_LIMIT.A_COUNTRY_RISK=LCUST_RISK_COUNTRY.VALUE(+) )
AND ( CUST_RELATIONSHIP.A_COUNTRY_RISK=RCUST_RISK_COUNTRY.VALUE(+) )
AND ( GMT_DIFF_INSTRUMENT.BOGROUP=A_SECURITY_INSTRUMENT.BOGROUP )
AND ( A_SECURITY_INSTRUMENT.BOUSER='OTL-UBC-M-Management-User' )
AND ( GMT_DIFF_INSTRUMENT.CLIENT_BANK=RP_INSTRUMENT_BO.A_CLIENT_BANK )
AND ( CUSTOMER_LIMIT.A_ACCOUNT_OFFCR_1=ACCOUNT_OFFICER_LM1.UOID(+) )
AND ( LCUST_RISK_COUNTRY.A_LANGUAGE(+)='01' )
AND ( RCUST_RISK_COUNTRY.A_LANGUAGE(+)='01' )
AND ( (RP_TERMS_BO.A_DEAL_INSTRC_PRT=PARTY_PART_SYND.P_OBJECT AND RP_TERMS_BO.A_DEAL_INSTRC_PRT IS NOT NULL) OR (RP_TERMS_BO.A_DEAL_INSTRC_SYND=PARTY_PART_SYND.P_OBJECT AND RP_TERMS_BO.A_DEAL_INSTRC_SYND IS NOT NULL) )
AND ( CUSTOMER_PRT_SYND.UOID=PARTY_PART_SYND.A_CUSTOMER )
AND ( PARTY_PART_SYND.A_PARTY_TYPE=PARTY_TYPE_PRT_SYN.VALUE )
AND ( PARTY_TYPE_PRT_SYN.A_LANGUAGE='01' )
AND ( CUSTOMER_LIMIT.A_ORG_MANAGRIAL=LIMIT_CUST_ORG.UOID )
AND
(
RP_INSTRUMENT_BO.STATUS In ( 'ACT','EXP' )
AND
CASE WHEN RP_TERMS_BO.A_DEAL_INSTRC_PRT IS NOT NULL THEN 'Y' ELSE 'N' END = 'Y'
AND
RP_GL_NUMBER_BO.POSTING_TYPE_SDSC In ( 'Part Sold Cust','Usanc Part Cust' )
);
I have generated the explain plan and below is the result of it. Please check and let me know.
SQL> select * from table( dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 225713740
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31 | 19468 | | 200K (2)| 00:00:08 |
| 1 | NESTED LOOPS | | 31 | 19468 | | 200K (2)| 00:00:08 |
| 2 | NESTED LOOPS | | 31 | 19468 | | 200K (2)| 00:00:08 |
|* 3 | HASH JOIN | | 31 | 18166 | 55M| 200K (2)| 00:00:08 |
| 4 | MERGE JOIN CARTESIAN | | 103K| 54M| | 152K (1)| 00:00:06 |
|* 5 | HASH JOIN RIGHT OUTER | | 237 | 122K| | 151K (1)| 00:00:06 |
|* 6 | MAT_VIEW ACCESS FULL | COUNTRY_TYPE | 248 | 4464 | | 3 (0)| 00:00:01 |
|* 7 | HASH JOIN OUTER | | 232 | 115K| | 151K (1)| 00:00:06 |
| 8 | NESTED LOOPS | | 232 | 107K| | 151K (1)| 00:00:06 |
| 9 | NESTED LOOPS | | 233 | 107K| | 151K (1)| 00:00:06 |
|* 10 | HASH JOIN | | 233 | 102K| | 151K (1)| 00:00:06 |
|* 11 | HASH JOIN RIGHT OUTER | | 233 | 89705 | | 151K (1)| 00:00:06 |
|* 12 | MAT_VIEW ACCESS FULL | COUNTRY_TYPE | 248 | 4464 | | 3 (0)| 00:00:01 |
|* 13 | HASH JOIN | | 228 | 83676 | | 151K (1)| 00:00:06 |
| 14 | NESTED LOOPS OUTER | | 230 | 74980 | | 150K (1)| 00:00:06 |
| 15 | NESTED LOOPS OUTER | | 230 | 71300 | | 150K (1)| 00:00:06 |
|* 16 | FILTER | | | | | | |
| 17 | NESTED LOOPS OUTER | | 230 | 67390 | | 150K (1)| 00:00:06 |
|* 18 | HASH JOIN | | 230 | 59570 | | 150K (1)| 00:00:06 |
|* 19 | HASH JOIN | | 3502 | 694K| | 3142 (1)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | PK_OTL_BOUSER | 1 | 34 | | 1 (0)| 00:00:01 |
|* 21 | HASH JOIN | | 28894 | 4768K| | 3141 (1)| 00:00:01 |
| 22 | INDEX FULL SCAN | PK_OTL_BOGROUP | 12 | 252 | | 1 (0)| 00:00:01 |
| 23 | NESTED LOOPS | | 10000 | 1445K| | 3140 (1)| 00:00:01 |
| 24 | NESTED LOOPS | | 279K| 1445K| | 3140 (1)| 00:00:01 |
|* 25 | MAT_VIEW ACCESS FULL | ORGANIZATION | 11 | 143 | | 279 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | IDX_FLATTENED_INSTRUMENT_6 | 25400 | | | 79 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID | FLATTENED_INSTRUMENT | 952 | 125K| | 2067 (1)| 00:00:01 |
| 28 | VIEW | RP_GL_NUMBER_BO | 16652 | 910K| | 146K (1)| 00:00:06 |
| 29 | SORT UNIQUE | | 16652 | 2739K| 2904K| 146K (1)| 00:00:06 |
| 30 | UNION-ALL | | | | | | |
|* 31 | HASH JOIN | | 15903 | 2609K| | 10404 (1)| 00:00:01 |
| 32 | MAT_VIEW ACCESS FULL | GENERAL_LEDGER_NUM | 398 | 26268 | | 4 (0)| 00:00:01 |
|* 33 | HASH JOIN | | 15903 | 1584K| | 10400 (1)| 00:00:01 |
| 34 | INLIST ITERATOR | | | | | | |
|* 35 | INDEX RANGE SCAN | IDX_INSTRUMENT_2 | 16941 | 314K| | 73 (0)| 00:00:01 |
| 36 | NESTED LOOPS | | 18539 | 1502K| | 10327 (1)| 00:00:01 |
| 37 | NESTED LOOPS | | 38022 | 1502K| | 10327 (1)| 00:00:01 |
|* 38 | ED MAT_VIEW ACCESS BY INDEX ROWID BATCH | POSTING_TYPE | 2 | 90 | | 2 (0)| 00:00:01 |
|* 39 | INDEX SKIP SCAN | SYS_C_SNAP$_1976PK_POSTING_TY | 68 | | | 1 (0)| 00:00:01 |
|* 40 | INDEX RANGE SCAN | IDX_POSITION_1 | 19011 | | | 4495 (1)| 00:00:01 |
| 41 | MAT_VIEW ACCESS BY INDEX ROWID | POSITION | 9270 | 344K| | 5830 (1)| 00:00:01 |
|* 42 | HASH JOIN | | 320 | 53440 | | 33977 (1)| 00:00:02 |
|* 43 | MAT_VIEW ACCESS BY INDEX ROWID BATCHED | POSTING_TYPE | 2 | 90 | | 2 (0)| 00:00:01 |
|* 44 | INDEX SKIP SCAN | SYS_C_SNAP$_1976PK_POSTING_TY | 68 | | | 1 (0)| 00:00:01 |
|* 45 | HASH JOIN | | 320 | 39040 | | 33975 (1)| 00:00:02 |
| 46 | NESTED LOOPS | | 320 | 17920 | | 33971 (1)| 00:00:02 |
| 47 | NESTED LOOPS | | 16941 | 17920 | | 33971 (1)| 00:00:02 |
| 48 | INLIST ITERATOR | | | | | | |
|* 49 | INDEX RANGE SCAN | IDX_INSTRUMENT_2 | 16941 | 314K| | 73 (0)| 00:00:01 |
|* 50 | INDEX UNIQUE SCAN | SYS_C_SNAP$_2042PK_POSITION | 1 | | | 1 (0)| 00:00:01 |
|* 51 | MAT_VIEW ACCESS BY INDEX ROWID | POSITION | 1 | 37 | | 2 (0)| 00:00:01 |
| 52 | MAT_VIEW ACCESS FULL | GENERAL_LEDGER_NUM | 398 | 26268 | | 4 (0)| 00:00:01 |
|* 53 | HASH JOIN | | 108 | 22356 | | 33977 (1)| 00:00:02 |
|* 54 | HASH JOIN | | 108 | 15228 | | 33973 (1)| 00:00:02 |
|* 55 | MAT_VIEW ACCESS BY INDEX ROWID BATCHED | POSTING_TYPE | 2 | 90 | | 2 (0)| 00:00:01 |
|* 56 | INDEX SKIP SCAN | SYS_C_SNAP$_1976PK_POSTING_TY | 68 | | | 1 (0)| 00:00:01 |
| 57 | NESTED LOOPS | | 3678 | 344K| | 33971 (1)| 00:00:02 |
| 58 | NESTED LOOPS | | 16941 | 344K| | 33971 (1)| 00:00:02 |
| 59 | INLIST ITERATOR | | | | | | |
|* 60 | INDEX RANGE SCAN | IDX_INSTRUMENT_2 | 16941 | 314K| | 73 (0)| 00:00:01 |
|* 61 | INDEX UNIQUE SCAN | SYS_C_SNAP$_2042PK_POSITION | 1 | | | 1 (0)| 00:00:01 |
| 62 | MAT_VIEW ACCESS BY INDEX ROWID | POSITION | 1 | 77 | | 2 (0)| 00:00:01 |
| 63 | MAT_VIEW ACCESS FULL | GENERAL_LEDGER_NUM | 398 | 26268 | | 4 (0)| 00:00:01 |
|* 64 | HASH JOIN | | 213 | 35571 | | 33977 (1)| 00:00:02 |
|* 65 | HASH JOIN | | 213 | 21513 | | 33973 (1)| 00:00:02 |
|* 66 | MAT_VIEW ACCESS BY INDEX ROWID BATCHED | POSTING_TYPE | 2 | 90 | | 2 (0)| 00:00:01 |
|* 67 | INDEX SKIP SCAN | SYS_C_SNAP$_1976PK_POSTING_TY | 68 | | | 1 (0)| 00:00:01 |
| 68 | NESTED LOOPS | | 320 | 17920 | | 33971 (1)| 00:00:02 |
| 69 | NESTED LOOPS | | 16941 | 17920 | | 33971 (1)| 00:00:02 |
| 70 | INLIST ITERATOR | | | | | | |
|* 71 | INDEX RANGE SCAN | IDX_INSTRUMENT_2 | 16941 | 314K| | 73 (0)| 00:00:01 |
|* 72 | INDEX UNIQUE SCAN | SYS_C_SNAP$_2042PK_POSITION | 1 | | | 1 (0)| 00:00:01 |
|* 73 | MAT_VIEW ACCESS BY INDEX ROWID | POSITION | 1 | 37 | | 2 (0)| 00:00:01 |
| 74 | MAT_VIEW ACCESS FULL | GENERAL_LEDGER_NUM | 398 | 26268 | | 4 (0)| 00:00:01 |
|* 75 | HASH JOIN | | 108 | 22356 | | 33977 (1)| 00:00:02 |
|* 76 | HASH JOIN | | 108 | 15228 | | 33973 (1)| 00:00:02 |
|* 77 | MAT_VIEW ACCESS BY INDEX ROWID BATCHED | POSTING_TYPE | 2 | 90 | | 2 (0)| 00:00:01 |
|* 78 | INDEX SKIP SCAN | SYS_C_SNAP$_1976PK_POSTING_TY | 68 | | | 1 (0)| 00:00:01 |
| 79 | NESTED LOOPS | | 3678 | 344K| | 33971 (1)| 00:00:02 |
| 80 | NESTED LOOPS | | 16941 | 344K| | 33971 (1)| 00:00:02 |
| 81 | INLIST ITERATOR | | | | | | |
|* 82 | INDEX RANGE SCAN | IDX_INSTRUMENT_2 | 16941 | 314K| | 73 (0)| 00:00:01 |
|* 83 | INDEX UNIQUE SCAN | SYS_C_SNAP$_2042PK_POSITION | 1 | | | 1 (0)| 00:00:01 |
| 84 | MAT_VIEW ACCESS BY INDEX ROWID | POSITION | 1 | 77 | | 2 (0)| 00:00:01 |
| 85 | MAT_VIEW ACCESS FULL | GENERAL_LEDGER_NUM | 398 | 26268 | | 4 (0)| 00:00:01 |
| 86 | TABLE ACCESS BY INDEX ROWID | FLATTENED_TERMS | 1 | 34 | | 2 (0)| 00:00:01 |
|* 87 | INDEX UNIQUE SCAN | PK_FLATTENED_TERMS | 1 | | | 1 (0)| 00:00:01 |
| 88 | MAT_VIEW ACCESS BY INDEX ROWID | USER_DEF_TERMS | 1 | 17 | | 1 (0)| 00:00:01 |
|* 89 | INDEX UNIQUE SCAN | SYS_C_SNAP$_2025PK_USER_DEF_T | 1 | | | 0 (0)| 00:00:01 |
| 90 | MAT_VIEW ACCESS BY INDEX ROWID | POSITION | 1 | 16 | | 2 (0)| 00:00:01 |
|* 91 | INDEX UNIQUE SCAN | SYS_C_SNAP$_2042PK_POSITION | 1 | | | 1 (0)| 00:00:01 |
| 92 | MAT_VIEW ACCESS FULL | CUSTOMER | 15363 | 615K| | 182 (1)| 00:00:01 |
| 93 | MAT_VIEW ACCESS FULL | CUSTOMER | 15363 | 1005K| | 182 (1)| 00:00:01 |
|* 94 | INDEX UNIQUE SCAN | SYS_C_SNAP$_2158PK_ORGANIZATI | 1 | | | 0 (0)| 00:00:01 |
|* 95 | MAT_VIEW ACCESS BY INDEX ROWID | ORGANIZATION | 1 | 24 | | 1 (0)| 00:00:01 |
| 96 | MAT_VIEW ACCESS FULL | ACCOUNT_OFFICER | 2424 | 82416 | | 12 (0)| 00:00:01 |
| 97 | BUFFER SORT | | 440 | 9240 | | 152K (1)| 00:00:06 |
|* 98 | MAT_VIEW ACCESS FULL | PARTY_TYPE | 440 | 9240 | | 2 (0)| 00:00:01 |
|* 99 | MAT_VIEW ACCESS FULL | PARTY | 2857K| 100M| | 35573 (1)| 00:00:02 |
|*100 | INDEX UNIQUE SCAN | SYS_C_SNAP$_2363PK_CUSTOMER | 1 | | | 0 (0)| 00:00:01 |
| 101 | MAT_VIEW ACCESS BY INDEX ROWID | CUSTOMER | 1 | 42 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PARTY_PART_SYND"."A_PARTY_TYPE"="PARTY_TYPE_PRT_SYN"."VALUE")
filter("A_DEAL_INSTRC_PRT"="PARTY_PART_SYND"."P_OBJECT" AND "A_DEAL_INSTRC_PRT" IS NOT NULL OR
"A_DEAL_INSTRC_SYND"="PARTY_PART_SYND"."P_OBJECT" AND "A_DEAL_INSTRC_SYND" IS NOT NULL)
5 - access("CUSTOMER_LIMIT"."A_COUNTRY_RISK"="LCUST_RISK_COUNTRY"."VALUE"(+))
6 - filter("LCUST_RISK_COUNTRY"."A_LANGUAGE"(+)='01')
7 - access("CUSTOMER_LIMIT"."A_ACCOUNT_OFFCR_1"="ACCOUNT_OFFICER_LM1"."UOID"(+))
10 - access("FLATTENED_INSTRUMENT"."A_CLIENT_BANK"="CUSTOMER_LIMIT"."A_CLIENT_BANK" AND
"FLATTENED_INSTRUMENT"."A_CUSTOMER_LIMIT"="CUSTOMER_LIMIT"."UOID")
11 - access("CUST_RELATIONSHIP"."A_COUNTRY_RISK"="RCUST_RISK_COUNTRY"."VALUE"(+))
12 - filter("RCUST_RISK_COUNTRY"."A_LANGUAGE"(+)='01')
13 - access("FLATTENED_INSTRUMENT"."A_CUST_RELATIONSHP"="CUST_RELATIONSHIP"."UOID")
16 - filter(CASE WHEN "A_DEAL_INSTRC_PRT" IS NOT NULL THEN 'Y' ELSE 'N' END ='Y')
18 - access("FLATTENED_INSTRUMENT"."UOID"="RP_GL_NUMBER_BO"."P_INSTRUMENT")
19 - access("GMT_DIFF_INSTRUMENT"."BOGROUP"="A_SECURITY_INSTRUMENT"."BOGROUP")
20 - access("A_SECURITY_INSTRUMENT"."BOUSER"='OTL-UBC-M-Management-User')
21 - access("GMT_DIFF_INSTRUMENT"."CLIENT_BANK"="FLATTENED_INSTRUMENT"."A_CLIENT_BANK")
25 - filter("ORGANIZATION"."OPER_ORG_IND"='Y')
26 - access("FLATTENED_INSTRUMENT"."A_OPER_BK_ORG_ORIG"="UOID")
27 - filter("FLATTENED_INSTRUMENT"."STATUS"='ACT' OR "FLATTENED_INSTRUMENT"."STATUS"='EXP')
31 - access("POSITION"."A_GL_NUMBER_BASS"="GENERAL_LEDGER_NUM"."UOID")
33 - access("INSTRUMENT"."A_POSITION_ACTIVE"="POSITION"."UOID")
35 - access("INSTRUMENT"."STATUS"='ACT' OR "INSTRUMENT"."STATUS"='EXP' OR "INSTRUMENT"."STATUS"='LQB' OR "INSTRUMENT"."STATUS"='LQC')
38 - filter("POSTING_TYPE"."SHORT_DESCRIPTION"='Part Sold Cust' OR "POSTING_TYPE"."SHORT_DESCRIPTION"='Usanc Part Cust')
39 - access("POSTING_TYPE"."A_LANGUAGE"='01')
filter("POSTING_TYPE"."A_LANGUAGE"='01')
40 - access("POSITION"."A_POSTING_TYP_BASS"="POSTING_TYPE"."VALUE")
filter("POSITION"."A_GL_NUMBER_BASS" IS NOT NULL)
42 - access("POSITION"."A_POSTING_TYP_BLIP"="POSTING_TYPE"."VALUE")
43 - filter("POSTING_TYPE"."SHORT_DESCRIPTION"='Part Sold Cust' OR "POSTING_TYPE"."SHORT_DESCRIPTION"='Usanc Part Cust')
44 - access("POSTING_TYPE"."A_LANGUAGE"='01')
filter("POSTING_TYPE"."A_LANGUAGE"='01')
45 - access("POSITION"."A_GL_NUMBER_BLIP"="GENERAL_LEDGER_NUM"."UOID")
49 - access("INSTRUMENT"."STATUS"='ACT' OR "INSTRUMENT"."STATUS"='EXP' OR "INSTRUMENT"."STATUS"='LQB' OR "INSTRUMENT"."STATUS"='LQC')
50 - access("INSTRUMENT"."A_POSITION_ACTIVE"="POSITION"."UOID")
51 - filter("POSITION"."A_GL_NUMBER_BLIP" IS NOT NULL AND "POSITION"."A_POSTING_TYP_BLIP" IS NOT NULL)
53 - access("GENERAL_LEDGER_NUM"."UOID"=NVL("POSITION"."A_GL_NUMBER_BLIA",NVL("POSITION"."A_GL_NUMBER_BLIB","POSITION"."A_GL_NUMBER_BLIU")))
54 - access("POSTING_TYPE"."VALUE"=DECODE("POSITION"."A_GL_NUMBER_BLIA",NULL,DECODE("POSITION"."A_GL_NUMBER_BLIB",NULL,"POSITION"."A_POSTING
_TYP_BLIU","POSITION"."A_POSTING_TYP_BLIB"),"POSITION"."A_POSTING_TYP_BLIA"))
55 - filter("POSTING_TYPE"."SHORT_DESCRIPTION"='Part Sold Cust' OR "POSTING_TYPE"."SHORT_DESCRIPTION"='Usanc Part Cust')
56 - access("POSTING_TYPE"."A_LANGUAGE"='01')
filter("POSTING_TYPE"."A_LANGUAGE"='01')
60 - access("INSTRUMENT"."STATUS"='ACT' OR "INSTRUMENT"."STATUS"='EXP' OR "INSTRUMENT"."STATUS"='LQB' OR "INSTRUMENT"."STATUS"='LQC')
61 - access("INSTRUMENT"."A_POSITION_ACTIVE"="POSITION"."UOID")
64 - access("POSITION"."A_GL_NUMBER_CLIP"="GENERAL_LEDGER_NUM"."UOID")
65 - access("POSITION"."A_POSTING_TYP_CLIP"="POSTING_TYPE"."VALUE")
66 - filter("POSTING_TYPE"."SHORT_DESCRIPTION"='Part Sold Cust' OR "POSTING_TYPE"."SHORT_DESCRIPTION"='Usanc Part Cust')
67 - access("POSTING_TYPE"."A_LANGUAGE"='01')
filter("POSTING_TYPE"."A_LANGUAGE"='01')
71 - access("INSTRUMENT"."STATUS"='ACT' OR "INSTRUMENT"."STATUS"='EXP' OR "INSTRUMENT"."STATUS"='LQB' OR "INSTRUMENT"."STATUS"='LQC')
72 - access("INSTRUMENT"."A_POSITION_ACTIVE"="POSITION"."UOID")
73 - filter("POSITION"."A_GL_NUMBER_CLIP" IS NOT NULL AND "POSITION"."A_POSTING_TYP_CLIP" IS NOT NULL)
75 - access("GENERAL_LEDGER_NUM"."UOID"=NVL("POSITION"."A_GL_NUMBER_CLIA",NVL("POSITION"."A_GL_NUMBER_CLIB","POSITION"."A_GL_NUMBER_CLIU")))
76 - access("POSTING_TYPE"."VALUE"=DECODE("POSITION"."A_GL_NUMBER_CLIA",NULL,DECODE("POSITION"."A_GL_NUMBER_CLIB",NULL,"POSITION"."A_POSTING
_TYP_CLIU","POSITION"."A_POSTING_TYP_CLIB"),"POSITION"."A_POSTING_TYP_CLIA"))
77 - filter("POSTING_TYPE"."SHORT_DESCRIPTION"='Part Sold Cust' OR "POSTING_TYPE"."SHORT_DESCRIPTION"='Usanc Part Cust')
78 - access("POSTING_TYPE"."A_LANGUAGE"='01')
filter("POSTING_TYPE"."A_LANGUAGE"='01')
82 - access("INSTRUMENT"."STATUS"='ACT' OR "INSTRUMENT"."STATUS"='EXP' OR "INSTRUMENT"."STATUS"='LQB' OR "INSTRUMENT"."STATUS"='LQC')
83 - access("INSTRUMENT"."A_POSITION_ACTIVE"="POSITION"."UOID")
87 - access("FLATTENED_INSTRUMENT"."A_TERMS_ACTIVE"="U_TERMS"(+))
89 - access("C_USER_DEF_TERMS"="USER_DEF_TERMS"."UOID"(+))
91 - access("FLATTENED_INSTRUMENT"."A_POSITION_ACTIVE"="POS_ACTIVE"."UOID"(+))
94 - access("CUSTOMER_LIMIT"."A_ORG_MANAGRIAL"="UOID")
95 - filter("ORGANIZATION"."BANK_ORG_TYPE"='M')
98 - filter("PARTY_TYPE_PRT_SYN"."A_LANGUAGE"='01')
99 - filter("PARTY_PART_SYND"."A_CUSTOMER" IS NOT NULL)
100 - access("CUSTOMER_PRT_SYND"."UOID"="PARTY_PART_SYND"."A_CUSTOMER")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
- 7 Sql Plan Directives used for this statement
185 rows selected.