ORACLE数据库常见问题整理一

1：in和exists的区别

1.1：查询效率

select * from rest601 r1 where r1.name in (select r2.name from rest701 r2)

in()只执行一次,它查出B表中的所有id字段并缓存起来.之后,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录

可以看出,当B表数据较大时不适合使用in(),因为它会B表数据全部遍历一次.
如:A表有10000条记录,B表有1000000条记录,那么最多有可能遍历10000*1000000次,效率很差.
再如:A表有10000条记录,B表有100条记录,那么最多有可能遍历10000*100次,遍历次数大大减少,效率大大提升.

结论:in()适合B表比A表数据小的情况

select * from rest601 r1 where exists (select r2.name from rest701 r2 where r1.name = r2.name)

exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false.

 

当B表比A表数据大时适合使用exists(),因为它没有那么遍历操作,只需要再执行一次查询就行.
如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等.
如:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果.
再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.

结论:exists()适合B表比A表数据大的情况

 

1.2：exists(not exists)与in(not in)的对比及null值处理

     对于null的处理。由于null是未知，不能够用=或者<>进行比较（比较的结果未知），只能用is null或者is not null来判断是否是null。
    in和exists对于null的处理结果是相同的，对于IN，如果子查询中有NULL值，即便外查询的条件列有NULL值，返回的结果也不会有null，因为null=null返回不是true；对于EXISTS，如果查询的条件列有NULL值，由于null=null返回不是true，因此exists对于该行数据返回false，条件为null值的行也在最终的结果集合中。

NOT EXISTS 和NOT IN对于NULL的结果则大不相同，使用not in的话，如果子查询中有null，则返回的结果全空，例如 where 1 not in (2, null) 等价于where 1!=2 &&1!=null，由于后者为false，因此最终返回的结果为空

 

2：rowid和for update的区别

select * from rest601 for update    会锁表

select r.*,rowid from rest601     不会锁表

 

3：锁表查询

3.1查询锁

select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

3.2 查看锁定的sid

select /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username user_name,
      o.owner,
      o.object_name,
      o.object_type,
      s.sid,
      s.serial#
 from v$locked_object l,dba_objects o,v$session s
 where l.object_id=o.object_id
 and l.session_id=s.sid 
 order by o.object_id,xidusn desc;

3.3 删除锁定

alter system kill session '617,47632'; --sid 和serial

4：聚合函数

AVG、COUNT、MAX、MIN、SUM

平时遇到最多的sum(),max()等等，使用group by聚合函数即可轻松解决，但是有时候我们的需求：按类别分组，按时间排序，然后获取每个分组中最新的一条

select id, version, project_number
  from (select id,
               version,
               project_number,
               row_number() over(partition by project_number order by version) rn
          from uflowpmo.table
         where version in ('正式立项', '预立项')
           and project_number = 'projectnum') r
 where rn = 1

5：我认为最基础数据误删恢复

select *
  from rest701 as of timestamp to_timestamp('2019-08-12 16:57:00', 'yyyy-mm-dd hh24:mi:ss');

6：常用的一些sql

--找出一张表的重复数据
select r.name,count(*) from rest601 r group by r.name having count(*) > 1;

--删除重复记录
delete from rest601 r where r.rowid > (select min(r1.rowid) from rest601 r1 where r1.name = r.name);
--或者
delete from rest601 r where rowid not in (select min(rowid) from rest601 group by name);

--将B表中的c1字段和A表的c1字段关联，更新A表中的C1，C2字段
update A a set (a.c1,a.c2) = (select b.c1,b.c2 from B b where a.c1 = b.c1) where exists (select b.c1 from B b where a.c1 = b.c1);
--注意：where子条件必须带上，否则会导致A表凡是没有关联到的记录被更新为空