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()