You Asked

CREATE TABLE dept (dept_id NUMBER, dept_name VARCHAR2(50 CHAR), emp_count NUMBER);
CREATE TABLE emp (emp_id NUMBER, emp_name VARCHAR2(50 CHAR), dept_id NUMBER);

CREATE VIEW vw_dept_empcount
AS
SELECT d.dept_id, 
       d.dept_name, 
       d.emp_count AS d_emp_count, 
       e.emp_count AS e_emp_count
FROM   dept d
LEFT JOIN
       ( SELECT emp.dept_id, COUNT(*) AS emp_count FROM emp GROUP BY emp.dept_id ) e
ON     d.dept_id = e.dept_id
;

/* The following UPDATE runs successfully indicating that dept is considered to be the key-preserved table in view vw_dept_empcount */
UPDATE vw_dept_empcount
SET d_emp_count = e_emp_count
;
COMMIT;

/* The following query selects columns only from key-preserved table (dept) in view vw_dept_empcount.
   The expectation is that this query should access the key-preserved table dept only.
   In other words, the optimizer should perform join elimination and not access table emp.
*/
SELECT dept_id, dept_name
FROM   vw_dept_empcount
;

/* The following query shows the SQL plan that was used to execute the above query.
   The SQL plan shows that both tables (i.e. dept and emp) are accessed and a join operation is performed.
   This shows that join elimination is not occurring.
*/
SELECT *
FROM   TABLE(dbms_xplan.display_cursor)
;

SQL_ID  20660x2uqft95, child number 0
-------------------------------------
SELECT dept_id, dept_name  FROM   vw_dept_empcount
 
Plan hash value: 2939036968
 
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |     5 (100)|          |
|*  1 |  HASH JOIN OUTER     |      |     1 |   128 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | DEPT |     1 |   115 |     2   (0)| 00:00:01 |
|   3 |   VIEW               |      |     1 |    13 |     3  (34)| 00:00:01 |
|   4 |    HASH GROUP BY     |      |     1 |    13 |     3  (34)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| EMP  |     1 |    13 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("D"."DEPT_ID"="E"."DEPT_ID")
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

1. Why is join elimination not occurring in the above query that selects columns only from key-preserved table in view?

2. For the above query on the view to access table dept only, is there no way to achieve this other than to define the view as follows:

CREATE VIEW vw_dept_empcount
AS
SELECT d.dept_id, 
       d.dept_name, 
       d.emp_count AS d_emp_count, 
       ( SELECT COUNT(*) FROM emp e WHERE e.dept_id = d.dept_id ) AS e_emp_count
FROM   dept d
;

and Chris said...

The optimizer is very cautious about transformations like removing tables - a table being key-preserved isn't strong enough for it to eliminate tables.

There are lots of preconditions for the optimizer to eliminate a join. They're all missing in this example!

* You need primary & foreign key constraints on the tables
* You should only select columns from the child table; you're selecting from the parent.
* You need to inner join the tables

If you add the constraints, rewrite the view to remove the subquery and outer join, and only select child columns you'll find that join elimination does happen:

CREATE TABLE dept (dept_id NUMBER PRIMARY KEY, dept_name VARCHAR2(50 CHAR), emp_count NUMBER);
CREATE TABLE emp (emp_id NUMBER, emp_name VARCHAR2(50 CHAR), dept_id NUMBER references dept);

insert into dept values ( 10, 'test', 2 );
insert into emp values ( 1, 'emp 1', 10 );
insert into emp values ( 2, 'emp 2', 10 );

CREATE OR REPLACE VIEW vw_dept_empcount
AS
SELECT d.dept_id, 
       d.dept_name, 
       d.emp_count AS d_emp_count, 
       COUNT(*)    AS e_emp_count
FROM   dept d
JOIN   emp e
ON     d.dept_id = e.dept_id
GROUP  BY d.dept_id, 
       d.dept_name, 
       d.emp_count ;

SELECT e_emp_count c
FROM   vw_dept_empcount v;

select * from dbms_xplan.display_cursor( format => 'BASIC');

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  HASH GROUP BY     |      |
|   2 |   TABLE ACCESS FULL| EMP  |
-----------------------------------

Note now this means you can no longer run the update that worked previously:

UPDATE vw_dept_empcount
SET    d_emp_count = e_emp_count

ORA-01732: data manipulation operation not legal on this view

Rating

(1 rating)

Comments

Follow-up

Jay, May 10, 2024 - 3:26 am UTC

Thanks for the response. The preconditions for join elimination that you described are all related to the use case of inner joining parent-child tables that have foreign keys. Only supporting this use case is very restrictive.

My use case involves outer join and no parent-child relationship. You mentioned in your response that I am selecting from the parent table, but in my use case there is no parent and child tables. I may have given such an impression by using table names dept and emp which are commonly used in examples to illustrate parent-child relationships.

Let me explain another way. The example I gave in my original post can be described more generally as follows:

SELECT     <rowsource1 columns only>
FROM       rowsource1
LEFT JOIN  rowsource2
ON         <join conditions>
;

In the above generic query, the only condition that needs to be met for join elimination to be possible in theory is that rowsource1 should be the key-preserved table. The example in my original post meets this condition since dept is the key-preserved table and Oracle knows about this (since it allowed an update on the view column sourced from the dept table).

The following aspects baked into the generic query is what makes join elimination possible in theory once it is established that rowsource1 is the key-preserved table:
- Join operation is LEFT OUTER JOIN (with the key preserved table being the left table)
- Query selects columns from the key preserved table only

Given the above and the fact that Oracle already knows that rowsource1 is the key-preserved table in my dept-emp example (in the original post), shouldn't it be possible for the optimizer to perform the join elimination? Can this be filed as an enhancement request?

May 10, 2024 - 10:25 am UTC

As I said, the optimizer is very cautious about transformations like table elimination. Mistakes here can lead to massive wrong results problems. Relying on constraints is the safest way to do this.

Oracle Database relies on constraints to determine if a table is key-preserved. Note that a successful update does not guarantee a table's key-preserved. From 21c up there's a runtime check to see if a row has changed more than once:

CREATE OR REPLACE VIEW vw_dept_empcount
AS
SELECT d.dept_id, 
       d.dept_name, 
       d.emp_count AS d_emp_count, 
       e.emp_id as e_emp_count
FROM   dept d
LEFT JOIN emp e
ON     d.dept_id = e.dept_id;

insert into dept values ( 10, 'test 1', 0 );
insert into emp values ( 1, 'test 1', 10 );

update VW_DEPT_EMPCOUNT
set    d_emp_count = e_emp_count;
--1 row updated.

insert into dept values ( 10, 'test 1', 0 );
insert into emp values ( 1, 'test 1', 10 );

update VW_DEPT_EMPCOUNT
set    d_emp_count = e_emp_count;

select * from VW_DEPT_EMPCOUNT;
--ORA-30926: The operation attempted to update the same row (rowid: 'AAARlqAAMAAAACnAAB') twice.

In this specific case where you've grouped by the join columns in the outer-joined table then it may be possible to eliminate it, though this feels like a niche request to me.

You're welcome to file an ER if you think this use case is valuable to you.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Follow-up

More to Explore

PL/SQL demos

PL/SQL docs

Join elimination not occurring when selecting columns only from key-preserved table in view

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Follow-up

More to Explore

PL/SQL demos

PL/SQL docs