add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
BANNER BANNER_FULL BANNER_LEGACY CON_ID
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production 0
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
ORA-00900: invalid SQL statement
PRODUCTNUMBER DESCRIPTION
ABC Product ABC (with discount)
ABC Product ABC
ABC Product ABC
DEF Product DEF
XYZ Product XYZ
XYZ Product XYZ
XYZ Product XYZ
XYZ Product XYZ (with discount 1)
XYZ Product XYZ (with discount 2)
XYZ Product XYZ (with discount 3)
I1_PD CNT R_N I2_PN I2_PD
Product ABC (with discount) 1 2 ABC Product ABC (with discount)
Product ABC 2 1 ABC Product ABC
Product ABC 2 1 ABC Product ABC
Product DEF 1 1 DEF Product DEF
Product XYZ 3 1 XYZ Product XYZ
Product XYZ 3 1 XYZ Product XYZ
Product XYZ 3 1 XYZ Product XYZ
Product XYZ (with discount 1) 1 2 XYZ Product XYZ (with discount 1)
Product XYZ (with discount 2) 1 4 XYZ Product XYZ (with discount 2)
Product XYZ (with discount 3) 1 3 XYZ Product XYZ (with discount 3)
Product Number Product Desc.
ABC Product ABC
DEF Product DEF
XYZ Product XYZ
PRODUCTNUMBER DESCRIPTION
ABC Product ABC
DEF Product DEF
XYZ Product XYZ
PRODUCTNUMBER
DEF
XYZ
ABC
1 rows affected
ORA-00900: invalid SQL statement
Product Number Product Desc.
ABC Product ABC
DEF Product DEF
XYZ Product XYZ
1 rows affected
ORA-00900: invalid SQL statement
PRODUCTNUMBER
DEF
XYZ
ABC
PLAN_TABLE_OUTPUT
SQL_ID 51grqk19ayyuy, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ distinct cnt1.ProductNumber from
( select COUNT(*) as total, ProductNumber from Inventory group by
ProductNumber ) cnt1 JOIN ( select MAX(total) as maxtotal,
ProductNumber from ( select COUNT(*) as total, ProductNumber
   from Inventory group by ProductNumber ) t group by
ProductNumber ) cnt2 ON cnt1.total = cnt2.maxtotal
Plan hash value: 91479563
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 14 | | | |
| 1 | HASH UNIQUE | | 1 | 1 | 3 |00:00:00.01 | 14 | 2170K| 2170K| 654K (0)|
|* 2 | HASH JOIN SEMI | | 1 | 1 | 3 |00:00:00.01 | 14 | 1995K| 1995K| 1007K (0)|
| 3 | VIEW | | 1 | 10 | 3 |00:00:00.01 | 7 | | | |
| 4 | HASH GROUP BY | | 1 | 10 | 3 |00:00:00.01 | 7 | 1520K| 1520K| 662K (0)|
| 5 | TABLE ACCESS FULL| INVENTORY | 1 | 10 | 10 |00:00:00.01 | 7 | | | |
| 6 | VIEW | | 1 | 10 | 3 |00:00:00.01 | 7 | | | |
| 7 | HASH GROUP BY | | 1 | 10 | 3 |00:00:00.01 | 7 | 1520K| 1520K| 665K (0)|
| 8 | TABLE ACCESS FULL| INVENTORY | 1 | 10 | 10 |00:00:00.01 | 7 | | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CNT1"."TOTAL"="CNT2"."MAXTOTAL")
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
ORA-00900: invalid SQL statement
1 rows affected
PRODUCTNUMBER DESCRIPTION
ABC Product ABC
DEF Product DEF
XYZ Product XYZ
PLAN_TABLE_OUTPUT
SQL_ID 93j7mguwyy8mw, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ ProductNumber, Description from (
 select ProductNumber, Description , row_number() over
(partition by productnumber order by cnt1, cnt2 desc) as rn from (
 select ProductNumber, Description , count(1) over (partition
by ProductNumber) as cnt1 , count(1) over (partition by
ProductNumber, Description) as cnt2 from inventory ) t ) u where
rn = 1
Plan hash value: 307053586
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 7 | | | |
|* 1 | VIEW | | 1 | 10 | 3 |00:00:00.01 | 7 | | | |
|* 2 | WINDOW SORT PUSHED RANK| | 1 | 10 | 3 |00:00:00.01 | 7 | 2048 | 2048 | 2048 (0)|
| 3 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 7 | | | |
| 4 | WINDOW SORT | | 1 | 10 | 10 |00:00:00.01 | 7 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL | INVENTORY | 1 | 10 | 10 |00:00:00.01 | 7 | | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "PRODUCTNUMBER" ORDER BY "CNT1",INTERNAL_FUNCTION("CNT2") DESC )<=1)
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)