SQL语句的优化三

mengxuyang

        WHERE条件中使用’in’子句的情况,如：
BF：select count(*) from staff_member
Where id_no in (‘0’,’1’);
WHERE条件中的'in'在逻辑上相当于'or'，所以语法分析器会将in ('0','1')转化为id_no ='0' or id_no='1'来执行。我们期望它会根据每个or子句分别查找，再将结果相加，这样可以利用id_no上的索引；但实际上（根据showplan）,它却采用了"OR策略"，即先取出满足每个or子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉重复行，最后从这个临时表中计算结果。我们可以将or子句分开：
RP：DECLARE
a integer;
        b integer;
BEGIN
   select count(*) into a from stuff where id_no='0';
   select count(*) into b from stuff where id_no='1';
a := a + b;
END;

        如果在table上创建了一个顺序为col1，col2，col3的复合index时，在查询中只有以下三种where条件子句能有效的使用index：
…where col1= @col1 and col2= @col2 and col3= @col3;
…where col1= @col1 and col2= @col2;
…where col1= @col1


子查询的调整

a.        调整具有IN和EXISTS子句的子查询
具有IN的子查询：
BF：
   select emp_id from EMP
   where dep_id IN ( select dep_id from DEP
where dep_no = ‘001’)；
具有EXISTS的子查询：
BF：
   select emp_id from EMP e
   where exists ( select dep_id from DEP d
where e.dep_id = d.dep_id
and d.dep_no = ‘001’)；
用对等连接调整具有IN和EXISTS的子查询：
上面的例子中的子查询有两种情况，dep_id unique和nounique。
当dep_id是unique,
RP：
   select e.emp_id from EMP e, DEP d
   where e..dep_id = d.dep_id and d.dep_no = ‘001’；

当dep_id是nounique,                                                                                                                                                   
RP：
  select e.emp_id from EMP e,
(select distinct dep_id from DEP where dep_no = ‘001’ ) d
where e..dep_id = d.dep_id；
需要注意的是，具有IN子句的非关联子查询和EXISTS子句的关联子查询，Oracle的优化器虽然能将其转换为标准的连接操作，但Oracle转换用的是NESTED LOOPS连接操作，而且有很多其他因素支配着SQL优化器是否将一个子查询自动转换为一个连接操作。首先，连接操作的两个数据表列通常都应该有唯一的数据索引。所以，我们应该自己重写这些子查询。

b.调整具有NOT IN和NOT EXISTS子句的子查询
具有NOT IN的子查询：
BF：
          select emp_id from EMP
          where dep_id NOT IN ( select dep_id from DEP
where dep_no = ‘001’)；
具有NOT EXISTS的子查询：
BF：
   select emp_id from EMP e
   where NOT EXISTS ( select dep_id from DEP d
where e dep_id = d. dep_id and .d.dep_no = ‘001’)；
用外联接调整具有NOT IN和NOT EXISTS的子查询
RP：
   select e.emp_id from EMP e,DEP d
   where e.dep_id = d.dep_id(+)
        and d.dep_id is null
        and d.dep_no (+)= ‘001’；

c调整具有自连接的子查询
  所谓具有自连接的子查询，实际上是在一种特殊需求下使用的具有IN子句的关联子查询。我们可以用连接的方式重写该子查询。例如以下的需求：
查询每个部门中工资高于该部门平均工资的员工ID,名称，工资，部门ID：
   BF：
select a.emp_id ,a.emp_name,a.emp_salary,a.dep_id from salary a
where a.emp_salary >
(select avg(b.emp_salary) from salary b where b.dep_id = a.dep_id )；
  
   RP：
select a.emp_id ,a.emp_name,a.emp_salary,a.dep_id  from salary a,
(select dep_id,avg(emp_salary) avg_salary from salary group by dep_id) b
where a.dep_id = b.dep_id
and a.emp_salary > b.avg_salary；

使用绑定变量优化SQL
使用绑定变量可以提高Library Cache的Hit Ratio，减少SQL语句的重编译，从而达到提高查询效率的目的。
BF：
SQL> alter system flush shared_pool;
系统已更改。
SQL> declare
  2    type rc is ref cursor;
  3    l_rc rc;
  4    l_dummy all_objects.object_name%type;
  5    l_start number default dbms_utility.get_time;
  6  begin
  7    for i in 1..1000
  8    loop
  9       open l_rc for
10       'select object_name
11           from all_objects
12        where object_id ='|| i;
13        fetch l_rc into l_dummy;
14        close l_rc;
15     end loop;
16     dbms_output.put_line
17     (round((dbms_utility.get_time - l_start)/100,2)||' seconds...');
18  end;
19  /
18.36 seconds...
PL/SQL 过程已成功完成。

这是从all_objects进行的单条查询，在循环1000次的情况下，Oracle对其进行的1000次编译，需要18.36秒才能完成。
RP：
SQL> alter system flush shared_pool;
系统已更改。
SQL> declare
  2    type rc is ref cursor;
  3    l_rc rc;
  4    l_dummy all_objects.object_name%type;
  5    l_start number default dbms_utility.get_time;
  6  begin
  7    for i in 1..1000
  8    loop
  9       open l_rc for
10       'select object_name
11           from all_objects
12        where object_id =:x'
13        using i;
14        fetch l_rc into l_dummy;
15        close l_rc;
16     end loop;
17     dbms_output.put_line
18     (round((dbms_utility.get_time - l_start)/100,2)||' seconds...');
19  end;
20  /
.56 seconds...
PL/SQL 过程已成功完成。
这是改用绑定变量之后的结果。这时Oracle对该PL/SQL 进行1次编译。执行时间明显减少。我在分别提交这两个PL/SQL 之前都执行了alter system flush shared_pool;命令，以保证对比结果的真实有效。

减少查询的次数

在一次执行多条SQL时，考虑正确的SQL顺序，减少查询的次数。如：
BF:
IF NOT EXISTS(SELECT count(*) FROM Item WHERE fchrItemID=@chrItemID and fchrA=@chrA)
    INSERT INTO Item (fchrItemID ,fchrItem ,fchrItemName , fchrA , flotQ , flotMQ )
VALUES (@chrItemID , @chrItem , @chrItemName , @chrA , -1*@lotQ , -1*@lotMQ )
else
      UPDATE Item SET flotQ=flotQ-@lotQ,flotMQ=flotMQ-@lotMQ
      WHERE fchrItemID=@chrItemID and fchrA=@chrA
对于这个SQl来说，select和update对Item做了两次查询操作。实际上我们只需要一次查询就可以实现功能。
RP：
UPDATE Item SET flotQ=flotQ-@lotQ,flotMQ=flotMQ-@lotMQ
WHERE fchrItemID=@chrItemID and fchrA=@chrA
IF @@rowcount = 0
    INSERT INTO @List (fchrItemID ,fchrItem ,fchrItemName , fchrA , flotQ , flotMQ )
    VALUES (@chrItemID , @chrItem , @chrItemName , @chrA , -1*@lotQ , -1*@lotMQ )

acong

PL/SQL

不是 T-SQL的语句

sdjiangfan

怎么有点乱乱的！