Sql优化(十一) 避免对数据的重复扫描（1）

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

一、 用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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18474/viewspace-1060742/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18474/viewspace-1060742/