Exadata: Function Based Index Not Used With OUTER JOIN And View
Last updated on JANUARY 08, 2014
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 and later
Oracle Exadata Hardware - Version 126.96.36.199 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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms