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
from ap_invoices_all ai
, apps.mo_glob_org_access_tmp mg
ai.org_id = mg.organization_id
-- Second view
create or replace view XX_AP_TEMP_V5
from ap_invoices ai
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??