2014-10-04 BaoXinjian

一、摘要

二、使用复杂子查询with as来提高sql性能

利用复杂子查询with as来提高sql性能。

先看个例子：

WITH dept_costs AS (  SELECT   d.department_name, SUM (e.salary) AS dept_total                        FROM      employees e                               JOIN                                  departments d                               ON e.department_id = d.department_id                    GROUP BY   d.department_name),    avg_cost       AS (SELECT   SUM (dept_total) / COUNT ( * ) AS dept_avg             FROM   dept_costs)  SELECT   *    FROM   dept_costs   WHERE   dept_total > (  SELECT   dept_avg FROM avg_cost)ORDER BY   department_name;

在这个例子中，dept_costs相当于一个view，与view不同的是，...

用CASE EXPRESSION将

对数据的读取操作是非常消耗资源的,如何减少对数据的扫描,是提升sql效率的一个重要方面,例如物化视图技术。本篇介绍几种sql写法，分别是CASE expression/DML with returning clause /multitable insert。

二、用CASE EXPRESSION将多句查询组合在一起

SELECT   COUNT ( * )  FROM   employees WHERE   salary < 2000;SELECT   COUNT ( * )  FROM   employees WHERE   salary BETWEEN 2000 AND 4000;SELECT   COUNT ( * )  FROM   employees WHERE   salary > 4000;

改写成

SELECT   COUNT (CASE WHEN salary < 2000 THEN 1 ELSE NULL END) count1,         COUNT (CASE WHEN salary BETWEEN 2001 AND 4000 THEN 1 ELSE NULL END)            count2,         COUNT (CASE WHEN salary > 4000 THEN 1 ELSE NULL END) count3  FROM   employees;

二、DML with returning clause

有时候DML操作后紧跟着对这些行的select操作。DML with returning clause将insert/update/delete操作和select操作合并在一起，避免了重复扫描。
例子：

   UPDATE   employees      SET   job_id = ‘SA_MAN‘, salary = salary + 1000, department_id = 140    WHERE   last_name = ‘Jones‘RETURNING   salary * 0.25, last_name, department_id     INTO   :bnd1, :bnd2, :bnd3;

如果修改的是多行记录，则returning clause返回到数组变量中。

三、 multitable insert

一句sql包含多条insert的写法。相比于多句insert语句，前者只需扫描源table一次，而后者要扫描多次。这在数据量很大的情况下性能差别非常大。Multitable insert可分为conditional(有条件的),unconditional,insert all（执行所有insert）,insert first(只执行第一个满足条件的insert)等不同用法
有时候表里面一行记录包含了多类信息，如某一话单表中包含了一个设备多种话务类型的费用,这种表我们称为非关系表。下面例子应用oracle multitable insert技术将非关系表转换为关系型表。
create table test_table (id1 number,val_1 number,id2 number,val_2 number); --非关系表
insert into test_table values(1,101,1,202);
insert into test_table values(6,666,7,777);

--基表
create table id_type (a number);
insert into id_type values(1);
insert into id_type values(2);
insert into id_type values(3);
--目标表（将非关系表转换为两张关系表）
create table id1_table (id number,val number);
create table id2_table (id number,val number);

--普通方法，用多条insert语句
insert INTO id1_table select id1,val1 from test_table;
insert INTO id2_table select id2,val2 from test_table;

--用multiple insert，将非关系型表数据一条记录拆成两条,分别插入两张目标表
insert all
INTO id1_table values(id1,val_1)
INTO id2_table values(id2,val_2)
select id1,val_1,id2,val_2 from test_table;

--有判断条件的multiple insert
insert all
WHEN id1 =1 then
INTO id1_table values(id1,val_1)
when ID2 =5 then
INTO id2_table values(id2,val_2)
select id1,val_1,id2,val_2 from test_table;

--multiple insert还有更复杂的写法，判断条件可以用子查询
insert all
WHEN id1 in(select a from id_type) then
INTO id1_table values(id1,val_1)
when ID2 in(select a from id_type) then
INTO id2_table values(id2,val_2)
select id1,val_1,id2,val_2 from test_table;

参考： luw 百度文库资料

PLSQL_性能优化系列13_Oracle DataScan