clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1451356 fiddles created (18689 in the last week).

create table USERS ( USER_ID integer primary key , FIRST_NAME varchar2(30), LAST_NAME varchar2(30) );
 hidden batch(es)


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;
 hidden batch(es)


create index ORDERS_BY_USER on ORDERS(USER_ID) local;
 hidden batch(es)


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 ;
 hidden batch(es)


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)
 hidden batch(es)