Skip to Main Content

E-Business Suite

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Getting high cost when query multiple views with join

Fendy FendyMar 14 2024

Hi Gurus,

I have created 3 views XXAP_INVOICE_V2, XXAP_INVOICE_LINE_V2, XXAP_INVOICE_DISTRIBUTION_V2

CREATE OR REPLACE FORCE VIEW APPS.XXAP_INVOICE_V2
AS
SELECT
AI.INVOICE_ID,
TRUNC (AI.CREATION_DATE) CREATION_DATE,
AI.VENDOR_ID,
AI.INVOICE_NUM,
AI.SET_OF_BOOKS_ID,
AI.INVOICE_CURRENCY_CODE invoice_currency,
AI.INVOICE_AMOUNT,
AI.VENDOR_SITE_ID,
AI.AMOUNT_PAID,
AI.PAY_CURR_INVOICE_AMOUNT,
AI.INVOICE_TYPE_LOOKUP_CODE invoice_type,
AI.INVOICE_DATE,
AI.ORG_ID,
AI.GL_DATE,
AI.WFAPPROVAL_STATUS APPROVAL_STATUS,
HP.PARTY_NAME TRADING_PARTNER,
PV.SEGMENT1 SUPPLIER_NUMBER,
PVS.VENDOR_SITE_CODE SUPPLIER_SITE,
GCC.CONCATENATED_SEGMENTS LIABILTY_GL_ACCOUNT,
GCC.SEGMENT1 LIABILITY_COMPANY,
GCC.SEGMENT2 LIABILITY_ACCOUNT,
GCC.SEGMENT3 LIABILITY_PRODUCT,
GCC.SEGMENT4 LIABILITY_REGION,
GCC.SEGMENT5 LIABILITY_DEPT,
GCC.SEGMENT6 LIABILITY_RELATED_PARTY,
GCC.SEGMENT7 LIABILITY_RESERVED
FROM AP_INVOICES_ALL AI,
HZ_PARTIES HP,
AP_SUPPLIERS PV,
AP_SUPPLIER_SITES_ALL PVS,
GL_CODE_COMBINATIONS_KFV GCC,
FND_USER C,
FND_USER L,
AP_TERMS AT
WHERE AI.PARTY_ID = HP.PARTY_ID
AND AI.VENDOR_ID = PV.VENDOR_ID(+)
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND AI.ACCTS_PAY_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND AI.CREATED_BY = C.USER_ID
AND AI.LAST_UPDATED_BY = L.USER_ID
AND AI.TERMS_ID = AT.TERM_ID
AND gl_security_pkg.validate_access (
fnd_profile.VALUE ('GL_SET_OF_BKS_ID'),
ai.ACCTS_PAY_CODE_COMBINATION_ID) = 'TRUE';

CREATE OR REPLACE FORCE VIEW APPS.XXAP_INVOICE_LINE_V2
AS
SELECT
AIL.INVOICE_ID,
AIL.LINE_NUMBER,
AIL.LINE_TYPE_LOOKUP_CODE LINE_TYPE,
AIL.DESCRIPTION LINE_DESCRIPTION,
AIL.LINE_SOURCE,
AIL.ORG_ID,
AIL.LINE_GROUP_NUMBER,
AIL.DEFERRED_ACCTG_FLAG DEFERRED_COST,
AIL.DEF_ACCTG_START_DATE DEFERRED_START_DATE,
AIL.DEF_ACCTG_END_DATE DEFERRED_END_DATE,
AIL.SET_OF_BOOKS_ID,
AIL.AMOUNT,
(SELECT SEGMENT1
FROM PA_PROJECTS_ALL
WHERE PROJECT_ID = AIL.PROJECT_ID)
PROJECT_CODE,
(SELECT SEGMENT1
FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID = AIL.PO_HEADER_ID)
PO_NUMBER,
(SELECT pol.LINE_NUM
FROM po_lines_all pol
WHERE ail.po_header_id = pol.po_header_id
AND ail.PO_LINE_ID = pol.po_line_id)
PO_LINE_NUMBER
FROM AP_INVOICE_LINES_ALL AIL,
GL_CODE_COMBINATIONS_KFV GCC
WHERE 1 = 1
AND AIL.DEFAULT_DIST_CCID = GCC.CODE_COMBINATION_ID(+)

