oracle 查询优化

• 第一章 表单查询

• replace 函数 替换功能 replace(原字段，“原字段旧内容“,“原字段新内容“,)

• COALESCE 返回该表达式列表的第一个非空value

  COALESCE(value1, value2, value3, …)

  含义： 返回value列表第一个非空的值。

  value列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。

  示例: select coalesce (1, null, 2 ) from dual ; – 返回1

  select coalesce ( null, 2, 1 ) from dual ; – 返回2

  select coalesce (t.empno, t.mgr ) from scott.emp t ;

  – 效果类似 NVL( t.empno, t.mgr )

• GREATEST 返回值列表中最大值

• LEAST 返回值列表中最小值 参数中的null问题，可以忽略
• 多个选择的case when
• rownum的应用，不能进行rownum = 2一类的筛选条件，因为需要查询到以后进行序号的标记

• escape转义字符

• 第二章 排序

• translate 以字符为单位，一一替换 from_string 与 to_string， to_string为空则杀出原字符串，长度不已按先后顺序一一替换不足补空格

• null first 空值在前，null last空值在后

• 第三章 操作多个表

• a union all b ，a b表数据类型必须一致 ‘’ 空串常常相当于null，但并不=null
• nuion是一种取并集的操作(不包含重复数据)，or是取交集的操作 nuion all包含重复

• with语句：可以理解为，临时创建一个在查询期间存在的VIEW(e),这个view仅在查询期间存在，查询之后就消失

  `WITH e AS (select ROWNUM AS sn ,depot,mgR,job FROM V)`

• 联结操作

  in () 会先执行emp2表的全查询，再与emp中的记录进行匹配，当emp2表的记录较少时可以使用；exists()不会执行emp2的全查询，当emp2的记录较大时应当用exists();emp2和emp大小一样时，两者效率一致。1.IN联结     `select empno,ename,job,sal,deptno FROM emp WHERE (ename,job,sal) IN (select ename,job,sal FROM emp2);`2.EXISTS写法     `select empno,ename,job,sal,deptno      FROM emp      WHERE EXISTS (     select null from emp2 b      where b.ename = a.ename     AND b.job = a.job     AND b.sal = a.sal);`

• 左右联结

  (+)是一种外联结的符号，放在非主表的一方,不写就位inner join。select a.name,b.name from a left join a.no = b.no order by 1,2;select a.name,b.name from a where a.no = b.no(+) order by 1,2;left join 左边为主表，左表返回所有数据。右表只返回与左表匹配的数据right join 右边为主表，右表返回所有数据，左表只返回与右表匹配的数据inner join 只返回两个表匹配到的数据库full join 返回两个表中的所有数据，非匹配的数据显示null,没有（+）写法

• 自关联

  实质是一张表的联结操作select a.*,b.* from A a left join A b on a.no = b.no order by 1;

• 外联结中的条件不要乱放

  select a.name,b.name,b.status from a left join b on a.no = b.no where b.status = 1;select a.name,b.name,b.status from a left join b on (a.no = b.no and b.status = 1);在联结条件中加条件针对的b表的数据，在外边加条件时针对联结以后的结果集。

• 多表查询时，一定要考虑空值的情况，

  not in (null)返回为空

• 第四章 插入、更新、删除

• 阻止对某几列的插入

  通过建立部分列的视图，达到阻止个别列的更新操作。

• 复制表的定义及数据

  creat table a as select * from b;

  复制只能copy表的结构，不能复制默认值和约束等信息。

• 用with check option关键字限制数据录入

  创建的视图中带有where等条件，则with check option 子句可以保证让你只能在视图的条件之内对视图进行DML

• 多表插入语句

  a)无条件insert

  insert all into emp1(empno,enme,job)values(empno,ename,job) into emp2(empno,ename,deptno)values(empno,ename,deptno)select empno,ename,job,deptno from emp where deptno in (10,20);

  同时向两个表中插入数据，两个表中的数据条数一致。

  b)有条件insert

  delete emp1;delete emp2;insert all when job in ('manager','salesman') into emp1(empno,enme,job)values(empno,ename,job) when deptno in ('20','30') into emp2(empno,ename,deptno)values(empno,ename,deptno)select empno,ename,job,deptno from emp;

  同一条记录如果符合两个判断条件，两个表中都会写入

  c)转置insert

  d)有条件 insert first

  delete emp1;delete emp2;insert first when job in ('manager','salesman') into emp1(empno,enme,job)values(empno,ename,job) when deptno in ('20','30') into emp2(empno,ename,deptno)values(empno,ename,deptno)select empno,ename,job,deptno from emp;

  同一条记录符合第一条判定条件后，不在进行后续的条件判定。

