Exadata: Function Based Index Not Used With OUTER JOIN And View
(Doc ID 1606874.1)
Last updated on AUGUST 18, 2020
Applies to:Oracle Exadata Hardware - Version 18.104.22.168 and later
Oracle Database - Enterprise Edition - Version 22.214.171.124 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Oracle optimizer doesn't use function based index and instead goes for full table scan when outer join is used on a view which selects from the table.
But if the table is used directly instead of the view, then index scan is preferred.
An employees table has an index:
create index emp_fx on employees(case emp_type when 'YES' then id end)
That same employee table has an unconditional view:
create view v_employees as select * from employees
The following query uses the base table and the plan show the index:
select * from salary s left outer join employees e ON case e.emp_type when 'YES' then e.id end = s.id
This other query uses the view, but the plan do not show the index:
select * from salary s left outer join v_employees e ON case e.emp_type when 'YES' then e.id end = s.id
This query uses the view and the plan show the index:
select * from salary s inner join v_employees e ON case e.emp_type when 'YES' then e.id end = s.id
The testcase attached to this document easily reproduces the problem.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!