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;

insert into users (user_id,first_name,last_name)
           SELECT 1,'joao','maria' from dual
UNION ALL  SELECT 2,'maria','joao' from dual;
2 rows affected

insert into ORDERS 
           (order_id,  order_date, amount, description, user_id)
            Select 1,to_date('20200505','YYYYMMDD'), 10.55 ,     'desc' , 1   from dual
 UNION ALL  SELECT 2,to_date('20200505','YYYYMMDD'), 12.60 ,     'desc2', 1   from dual
 UNION ALL  SELECT 3,to_date('20200505','YYYYMMDD'), 7.90  ,     'desc3', 2   from dual
 UNION ALL  SELECT 4,to_date('20200505','YYYYMMDD'), 4.12  ,     'desc4', 2   from dual;
4 rows affected

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=2
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 |     7 |     7 |

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

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

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

 

Predicate Information (identified by operation id):

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

 

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

 

Note

-----

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