I am pretty sure. 请看分析。

onewayticket (onewayticket)
本文发表在 rolia.net/zh 相约加拿大网上社区枫下论坛
我在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/zh
(#261831@0)
2001-11-14 -05:00

回到话题: 闲来无事, 出道ORACLE SQL的题考考大家. 这是在下几个月前工作中遇到的问题, 看似简单, 其实不然.

回到论坛: HOME枫下论坛枫下论坛主坛工作学习IT技术讨论

URL:   
http://www.rolia.net/zh/post.php?f=0&p=261831