Hello, i have a question about execution plan.
I have tables below:
create table user_t(
user_no number(10) constraint user_pk primary key,
user_name varchar2(10) not null,
user_check_no number(10) not null,
user_code varchar2(4) not null
);
create index user_t_x01 on user_t(user_check_no);
create index user_t_x02 on user_t(user_code);
create table user_history(
user_no number(10) not null,
change_date date not null,
phone varchar2(10) not null,
address varchar2(10) not null,
state_code varchar2(10) not null,
constraint user_history_pk primary key(user_no, change_date)
);
explain plan for
select
c.user_no, c.user_name, h.phone, h.address, h.state_code, h.change_date
from user_t c, user_history h
where c.user_check_no = :rmnno
and h.user_no = c.user_no
and h.change_date = (
select max(change_date)
from user_history m
where user_no = c.user_no
and change_date >= trunc(add_months(sysdate, -12), 'mm')
and change_date < trunc(sysdate, 'mm')
);
select * from table(dbms_xplan.display());
I got a execution plan below:
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 85 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 85 | 3 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 42 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| USER_T | 1 | 33 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | USER_T_X01 | 1 | | 1 (0)| 00:00:01 |
| 6 | VIEW PUSHED PREDICATE | VW_SQ_1 | 1 | 9 | 1 (0)| 00:00:01 |
|* 7 | FILTER | | | | | |
| 8 | SORT AGGREGATE | | 1 | 22 | | |
|* 9 | FILTER | | | | | |
|* 10 | INDEX RANGE SCAN | USER_HISTORY_PK | 1 | 22 | 2 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | USER_HISTORY_PK | 1 | | 0 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | USER_HISTORY | 1 | 43 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
My question is, why min/max → first_row doesn't apply to the index user_history_pk?
Because the index columns are (user_no, change_date) and the sub query retrieves max value for change_date,
I expected the index range scan(id = 10) should be able to get a row using min/max → first_row.
When unnest is removed from the sub query, i got the execution plan below:
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 76 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 76 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 76 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | USER_T | 1 | 33 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | USER_T_X01 | 1 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | USER_HISTORY_PK | 1 | | 0 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 22 | | |
|* 7 | FILTER | | | | | |
| 8 | FIRST ROW | | 1 | 22 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN (MIN/MAX)| USER_HISTORY_PK | 1 | 22 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | USER_HISTORY | 1 | 43 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
As you can see, index range scan (min/max) → first row happend. But in here, VIEW PUSHED PREDICATE dissapeared.. I want to preserve it.
(I'm doing the steps in the database book right now, so I want to imitate the execution plan in the book)