CREATE OR REPLACE FORCE VIEW APPS.XXAP_INVOICE_DISTRIBUTION_V2
AS
SELECT aid.set_of_books_id,
aid.accounting_date,
aid.accrual_posted_flag,
aid.assets_addition_flag,
aid.distribution_line_number dist_line,
aid.dist_code_combination_id,
gcc.concatenated_segments dist_gl_account,
gcc.segment1 dist_company,
gcc.segment2 dist_account,
gcc.segment3 dist_product,
gcc.segment4 dist_region,
gcc.segment5 dist_dept,
gcc.segment6 dist_related_party,
gcc.segment7 dist_reserved,
aid.invoice_id,
TRUNC (aid.last_update_date) last_update_date,
c.user_name created_by,
TRUNC (aid.creation_date) creation_date,
l.user_name last_updated_by,
aid.line_type_lookup_code line_type,
TO_CHAR (aid.accounting_date, 'MON-RR') period_name,
aid.amount,
NVL (aid.base_amount, aid.amount) base_amount,
aid.description dist_description,
aid.reversal_flag,
aid.invoice_distribution_id,
aid.invoice_line_number invoice_line
FROM ap_invoice_distributions_all aid,
gl_code_combinations_kfv gcc,
fnd_user c,
fnd_user l
WHERE 1 = 1
AND aid.dist_code_combination_id = gcc.code_combination_id(+)
AND aid.created_by = c.user_id
AND aid.last_updated_by = l.user_id
AND ( (NVL (aid.base_amount, aid.amount) <> 0)
OR ( NVL (aid.base_amount, aid.amount) = 0
AND aid.po_distribution_id IS NOT NULL
AND aid.line_type_lookup_code != 'NONREC_TAX'));

When execute following query :

SELECT ai.LIABILITY_COMPANY AS COL0
,ai.TRADING_PARTNER AS COL1
,ai.INVOICE_TYPE AS COL2
,ai.INVOICE_NUM AS COL3
,ai.INVOICE_DATE AS COL4
,ail.LINE_TYPE AS COL5
,aid.INVOICE_LINE AS COL6
,aid.DIST_LINE AS COL7
,ai.INVOICE_CURRENCY AS COL8
,SUM(aid.AMOUNT) AS COL9
,SUM(aid.BASE_AMOUNT) AS COL10
,aid.DIST_GL_ACCOUNT AS COL11
,aid.DIST_DESCRIPTION AS COL12
,aid.PERIOD_NAME AS COL13
--,ail.TAX_CLASSIFICATION_CODE AS COL14
,ail.PROJECT_CODE AS COL15
,ail.DEFERRED_COST AS COL16
,ail.DEFERRED_START_DATE AS COL17
,ail.DEFERRED_END_DATE AS COL18
,ail.PO_NUMBER AS COL19
,ail.PO_LINE_NUMBER AS COL20
,aid.INVOICE_DISTRIBUTION_ID AS COL21
FROM XXAP_INVOICE_V2 AI,
XXAP_INVOICE_LINE_V2 AIL,
XXAP_INVOICE_DISTRIBUTION_V2 AID
WHERE 1=1
AND AI.LIABILITY_COMPANY = 'SPENN'
AND AI.CREATION_DATE >= to_date('01-MAR-23', 'DD-MON-RR')
AND AI.CREATION_DATE <= to_date('31-MAR-23', 'DD-MON-RR')
AND ail.invoice_id = ai.invoice_id
AND (aid.INVOICE_ID = ail.INVOICE_ID
AND aid.INVOICE_LINE = ail.LINE_NUMBER)
GROUP BY ai.LIABILITY_COMPANY
,ai.TRADING_PARTNER
,ai.INVOICE_TYPE
,ai.INVOICE_NUM
,ai.INVOICE_DATE
,ail.LINE_TYPE
,aid.INVOICE_LINE
,aid.DIST_LINE
,ai.INVOICE_CURRENCY
,aid.DIST_GL_ACCOUNT
,aid.DIST_DESCRIPTION
,aid.PERIOD_NAME
--,ail.TAX_CLASSIFICATION_CODE AS COL14
,ail.PROJECT_CODE
,ail.DEFERRED_COST
,ail.DEFERRED_START_DATE
,ail.PO_NUMBER
,ail.DEFERRED_END_DATE
,ail.PO_LINE_NUMBER
,aid.INVOICE_DISTRIBUTION_ID

explain plain in TOAD showing high cost on AP_INVOICE_LINES_ALL full table scan

Is there any way i can tune this further?

Regards,

Fendy

Comments
Post Details
Added on Mar 14 2024
0 comments
103 views