0 Replies Latest reply on Jul 2, 2013 8:18 PM by Pavan_Venkata

    Query Performance AP_INVOICES_ALL vs AP_INVOICES in R12

    Pavan_Venkata

      Hi,

       

      I came across a strange (for me so far) that the query based on the synonym AP_INVOICES is faster than AP_INVOICES_ALL in R12. Please read through.

       

      -- First view

      create or replace view XX_AP_TEMP_V4

      as

      (

      select

                AI.INVOICE_AMOUNT INVOICE_AMOUNT,

                AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE,

                AI.INVOICE_DATE INVOICE_DATE,

                AI.INVOICE_ID INVOICE_ID,

                AI.INVOICE_NUM INVOICE_NUM,

                AI.org_id,

                AP_INVOICES_PKG.GET_APPROVAL_STATUS (AI.INVOICE_ID,

                                                     AI.INVOICE_AMOUNT,

                                                     AI.PAYMENT_STATUS_FLAG,

                                                     AI.INVOICE_TYPE_LOOKUP_CODE) approval_status

      from ap_invoices_all ai

      , apps.mo_glob_org_access_tmp mg

      where

      ai.org_id = mg.organization_id

      );

       

      -- Second view

      create or replace view XX_AP_TEMP_V5

      as

      (

      select

                AI.INVOICE_AMOUNT INVOICE_AMOUNT,

                AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE,

                AI.INVOICE_DATE INVOICE_DATE,

                AI.INVOICE_ID INVOICE_ID,

                AI.INVOICE_NUM INVOICE_NUM,

                AI.org_id,

                AP_INVOICES_PKG.GET_APPROVAL_STATUS (AI.INVOICE_ID,

                                                     AI.INVOICE_AMOUNT,

                                                     AI.PAYMENT_STATUS_FLAG,

                                                     AI.INVOICE_TYPE_LOOKUP_CODE) approval_status

      from ap_invoices ai

      , apps.mo_glob_org_access_tmp mg

      where

      ai.org_id = mg.organization_id

      );

       

       

      select * from XX_AP_TEMP_V4 where approval_status like '%APPROVE%'; -- takes about 6mins to give first 50 rows

       

      select * from XX_AP_TEMP_V5 where approval_status like '%APPROVE%' -- takes about 10sec to give first 50 rows

       

      Why there is such a difference when we can't create any indexes on Synonym or View??

       

      Thanks,

      Pavan.