Hi,
i have a question on the reason an explain plan is like it is and not how i excpected it to be (not that i am surprised by that).
I didnt find any hints on the net on this, most probably due to a lack of creativity to find effective keywords since this seems to me a simple and common question.
Essentially i wonder why the table outerjoined (resp. its pk-index) doesn't disapper always from the plan even there is no filter predicate nor a part of the projection on it.
I did this on the actual live sql: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
explain plan for
select
first_name
from hr.employees e
, hr.departments d
where e.department_id = d.department_id(+)
and d.department_name(+) like 'D%'
/
select * from table(dbms_xplan.display())
/
Statement processed.
PLAN_TABLE_OUTPUT
Plan hash value: 2228653197
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 749 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | EMP_NAME_IX | 107 | 749 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
This is what i expected.
Now i copied the department table and added an extended primary key on (department_id, department_name)
create table departments_test as (select * from hr.departments)
/
alter table departments_test add constraint pk_dept primary key (department_id, department_name)
/
explain plan for
select
first_name
from hr.employees e
, departments_test d
where e.department_id = d.department_id(+)
and d.department_name(+) like 'D%'
/
select * from table(dbms_xplan.display())
/
Statement processed.
PLAN_TABLE_OUTPUT
Plan hash value: 2033173216
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 2782 | 4 (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 107 | 2782 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS STORAGE FULL| EMPLOYEES | 107 | 1070 | 3 (0)| 00:00:01 |
|* 3 | INDEX SKIP SCAN | PK_DEPT | 2 | 32 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))
3 - access("D"."DEPARTMENT_NAME"(+) LIKE 'D%')
filter("D"."DEPARTMENT_NAME"(+) LIKE 'D%')
PK-Index appears in the plan.
Lets change the "like" into "=".
explain plan for
select
first_name
from hr.employees e
, departments_test d
where e.department_id = d.department_id(+)
and d.department_name(+) = 'D%'
/
select * from table(dbms_xplan.display())
/
Statement processed.
PLAN_TABLE_OUTPUT
Plan hash value: 2228653197
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 749 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | EMP_NAME_IX | 107 | 749 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Surprisingly to me, without the "like" the plan looks like expected.
Perhaps the answer ist simply something like: explain plan is a fake this time, take a look at the real execution plan, but i cant test this myself.
But if it isnt as simple as this may be someone can hint me to some blog or documentation where this behaviour is explain and hopefully some hints are giving how to lead the optimizer on a possibly better path.
Thanks in advance.
Regards
Chris