文章已移至新博客:http://www.youzitool.com/index/detail/id/24.html
本文主要对oracle的一些常用或易犯错的知识点进行积累总结,根据工作情况将不断更新,写得不好的地方欢迎提出,同时欢迎大家留言个人的oracle心得 ^_^
1、exists和in的效率问题
① select * from T1 where exists(select 1 from T2 where T1.a=T2.a);② select * from T1 where T1.a in (select T2.a from T2);
T1数据量小、T2数据量非常大时(即T1<<T2)①的查询效率高
T1数据量大、T2数据量非常小时(即T1>>T2)②的查询效率高
2、union和union all区别
1)它们都是将两个结果集合二为一,但union会对结果集进行排序去重,union all只是简单将两个结果合并返回,不排重。
2)效率上union all比union高很多,若查询的结果集不包含重复数据,请用union all
3)无论使用union还是union all,请对数据库表建立索引。
3、in参数的个数限制
select * from T1 where a in(a1, a2, a3...)
oracle考虑性能问题将sql语句中in后括号中参数个数做了限制,不能超过1000,超过将报错。
解决方法:用or拆分in中的参数个数,本例中sql示例即为:select * from T1 where a in(a1, a2, ..., a1000) or a in(a1001, ...)
4、行转列
使用oracle10g以上提供的函数wmsys.wm_concat。
如user表如下:
id name
1 jackdai
1 lewjim
1 matraxa
1 alcyoneus
2 youzitool
2 leyou
若想要结果为:
id name
1 jackdai,lewjim,matraxa,alcyoneus
2 youzitool,leyou
查询sql如下:
select id, wmsys.wm_concat(name) name from user group by id
5、小心踩到dblink的大bug
问题现象:执行时间在几十秒的过程竟然执行了900分钟以上也没完成;使用awr分析时存在大量的“inactive transaction branch”等待事件。此现象偶尔出现,开始感觉莫名其妙的
真正原因:过程中使用了dblink,触发了oracle的bug,网上说在oracle11.2.0.3版本已修复此问题,但我们就用的此版本,问题仍然存在,坑...
解决办法:去掉dblink,改用其它方式,可根据具体情况选择
6、执行过程报错:ORA-20005: object statistics are locked (stattype = ALL)
解放方式:EXEC DBMS_STATS.unlock_schema_stats(ownname => 'username'); --username为数据库用户名
7、plsql查询中文记录为空:
现象:在plsql中执行查询结果为空:select * from a where a.name like '%中文%';
原因:操作系统中环境变量未设置或设置有问题
解决方法:
1)查询数据库字符集
select userenv('language') from dual;--查询结果如:SIMPLIFIED CHINESE_CHINA.AL32UTF8
2)系统环境变量设置:
变量名:NLS_LANG
变量值:SIMPLIFIED CHINESE_CHINA.AL32UTF8
变量值是select userenv('language') from dual;查询到的值
3)重启plsql即可