Tested using the 23ai Free Developer VirtualBox appliance:
$ oraversion -compositeVersion
23.4.0.24.05
Using the “HR” sample schema, in release 23.3, with statistics gathered post-installation.
select emp.employee_id, emp.last_name, emp.manager_id
from employees emp
where exists ( select 1 from employees mgr,
departments dep
where mgr.employee_id = emp.manager_id
and mgr.department_id = dep.department_id
and instr(dep.department_name, 'M') = 1
);
If using optimizer_features_enable = “21.1.0”:
Plan hash value: 3888768276
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 1 |00:00:00.01 | 12 |
|- * 1 | HASH JOIN | | 1 | 18 | 504 | 6 (17)| 00:00:01 | 1 |00:00:00.01 | 12 |
| 2 | NESTED LOOPS | | 1 | 18 | 504 | 6 (17)| 00:00:01 | 1 |00:00:00.01 | 12 |
| 3 | NESTED LOOPS | | 1 | 18 | 504 | 6 (17)| 00:00:01 | 1 |00:00:00.01 | 11 |
|- 4 | STATISTICS COLLECTOR | | 1 | | | | | 2 |00:00:00.01 | 9 |
| 5 | VIEW | VW_SQ_1 | 1 | 3 | 39 | 4 (0)| 00:00:01 | 2 |00:00:00.01 | 9 |
| 6 | HASH UNIQUE | | 1 | 3 | 69 | | | 2 |00:00:00.01 | 9 |
| 7 | NESTED LOOPS | | 1 | 3 | 69 | 4 (0)| 00:00:01 | 2 |00:00:00.01 | 9 |
| 8 | NESTED LOOPS | | 1 | 10 | 69 | 4 (0)| 00:00:01 | 2 |00:00:00.01 | 8 |
| * 9 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 1 | 16 | 3 (0)| 00:00:01 | 2 |00:00:00.01 | 6 |
| * 10 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 2 | 10 | | 0 (0)| | 2 |00:00:00.01 | 2 |
| 11 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 2 | 10 | 70 | 1 (0)| 00:00:01 | 2 |00:00:00.01 | 1 |
| * 12 | INDEX RANGE SCAN | EMP_MANAGER_IX | 2 | 6 | | 0 (0)| | 1 |00:00:00.01 | 2 |
| 13 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 6 | 90 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |
|- 14 | TABLE ACCESS FULL | EMPLOYEES | 0 | 6 | 90 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------------------------------------------------------
If using optimizer_features_enable = “23.1.0”:
Plan hash value: 2064895219
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 49 (100)| | 1 |00:00:00.01 | 79 |
|* 1 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 79 |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 1605 | 3 (0)| 00:00:01 | 107 |00:00:00.01 | 7 |
| 3 | NESTED LOOPS | | 19 | 1 | 23 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 72 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 19 | 1 | 7 | 1 (0)| 00:00:01 | 18 |00:00:00.01 | 36 |
|* 5 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 19 | 1 | | 0 (0)| | 18 |00:00:00.01 | 18 |
|* 6 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 18 | 1 | 16 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 36 |
|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 18 | 1 | | 0 (0)| | 18 |00:00:00.01 | 18 |
-----------------------------------------------------------------------------------------------------------------------------------------
Not only is the estimated cost higher; the actual count of logical reads is also 6.5 x higher.
(The subquery is correctly unnested if requested by using an explicit /*+ UNNEST */ hint.)
Just thought I'd mention it.
Best regards,