• 用其他表中的值更新

  在遇到只更新部分数据时，sql语句写错可能会引起整表的更新操作，如下：

  update emp     set emp.dname =         (select dept.dname from dept            where dept.deptno = emp.deptno                and dept.dname in ('salesman','manager'));

  这样操作会更新整个表，为满足条件的列被位置null

  正确的语句如下：

  update emp     set emp.dname =         (select dept.dname from dept            where dept.deptno = emp.deptno                and dept.dname in ('salesman','manager'))where exists (select dept.dname from dept            where dept.deptno = emp.deptno                and dept.dname in ('salesman','manager'));

  强烈建议用MERGE改写,访问效率最高：

  merge into emp using (select dname,deptno from dept            where dept.dname in ('salesman','manager')) depton (dept.deptno = emp.deptno)where matched then    update set emp.dname = dept.dname;

• 合并记录

  merge into这个语句，很厉害，暂时看不懂，需要在研究研究

第五章 使用字符串

• 遍历字符串

  creat or relace view v as select '奥格瑞玛' as 汉字，'agrm' as 首拼 from dual;select v.汉字,    v.首拼,    level,    substr(v.汉字,level,1) as 汉字拆分,    substr(v.首拼,level,1) as 首拼拆分,    'substr('''||v.汉字||''','||level ||',1)' as funfrom vconnect by level <= length(v.汉字); 

  效果如下，本人用的pl/sql，字符集设置可能有问题，导致效果不是很如意！

  

• 字符串文字中包含引号

  常常有人写sql时不知道字符串内的单引号怎么写，其实只要把一个单引号改成两个单引号就可以了。

  oracle 10g引入了q-quote特性，也就是使用Q或者q开头，字符串前后使用界定符“ ’ ”，规则如下：

  1）界定符可以是[]、{}、<>、（）而且必须成对出现。

  例子如下：

  select q'[g'day mate]' qmarks from dual union allselect q'<beavers' teeth>' from dual union allselect q'(')' from dual;

  

• 计算字符在字符串中出现的次数

  首先建立一个view：

  create or replace view v as select 'cleark,king,miller' as str from dual;

  oracle 11g给出了新函数 regexp_count,可以直接引用。

  select regexp_count() + 1 as cnt from v;select length(regexp_replace(str,'[^,]')) as cnt from v;实际效果是非逗号的字符被删掉了，然后返回了字符串的长度。

这里推荐一个讲 regexp_replace的博文http://blog.csdn.net/itmyhome1990/article/details/50380718

• 查询只包含字母或者数字型的数据

  regexp_like正则表达式的like

  1）regexp_like(date,’A’)对应普通的like ‘%A%’;

      select * from v where regexp_like(data,'A');

  2)regexp_like(date,’^A’)对应普通的like ‘A%’;

  3)regexp_like(date,’A$’)对应普通的like ‘%A’;

  4)regexp_like(date,’^A$’)对应普通的like ‘A’;

• 提取大写字母

  create or replace view v asselect 'Micheal Jackson' as a1 from dual;select regexp_replace(v.a1,'([[:upper:]])(.*)([[:upper:]])(.*)','\1\3') as sx from v;drop view v;

  

• 聚集字符串

  listagg分析函数http://dacoolbaby.iteye.com/blog/1698957

  with temp as(select 'China' nation ,'Guangzhou' city from dual union allselect 'China' nation ,'Shanghai' city from dual union allselect 'China' nation ,'Beijing' city from dual union allselect 'USA' nation ,'New York' city from dual union allselect 'USA' nation ,'Bostom' city from dual union allselect 'Japan' nation ,'Tokyo' city from dual )select nation,listagg(city,',') within GROUP (order by city)from tempgroup by nation

  

  with temp as(select 500 population, 'China' nation ,'Guangzhou' city from dual union allselect 1500 population, 'China' nation ,'Shanghai' city from dual union allselect 500 population, 'China' nation ,'Beijing' city from dual union allselect 1000 population, 'USA' nation ,'New York' city from dual union allselect 500 population, 'USA' nation ,'Bostom' city from dual union allselect 500 population, 'Japan' nation ,'Tokyo' city from dual )select population,nation,city,listagg(city,',') within GROUP (order by city) over (partition by nation) rankfrom temp

  

• 本章终点来了

  按字符顺序排列字符串
  ADAMS->ADMS
  经历三步：

  1）拆分2）用listagg合并3）处理全表数据，除去重复select ename,    (select listagg(MIN(substr(name,level,1))) within group(order by MIN(substr(ename,level,1))) from dual)connect by level <= length(ename)    group by substr(ename,level,1)) )as new_namefrom emp;