Hi All,
Oracle version 11.2.0
I have query , ran for one parameter (returning 1 row only).
PLAN_TABLE_OUTPUT
SQL_ID 524mfs1pxdakd, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */
a.*,
ROWNUM seq_num,
Ceil (ROWNUM/25000) * 10 seq_batch_id,
' ' process_flag
FROM
(
SELECT DISTINCT
CASE
WHEN rct.org_id = 82 THEN rct.trx_number
ELSE rct.interface_header_attribute1
END externalid,
CASE
WHEN rct.org_id = 82 THEN rct.trx_number
ELSE rct.interface_header_attribute1
END document_number,
(
SELECT hca.account_number
FROM XXC_cust_header_extract hca
WHERE rct.bill_to_customer_id=hca.cust_account_id) customer,
(
SELECT CASE
WHEN ct_prev.org_id = 82 THEN ct_prev.trx_number
ELSE ct_prev.interface_header_attribute1
END
FROM ra_customer_trx_all ct_prev
WHERE rct.previous_customer_trx_id = ct_prev.customer_trx_id) applying_to_ar_inv,
2 subsidary,
rct.trx_date ,
to_char(to_date(apsa.gl_date, 'DD-MON-YY'), 'Mon YYYY') posting_period,
rctl.description memo,
1 qty,
rctl.extended_amount amount,
rcta.name CM_TYPE,
ar_coa1.ns_subsidiary_ext_id HEADER_SUBSIDIARY,
ar_coa1.ns_division_ext_id HEADER_DIVISION,
ar_coa1.ns_dept_ext_id HEADER_DEPARTMENT,
ar_coa1.ns_office_ext_id HEADER_OFFICE,
ar_coa1.ns_revenue_ext_id HEADER_REVENUE,
ar_coa1.ns_region_ext_id HEADER_REGION,
ar_coa1.ns_program_ext_id HEADER_PROGRAM,
ar_coa1.ns_natural_acount_no HEADER_NATURAL_ACCOUNT,
ar_coa.ns_subsidiary_ext_id ITEM_SUBSIDIARY,
ar_coa.ns_division_ext_id ITEM_DIVISION,
ar_coa.ns_dept_ext_id ITEM_DEPARTMENT,
ar_coa.ns_office_ext_id ITEM_OFFICE,
ar_coa.ns_revenue_ext_id ITEM_REVENUE,
ar_coa.ns_region_ext_id ITEM_REGION,
ar_coa.ns_program_ext_id ITEM_PROGRAM,
ar_coa.ns_natural_acount_no ITEM_NATURAL_ACCOUNT,
ar_coa.old_seg6 NS_ITEM_ID
FROM XXC_INV_HEADERS_EXTRACT RCT,
XXC_INV_TYPES_EXTRACT RCTA,
XXC_INV_LINES_EXTRACT RCTL,
XXC_INV_PAYSCHUEDULES_EXTRACT APSA ,
XXC_COA_LINE_EXTRACT ar_coa,
XXC_COA_HEADER_EXTRACT ar_coa1,
fnd_lookup_values_vl flv
WHERE 1=1
AND rct.cust_trx_type_id = rcta.cust_trx_type_id
AND rct.org_id = rcta.org_id
AND ar_coa.customer_trx_line_id(+) = rctl.customer_trx_line_id
AND ar_coa1.customer_trx_id(+) = rct.customer_trx_id
AND rct.customer_trx_id = rctl.customer_trx_id
AND rct.customer_trx_id = apsa.customer_trx_id
AND flv.lookup_type = 'INV/CM'
AND flv.lookup_code = 'CM'
AND flv.lookup_code = apsa.class
AND flv.enabled_flag = 'Y'
AND Nvl(flv.end_date_active, SYSDATE + 1) >= SYSDATE
AND rct.interface_header_attribute1='123456'
)a;
Plan hash value: 3884935335
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:12.54 | 64872 | | | |
|* 1 | TABLE ACCESS FULL | XXC_CUST_HEADER_EXTRACT | 1 | 615 | 1 |00:00:00.02 | 2087 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 1 | 1 | 1 |00:00:00.01 | 4 | | | |
|* 3 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 4 | COUNT | | 1 | | 1 |00:00:12.54 | 64872 | | | |
| 5 | VIEW | | 1 | 1 | 1 |00:00:12.54 | 64872 | | | |
| 6 | HASH UNIQUE | | 1 | 1 | 1 |00:00:12.54 | 64872 | 759K| 759K| 480K (0)|
|* 7 | HASH JOIN OUTER | | 1 | 1 | 1 |00:00:12.52 | 62781 | 805K| 805K| 410K (0)|
| 8 | NESTED LOOPS | | 1 | 1 | 1 |00:00:08.29 | 57888 | | | |
| 9 | NESTED LOOPS | | 1 | 8 | 1 |00:00:08.29 | 57887 | | | |
|* 10 | HASH JOIN OUTER | | 1 | 1 | 1 |00:00:08.29 | 57884 | 870K| 870K| 410K (0)|
|* 11 | HASH JOIN | | 1 | 1 | 1 |00:00:04.02 | 55576 | 885K| 885K| 410K (0)|
|* 12 | HASH JOIN | | 1 | 1 | 1 |00:00:04.02 | 55574 | 908K| 908K| 410K (0)|
|* 13 | TABLE ACCESS FULL | XXC_INV_HEADERS_EXTRACT | 1 | 1 | 1 |00:00:00.40 | 31677 | | | |
| 14 | MERGE JOIN | | 1 | 41656 | 105K|00:00:02.93 | 23897 | | | |
|* 15 | TABLE ACCESS FULL | XXC_INV_PAYSCHUEDULES_EXTRACT | 1 | 168K| 105K|00:00:00.60 | 23893 | | | |
|* 16 | SORT JOIN | | 105K| 1 | 105K|00:00:00.76 | 4 | 2048 | 2048 | 2048 (0)|
|* 17 | TABLE ACCESS BY INDEX ROWID| FND_LOOKUP_VALUES | 1 | 1 | 1 |00:00:00.01 | 4 | | | |
|* 18 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 19 | TABLE ACCESS FULL | XXC_INV_TYPES_EXTRACT | 1 | 22 | 22 |00:00:00.01 | 2 | | | |
| 20 | TABLE ACCESS FULL | XXC_COA_HEADER_EXTRACT | 1 | 597K| 500K|00:00:01.15 | 2308 | | | |
|* 21 | INDEX RANGE SCAN | XXC_INVLINE_ID | 1 | 8 | 1 |00:00:00.01 | 3 | | | |
| 22 | TABLE ACCESS BY INDEX ROWID | XXC_INV_LINES_EXTRACT | 1 | 8 | 1 |00:00:00.01 | 1 | | | |
| 23 | TABLE ACCESS FULL | XXC_COA_LINE_EXTRACT | 1 | 562K| 496K|00:00:01.16 | 4893 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("HCA"."CUST_ACCOUNT_ID"=:B1)
3 - access("CT_PREV"."CUSTOMER_TRX_ID"=:B1)
7 - access("AR_COA"."CUSTOMER_TRX_LINE_ID"="RCTL"."CUSTOMER_TRX_LINE_ID")
10 - access("AR_COA1"."CUSTOMER_TRX_ID"="RCT"."CUSTOMER_TRX_ID")
11 - access("RCT"."CUST_TRX_TYPE_ID"="RCTA"."CUST_TRX_TYPE_ID" AND "RCT"."ORG_ID"="RCTA"."ORG_ID")
12 - access("RCT"."CUSTOMER_TRX_ID"="APSA"."CUSTOMER_TRX_ID")
13 - filter("RCT"."INTERFACE_HEADER_ATTRIBUTE1"='011700020281-62-12313816-C')
15 - filter(("APSA"."CLASS"='CM' AND "APSA"."CUSTOMER_TRX_ID" IS NOT NULL))
16 - access("B"."LOOKUP_CODE"="APSA"."CLASS")
filter("B"."LOOKUP_CODE"="APSA"."CLASS")
17 - filter(("B"."ENABLED_FLAG"='Y' AND NVL("B"."END_DATE_ACTIVE",SYSDATE@!+1)>=SYSDATE@!))
18 - access("B"."LOOKUP_TYPE"='INV/CM' AND "B"."LOOKUP_CODE"='CM' AND "B"."LANGUAGE"=USERENV('LANG'))
filter(("B"."LOOKUP_CODE"='CM' AND "B"."LANGUAGE"=USERENV('LANG')))
21 - access("RCT"."CUSTOMER_TRX_ID"="RCTL"."CUSTOMER_TRX_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Can you please help me on this?
Thanks