Oracle一些常见用法

1、Decode,Case when：
Decode：语法简介，只能应用a=b然后取c的情况，只能在查询中使用，不能作为判断逻辑，有一种很好的使用场景，在判断两个字段是否相等且认为同为空时也是相等(null=null不成立，null<>null也不成立，应表示为is null或is not null)，则可使用Decode(a,b,1,2)=1可放在where后简化代码(efa用到这一段)；
Case when：应用多元化，可以作为查询语句，可以作为逻辑判断，和if when有同等功效，可以将decode翻译为case when，反之不一定行；
2、Nvl，Nullif：
Nvl应用很广，非空处理时会用到这个。在使用时要注意前后数据类型一致，在hs时遇到过存储过程nvl中两个数据类型不一致导致的报错；
Nullif：nullif(a,b),若a=b则返回为空，否则返回为B，18年以前没用过这个函数，可以简化代码，若除数为0，需要返回结果为空时可以用a/nullif(b,0)
3、Length(substr)、lengthb(substrb)：
Length(substr):直接比较长度，不分中英文；
Lengthb(substr):中文为3个长度，英文为一个，其他语言待研究；
在进行长度截取时，若字段类型为nvarchar2，用length截取该字段对应长度不会有问题，若对应字段类型为varchar2，则建议用lengthb截取，曾经待过的一个项目用substr截取字段长度为4000个字节时报错（个人判断生产不会出现类似问题就没改，好心人看到了记得改一下）；
4、分析函数：
一、Row_number() over(partition by col order by col1):
在去重时经常用到，两个表关联时，若一个表关联条件对应行有多条需要使用row_number()过滤，同一个组里面不会出现重复；

二、Rank() over(…)：
和row_number() over(…)类似，但是同一个组内会有重复，在取同一个批次时需要用到这个函数，序号是跳跃的；

三、DENSE_RANK() over(…):
和rank类似，但是序号是连续的

四、last_value和first_value
last_value(字段值a) over(partition by b order by c asc/desc rows between N(N行前) precending and M(M行后) following)[若是无界的一定要使用unbounding去修饰，不然last_value对应的值是第一个值而不是最后一个]
First_value(字段值a) over(partition by b order by c asc/desc rows between N(N行前) precending and M(M行后) following)
解释：last_value是求在b维度下对c进行排序后，取当前范围内最后一个c对应的字段a的值，first_value为取第一个，不过first_value不加界限也不影响，官方写法是加界限（最好加）

五：sum、max、count等聚合函数也可当作分析函数。
5、聚合函数：
Sum、max、min、avg、count
Sum、max、min、avg需要注意空值问题，count不会，在存储过程中，若使用：select a into b from table，需要加异常处理，但count不用。

6、常用系统视图(对象均为大写)：
Oracle自带视图一般有三种，dba、all、user，解释：dba全用户全对象，all本用户有权限的对象，user本用户自带的对象；
一、user_dependence：对应的字段有对象(包、存储过程、视图、同义词等)，依赖的对象，可以查当前存储过程下引用了哪些表，适用于对一张表进行修改时，需要同步处理哪些存储过程；
二、User_object：查看当前用户下的对象，可以检查有哪些对象，且对象的有效性；
三、User_source：查看系统对象(视图，存储过程)的脚本，比如需要检查哪个存储过程中待了某个值，可以使用该视图（记得upper）
四、user_tab_privs：检查当前用户拥有的权限，稍微看一下就知道了，但要注意级联授权，若a用户下视图引用了下b用户下的表，然后a将视图授权给c用户用，需要b授权该表权限给c用户，若b对a是级联授权，a就有权限授权该表给c用(曾今上线用过这个操作),每次上线钱都要检查这个；
五、v$sql（gv$sql(全局的)）：
查询执行过的sql语句，有一个sql_text和full（忘了啥，full是全部的查询语句，sql_text是截取的一部分），可以查到session
六、其他的自己再慢慢查吧！

7、正则表达式：
和分析函数一起使用，可以使代码简化很多。
Regexp_substr(a,b,c,d)：a要处理的字段，b匹配表达式（用法请百度），其中用的比较多的是’[^,]+’，以，分割字段a，c表示起始位置，d表示截取第几个，可与connect by level一起使用，进行列转行时非常方便；
regexp_instr(a,b,c,d)：b在a中出现的位置，其余描述类似，与正则与instr不同的是b能用|分割，判断多个值是否在a中如regexp_substr(a,’1|a|,’)表示1、a、,出现的位置
Regexp_count(a,b):b在a中出现的次数，与regexp_substr 一起使用时，可以用connect by level<=regexp_count(a,b)+1
Regexp_like(a,b):判断b是否在a中（返回true和false），其中b可以是多个值，同一个字段在用多个like时可以用其简化；

8、优化（经验积累较少，持续学习中）：
一、执行计划：
方法一：F5
方法二：explain plan for select …from…where …;
Select * from table(dbms_xplan.display(null,null,’advanced’))
可以查看到谓词信息，执行计划较f5准确；
方法三：查询sqlid；
Select * from table(dbms_xplan.display_cursor(sqlid));
读懂执行计划需要掌握常见的访问方式和连接方式，其中方法一、二均为预估的执行计划，方法三为实际的执行计划（一般都是空间换时间，大部分性能问题都可以通过改写来优化，hint在项目中基本都不建议用）
二、访问方式：
1、全表扫描：优化时，注意查看全表扫描的地方，该加索引的加索引，该分区的分区；
2、索引扫描：能显著加快查询效率，但是是针对查询的结果集较小时(查阅的资料大概是3%至10%)，返回结果集较大时，全表扫描可能更快，这就和我们翻书时一个道理，索引就相当于目录。
3、唯一索引扫描：最优的查询方式
4、快速索引扫描：（忘了应用在哪种情况下），好像是在查询时查询列刚好是索引列（按照建索引的顺序查询出列）
5、跳跃索引扫描：该索引为复合索引，查询条件列只有其中的部分字段且返回结果集较小时
注：索引列的条件不要带函数，若带函数可以建函数索引；
唯一索引和主键的区别是，主键不允许为空；
建索引时加个常数可以提高查询效率，让原本不走索引的sql语句走索引（一本书上看到的，但是会增大索引空间）。
索引太多会影响dml操作。
若排序列刚好为索引列，可以不适用order by ,索引是有序的（升序）
三、连接方式：
1、hash连接：适合大表和大表连接
2、嵌套连接：适合大小表的连接
3、排序合并连接：带有order by的语句
4、笛卡儿积：两个表没有关联条件 m*n形式的
四：其他：
1、In、exists、left join
听到最多的就是exists比in好用，因为exists固化了查询模式（好像是嵌套）而in就取决于子查询自身，听到最好的一个解释是（面试时面试官告诉我的，没面过。。。）：
In返回的是结果集，相当于一个数组；exists返回的是true和false。
Left join 可以和not exists在一定条件下互相改写，使两个表走索引

2、where 1>=1 比 where 1>0少一个复杂度
3、索引列不要使用is null 和is not null，最好给默认值(建表时即使字段可以为空，也能给默认值)
4、时间处理：
当月第一天：last_day(add_months(sysdate,-1))+1
To_date(substr(to_char(sysdate,’yyyymmdd’),1,6)|’01’)
当月最后一天：last_day(sysdate)
注意：求上一个月最后一天不要直接add_months