oracle 优化

1. 单表查询

1.1 空值转换成实际值

select coalesce(col, value) from dual;

coalesce 比NVL高级，支持多个col, 如 coalesce(col1, col2, value);

1.2 case when

SELECT T.SALARY, (CASE WHEN T.SALARY < 4000 THEN '4000-'
    WHEN T.SALARY < 5000 THEN '5000-'
   ELSE  
      '5000+'
   END ) LEVEL
FROM HR.EMPLOYEES T;

1.3 translate(expr, from_string, to_string)

select translate('ab 你好 bcadefg', 'abcdefg', '1234') from dual;

1.4 处理空值排序nulls first/nulls last

select t.employee_id, t.first_name, t.salary, t.manager_id 
from HR.EMPLOYEES t 
order by t.manager_id nulls first;

1.5 根据条件取不同列中的值来排序

select * from HR.EMPLOYEES t 
order by case when T.SALARY >= 3000 and t.salary < 1000 then 1 else 2 end, 3;

2. 操作多个表

2.1 union 去重

with e as (select rownum as sn, employee_id, email, job_id from HR.EMPLOYEES) 
select sn, EMPLOYEE_ID, email,job_id
from 
(
select sn, employee_id, email, job_id from e where E.JOB_ID='IT_PROG'
union 
select sn, employee_id, email, job_id from e where E.EMPLOYEE_ID='104'
)
order by 1;

2.2 外连接中的条件不要乱放

select ta.* from ta left join tb on  ta.id=tb.id where ta.no='1';

不同于

select ta.* from ta left join tb on  (ta.id=tb.id and ta.no='1');

3. insert update delete

3.1 阻止对某几列插入

创建不包含这几列的视图View，新增数据时通过view。

3.2 复制表的定义与数据

create table table_new as select * from table_base;  --定义、数据
create table table_new as select * from table_base where 1=2; -- 定义

复制表后，需要重新创建默认值和约束等信息。

3.3 用WITH CHECK OPTION 限制数据录入

alter table emp add constraints ch_sal check(sal>0); --简单

--使用加了With check option 的view来达到目的
insert into (select empno, ename, hiredate from emp where hiredate<=sysdate with CHECK OPTION) 
values(999, 'test', sysdate+1); -- 复杂

3.4 合并记录

merge into table_a 
using (select * from table_b) 
on (table_a.col_a = table_b.col_b)
when matched then
    update 
        set table_a.col_w = table_b.col_value;
    delete 
        where (condition_x)
when not matched then 
    insert(col_a, col_b)
    values(col_a_value, col_b_value); 
    where (condition_y)

3.5 删除违反参照完整性的纪录

alter table table_a add constraint fk_col_a foreign key(col_a) references table_b(col_b); 
//table_a 中 col_a存在外键约束时
delete from table_a 
where not exists (
    select 1 from table_b
    where table_b.col_b = table_a.col_a;
);