oracle一步接一步（4）--一些技巧

本片介绍一些常用技巧

 

1.删除表中重复记录

  比如有表 ztest(id int, name varchar(20))

  其中想删除name重复的记录,只保留其中的一条，可以如下处理：

delete from ztest
where name in 
    (select name from ztest group by name having count(name) > 1)
and rowid not in 
    (select min(rowid) from ztest group by name having count(name) > 1);

 技巧在于用到了oracle的每个表都给加的隐含字段rowid。令人惋惜的是，我喜欢用的mysql不支持这样的操作，不仅仅是没有隐含字段的问题，而是因为在删除语句where子句中不能再有查询。

同样，删除由多个字段决定的重复记录可如下编写sql (假设有ztestb表中添加了age字段)：

 

delete from ztest z
where (z.name, z.age) in 
    (select name, age from ztest group by name, age having count(*) > 1)
and rowid not in 
    (select min(rowid) from ztest group by name, age having count(*) > 1);

 

在这里说一个查询某个字段不重复的所有记录 ，发现mysql和oracle存在不同，例如下面的语句，在mysql中很好的执行

select * from user
where id in
(
    select min(id) from user group by name having count(name) > 1
)
or id in
(
    select id from user group by name having count(name) = 1
);

 意思是查询user表中name不重复的记录，id是主键。但是这样的语句在oracle10g中就会报一个不是group by的错误。从语法结构上讲，这样的语句是没有错误的，所以在这个地方，oracle不够聪明。当然我们可以用变通的方式来取得oracle中相同的记录，比如可以用下面的语句执行

select * from muser
where id in
(
    select min(id) from muser group by name having count(name) > 1
)
or name in
(
    select name from muser group by name having count(name) = 1 
);

 在网上看到很多人问怎样查询一个不带某个字段重复记录的sql语句。觉得有点好笑，自己会sql语法，和逻辑推理以后可以自己试试。比如上面的语句就是我这样推敲出来的，当然不能保证是最优的查询语句。不过在测试过程中却发现了oracle的不足之处。

 

2.复制表中数据

把表中的数据在重新插入一遍，原来的数据还是保留的可使用如下语句(在ztest没设置主键的时候测试通过)

insert into ztest(select * from ztest);

当然插入表结构相同的表中也应该可以。这样的语句在mysql中也同样适用。

 

3.分页查询语句

select * from
(
    select z.*, rownum rn
    from (select * from ztest) z
    where rownum < 10
)
where rn > 5; 

上面以表ztest最为测试对象，查找行号大于5小于10的记录。注意rownum只能出现 < 一个数的情况，而不能大于一个数，至于各种原理（依据查找忽略，标记从一的过程），请查阅相关资料。

 

4.建立索引

对于大笔数据，建立索引能有效的提高查询速度。下面是一个例子，可以执行看结果

--建表并插入两行数据
drop table itext;
create table itext(id int, name varchar(20));

--建立一个插入数据的存储过程
create or replace procedure proc_i(param in int) is
    i int;
begin
    for i in 1..param loop
        insert into itext(id, name) values(i, 'zhangyt');
    end loop;
end proc_i;
/
--调用
exec proc_i(100000);


set timing on;
--在没建立索引之前查询
select count(*) from itext;
set timing off;

create index itext_idx_id on itext(id);
set timing on;
--在建立索引之后查询
select count(*) from itext;
set timing off;

 set timing on;语句表示显示命令执行所用的时间。

对于查看某表相关的索引，可以用如下方式查询

                   select index_name from all_indexes where table_name = 'ITEXT';

或者

                   select index_name from user_indexes where table_name = 'ITEXT';

注意表的名字要大写，因为在建立表后，oracle会以大写的形式记录你所建立的表的名字。