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