Oracle 12c - view plans access unnecessary tables

Question

I am hoping for an explanation of some view + optimizer behavior

lets make a view like this

select e.employee_name, e.employee_active, ei.marital_status
    from employees e
    left join employee_info ei on ei.employee_id = e.employee_id

Then lets access it like this

select distinct employee_name from employee_view where employee_id = 13

In my head, I'm thinking we do not need to access employee_info table at all for this query to execute in a functionally correct way. Yet, when I explain plans on this query, I see it accessing employee_info. Am I missing something that makes it necessary to actually perform that join? If it is functionally equivalent to not hit that table, is there a way to stop that from happening in 12c?

A View by definition is nothing but a parsed SQL stored in the DB, when you query the View it actually executed the query and then returns the data. If you want to select only employee_name from employees table then why dont you create a view which only select from employees table and then the view will access the employees table. — Himanshu Kandpal, Jan 24 '22 at 19:49
Please provide *table definition* including `PK`and `FK` and the view definition. Your *example* of view query does not contain `employee_id` which leads to `ORA-00904: "EMPLOYEE_ID": invalid identifier` — Marmite Bomber, Jan 24 '22 at 20:01

score 1 · Answer 1 · answered Jan 24 '22 at 20:47

You deduction is basically correct, but you must take in account than not every optimization that is possible is also implemented.

Oracle pragmatically observes the potential and decides where the optimization should be extended.

For example there were times where the predicate where 1=1 and ... could confuse the optimizer, but as this "construction" is used rather often, in recent version the optimizer recognised it and ignore it without side effects.

Your setup of outer join to child, while selecting distinct from the parent columns only is probably not very frequent and therefore not yet covered.

Here a similar example, where Oracle can skip a table from a view (Note the PK and FK definition, which is crutial)

create table employees (
employee_id  int primary key,
employee_name varchar2(10),
employee_active varchar2(10));

create table employee_info (
employee_id int,
marital_status varchar2(10));

alter table employee_info
add constraint emp_info_fk1 foreign key (employee_id)
references employees (employee_id);

create or replace view employee_view as
select e.EMPLOYEE_ID, e.employee_name, e.employee_active, ei.marital_status
    from employees e
    join employee_info ei on ei.employee_id = e.employee_id;

The query select marital_status from employee_view where employee_id = 13 can skip the employees (parent) table and access only the child table

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |    20 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEE_INFO |     1 |    20 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
    1 - filter("EI"."EMPLOYEE_ID"=13)

More information and links in similar answer

Oracle 12c - view plans access unnecessary tables

1 Answers1