大家认为什么样的方式去重效率最高？GROUP BY ? DISTINCT? EXISTS? UNION?

lihy114

原来用group by比较多；现在开始尝试表的自连接配合rowid使用

db_master

常用disintc。因为经常用表连接操作，如果用group by 代码太臃肿了。

〇〇

dingjun123 发表于 2015-2-6 15:33
group by和distinct一样的。。。喜欢肯定是distinct

distinct有hash吗

jinaqu

从自身出发，我最关注的效率和性能

372369432

我现在用的比较多的是分析函数+rowid

duhonguo

学习ing

木已成头

同12楼，一般查询的时候都是好些个标关联查询，习惯用distinct

younghuijun

AskTom这篇文章可能对你有用:

https://asktom.oracle.com/pls/as ... ON_ID:4274289639999

〇〇

younghuijun 发表于 2015-2-16 09:38
AskTom这篇文章可能对你有用:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID: ...

tom的办法,出错的时候,exceptions表自动被填充？
how about adding a unique constraint on the columns in question and using "exceptions
into".  For example:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( a int, b int, c int );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec gen_data( 'T', 500 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select * from t where rownum < 10;

9 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table exceptions(row_id rowid,
  2                          owner varchar2(30),
  3                          table_name varchar2(30),
  4                          constraint varchar2(30));

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_unique unique(a,b,c)
exceptions into exceptions;
alter table t add constraint t_unique unique(a,b,c) exceptions into exceptions
                             *
ERROR at line 1:
ORA-02299: cannot validate (OPS$TKYTE.T_UNIQUE) - duplicate keys found


ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table dups
  2  as
  3  select *
  4    from t
  5   where rowid in ( select row_id from exceptions )
  6  /
create table dups
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


ops$tkyte@ORA817DEV.US.ORACLE.COM> delete from t where rowid in ( select row_id from
exceptions );

18 rows deleted.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select distinct * from dups;

9 rows created.

hooyz_com

来学习的ing