oracle查询重复数组,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;