-
数据表中根据一列数据进行数据去重:例如有表名为T_BUFFET_ORDER,包含三个字段:phone,month,status。现在phone字段有重复号码,根据phone去重:
delete from T_BUFFET_ORDER where phone in (select phone from T_BUFFET_ORDER group by PHONE HAVING count(PHONE)>1) and ROWID not in (select min(ROWID) from T_BUFFET_ORDER group by PHONE HAVING count(PHONE)>1);
-
根据多列数据去重,比如根据phone和month两个字段作为去重依据。
delete from T_BUFFET_ORDER a
where (a.phone,a.month) in (select phone,month from T_BUFFET_ORDER group by phone,month having count(*) > 1)
and rowid not in (select min(rowid) from T_BUFFET_ORDER group by phone,month having count(*)>1)
-
索引的重要性:
以 前没有过多的关注过索引,今天遇到一个问题,一个普通的电话号码抽奖资格表,里边一共40万数据。之前没有创建任何主键,联合主键和索引等等。今天突然发 现一个每隔一小时的定时任务竟然第一次没有做完,第二个就直接起来了。造成锁表。日志后台druid报了大量的慢语句错误。一条update竟然是需要 10S,这是什么样的牛速。果断建立了联合主键。操作40万数据的更新,6秒钟,轻松搞定。(每1000条做一次批量更新操作,防止session超 时)。
alter table T_BUFFET_ORDER add constraints buffet_order_pk primary key (phone,month);
count(1)与count(*)比较:
如果你的数据表没有主键,那么count(1)比count(*)快
如果有主键的话,那主键(联合主键)作为count的条件也比count(*)要快
如果你的表只有一个字段的话那count(*)就是最快
count(*) count(1) 两者比较。主要还是要count(1)所相对应的数据字段。 如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。 因为count(*),自动会优化指定到那一个字段。所以没必要去count(?),用count(*),sql会帮你完成优化的
count详解:
count(*)将返回表格中所有存在的行的总数包括值为null的行,然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入).distinct 列名,得到的结果将是除去值为null和重复数据后的结果
分页查询效率最高的语句
1.无orderby
select * from (select ROWNUM rowno,t.* from t_isag_sms t where t.SEND_DATE BETWEEN TO_DATE('2015/11/01','yyyy/mm/dd') and TO_DATE('2015/11/10','yyyy/mm/dd') and ROWNUM <=20) where rowno >=10;
2.有orderby
select * from (select tt.*,ROWNUM rn from (select t.* from t_isag_sms t where
t.SEND_DATE BETWEEN TO_DATE('2015/11/01','yyyy/mm/dd')
and TO_DATE('2015/11/10','yyyy/mm/dd') order by t.id desc)tt where ROWNUM<=20)where rn>=10;
3.使用HINT使语句获得最佳响应时间
select /*+ FIRST_ROWS */ * from (select ROWNUM rowno,t.* from t_isag_sms t where
t.SEND_DATE BETWEEN TO_DATE('2015/11/01','yyyy/mm/dd')
and TO_DATE('2015/11/10','yyyy/mm/dd') and ROWNUM <=20) where rowno >=10;
select /*+ FIRST_ROWS */ * from (select tt.*,ROWNUM rn from (select t.* from t_isag_sms t where
t.SEND_DATE BETWEEN TO_DATE('2015/11/01','yyyy/mm/dd')
and TO_DATE('2015/11/10','yyyy/mm/dd') order by t.id desc)tt where ROWNUM<=20)where rn>=10;