1 select e1.rowid,
2 e1.empno,
3 e1.ename
4 from emp e1,
5 (select empno, min(rowid)
min_rowid
6 from emp
7 group by empno)
e2
8 where e1.empno = e2.empno
9* and e1.rowid != e2.min_rowid
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 VIEW
3 2
SORT (GROUP BY)
4 3
TABLE ACCESS (FULL) OF 'EMP'
5 1 FILTER
6 5
SORT (JOIN)
7 6
TABLE ACCESS (FULL) OF 'EMP'
Statistics
----------------------------------------------------------
0
recursive calls
0
db block gets
6
consistent gets
0
physical reads
0
redo size
1096 bytes sent via
SQL*Net to client
651 bytes received
via SQL*Net from client
2
SQL*Net roundtrips to/from client
2
sorts (memory)
0
sorts (disk)
14 rows
processed
SQL> l
1 select e1.rowid,
2 e1.empno,
3 e1.ename
4 from emp e1
5 where e1.rowid not in (select min(e2.rowid) min_rowid
6
from emp e2
7*
where e1.empno = e2.empno)
SQL> /
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 FILTER
2 1 TABLE
ACCESS (FULL) OF 'EMP'
3 1 SORT
(AGGREGATE)
4 3
TABLE ACCESS (FULL) OF 'EMP'
Statistics
----------------------------------------------------------
0
recursive calls
0
db block gets
46 consistent
gets
0
physical reads
0
redo size
1096 bytes sent via
SQL*Net to client
651 bytes received
via SQL*Net from client
2
SQL*Net roundtrips to/from client
0
sorts (memory)
0
sorts (disk)
14 rows
processed
