Exadata: Function Based Index Not Used With OUTER JOIN And View (Doc ID 1606874.1)

Last updated on JANUARY 08, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Oracle Exadata Hardware - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

 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.

Example:

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.

Cause

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