本文发表在 rolia.net 枫下论坛我在table t的a列上加了index t_i, 这样能看出#260246,#260300,#260764并没有用到index (note: 对小型table来说，性能并不一定好）

cloud2001（坐看云起)的答案: (#260246):

SQL> SELECT * FROM T
2 WHERE A IN (
3 SELECT A FROM T
4 GROUP BY A HAVING COUNT(A)>1) ;

A B
---------- ----------
2 B2
3 B3
2 B4
3 B5
3 B6


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=67 Bytes=2211)
1 0 HASH JOIN (Cost=6 Card=67 Bytes=2211)
2 1 VIEW OF 'VW_NSO_1' (Cost=4 Card=82 Bytes=1066)
3 2 FILTER
4 3 SORT (GROUP BY) (Cost=4 Card=82 Bytes=1066)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=1 Card=82 Bytes=1066)
6 1 TABLE ACCESS (FULL) OF 'T' (Cost=1 Card=82 Bytes=1640)


contact(Contact)的答案 (#260300):

SQL> select T2.A, T1.B from T T1, T T2
2 where T1.a = T2.a group by T2.A, T1.B having count(T2.A)>1;

A B
---------- ----------
2 B2
2 B4
3 B3
3 B5
3 B6


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=67 Bytes=2211)
1 0 FILTER
2 1 SORT (GROUP BY) (Cost=5 Card=67 Bytes=2211)
3 2 HASH JOIN (Cost=3 Card=67 Bytes=2211)
4 3 TABLE ACCESS (FULL) OF 'T' (Cost=1 Card=82 Bytes=1640)
5 3 TABLE ACCESS (FULL) OF 'T' (Cost=1 Card=82 Bytes=1066)


cyt（会飞的鹿）的答案 (#261114):
(note: 应加上group by, 这样比较起来才公平，加group by后性能要差一些）

SQL> select * from T T1
2 where exists
3 (select A from T where A=T1.A group by A having count(1)>1)
4 group by a,b;

A B
---------- ----------
2 B2
2 B4
3 B3
3 B5
3 B6


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=5 Bytes=100)
1 0 SORT (GROUP BY) (Cost=3 Card=5 Bytes=100)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=1 Card=5 Bytes=100)
4 2 FILTER
5 4 SORT (GROUP BY NOSORT) (Cost=1 Card=1 Bytes=13)
6 5 INDEX (RANGE SCAN) OF 'T_I' (NON-UNIQUE) (Cost=1 Card=1 Bytes=13)



fromztoa(fromZtoA)的答案 (#260764):

SQL> select A, B from
2 (select A, B, lag(A, 1) over (order by A) as A1,
3 lead(A, 1) over (order by A) as A2
4 from T)
5 where A = A1 or A = A2;


A B
---------- ----------
2 B2
2 B4
3 B3
3 B5
3 B6


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=82 Bytes=3772)
1 0 VIEW (Cost=3 Card=82 Bytes=3772)
2 1 WINDOW (SORT)
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=1 Card=82 Bytes=1640)更多精彩文章及讨论，请光临枫下论坛 rolia.net