Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table USERS (
 USER_ID integer primary key
 , FIRST_NAME varchar2(30), LAST_NAME varchar2(30)
);
create table ORDERS (
 ORDER_ID integer primary key
 , ORDER_DATE timestamp, AMOUNT number(10,2),DESCRIPTION varchar2(30)
 , USER_ID integer references USERS
) partition by hash(USER_ID) partitions 8;
create index ORDERS_BY_USER on ORDERS(USER_ID) local;
explain plan for
select order_month,sum(amount),count(*)
from
  (
  select trunc(ORDER_DATE,'month') order_month, USER_ID, AMOUNT
  from ORDERS join USERS using(USER_ID)
  ) user_orders
where USER_ID=42
group by order_month
;
select * from dbms_xplan.display()

PLAN_TABLE_OUTPUT

Plan hash value: 1746940330

------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                            |                |     1 |    39 |     2  (50)| 00:00:01 |       |       |

|   1 |  HASH GROUP BY                              |                |     1 |    39 |     2  (50)| 00:00:01 |       |       |

|   2 |   PARTITION HASH SINGLE                     |                |     1 |    39 |     1   (0)| 00:00:01 |     6 |     6 |

|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ORDERS         |     1 |    39 |     1   (0)| 00:00:01 |     6 |     6 |

|*  4 |     INDEX RANGE SCAN                        | ORDERS_BY_USER |     1 |       |     1   (0)| 00:00:01 |     6 |     6 |

------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("ORDERS"."USER_ID"=42)

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)