oracle sql 高级编程学习笔记（五）

喜欢ヅ旅行 2022-05-15 02:07 334阅读 0赞

## 一、 查询块 ##

查询块根据select关键字定义，查询块要么嵌套在一个查询块中，要么以某种方式与一个查询块存在连接。查询书写的方式决定了查询块之间的关系。

select t.* from hr.employees t where t.department_id=60; 只有一个查询
    select t.* from hr.employees t where t.department_id in (select d.department_id from hr.departments d) --两个查询块

## 二、查询转换 ##

查询转换，在查询通过语法和权限检查通过之后，查询就进入转化为一切查询块阶段。  
查询转换的目的就是确定改变查询书写的方式会不会提供更好的执行计划。  
查询转换能够并且可能会重写你的查询。这样的改变并不会影响你查询的结果集。  
最常用的改变就是将独立的查询块转换为直接联结。

select t.* from employees t where t.department_id in(select d.department_id from departments d );

改为

select t.* from employees t ,departments d where t.department_id=d.department_id ;

结果集没有改变，但从优化器的角度来说转换后的版本执行计划的选择将会更好 。  
No\_query\_transformation，使用此提示后，除了谓语前推以外的查询转换都将被禁止。

**1、视图合并**

常常发生在  
1）能够在另外一个查询块的索引中使用的列  
2）能够在另外一个查询块的分区截断中所使用的列  
3）在一个联结视图中能够限制返回行数的条件  
–另外一个查询块的索引中使用的列  
select t.\* from orders t,(select o.sales\_rep\_id from orders o ) o\_view  
where t.sales\_rep\_id=o\_view.sales\_rep\_id  
and t.order\_total>100000;  
执行计划如下:

![这里写图片描述][70]

1.1 No\_query\_transformation 这个参数告诉优化器跳过查询转换，但是不限制or，视图合并，子查询unnesting，星型转换，物化视图重写

select /*+ No_query_transformation*/t.* from orders t,(select o.sales_rep_id from orders o ) o_view where t.sales_rep_id=o_view.sales_rep_id and t.order_total>100000 ;

执行计划如下  
![这里写图片描述][70 1]  
可以看到执行计划不进行视图合并中 第三步 view 保持原样，进行单独处理，与外部的orders表联结前，就要对orders表进行全表扫描。  
而视图合并中是将计算合并为一个计划，而不是让内嵌视图保持独立。

1.2 no\_merge 提示 防止内嵌视图被合并

select t.* from orders t,(select /*+ no_merge*/o.sales_rep_id from orders o ) o_view where t.sales_rep_id=o_view.sales_rep_id and t.order_total>100000;

执行计划  
![这里写图片描述][70 2]  
1.3 其他情况也会阻止视图合并 ：查询块中出现解析函数，聚合函数，集合运算（union intersect minus，） order by 子句或者使用了rownum。即使出现了这些情况可以通过merge提示来强制视图合并。但使用了merge强制合并，你必须确认查询结果在视图合并之后任然正确。

--未使用 /*+merge*/提示
    select e1.last_name,e1.salary,e1.department_id
      from employees e1,
           (select e2.department_id, avg(e2.salary) avg_sal
              from employees e2
             group by e2.department_id) v
     where e1.department_id = v.department_id
       and e1.salary > v.avg_sal;

sql中使用了聚合函数，所以就会阻止视图合并，执行计划如下  
![这里写图片描述][70 3]

--使用提示
       select /*+merge(v)*/ e1.last_name,e1.salary,e1.department_id from employees e1, (select e2.department_id, avg(e2.salary) avg_sal from employees e2 group by e2.department_id) v where e1.department_id = v.department_id and e1.salary > v.avg_sal;

使用merge提示后，强制优化器选择了视图合并，执行计划如下  
![这里写图片描述][70 4]

**2、子查询嵌套**  
子查询嵌套与视图合并相似之处在于子查询也是通过一个单独的查询块来表示的，可合并的视图与可以解嵌套的子查询之间的主要区别在于他们的位置不同  
1）子查询位于where 语句之后；  
2）当子查询不相关时，转化查询时非车直接的

select t.* from employees t where t.department_id in(select d.department_id from departments d );
    
    改为
    select t.* from employees t ,departments d where t.department_id=d.department_id ;

执行计划 如下  
![这里写图片描述][70 5]  
/*+ NO\_UNNEST*/ 提示 意味着子查询单独生成一个查询块

select  t.* from  employees t  where t.department_id in(select /*+ NO_UNNEST*/ d.department_id from  departments d)

不使用查询转换将选用Filter 运算而不是nested loops进行连接。filter是最典型的低效的将两个表进行连接。当然nested loops 也不一定是最优。还有待讨论

![这里写图片描述][70 6]  
2.2 联结子查询的解嵌套转换

select outer.last_name, outer.salary, outer.department_id from employees outer where outer.salary > (select avg(inner.salary) from employees inner where inner.department_id = outer.department_id group by inner.department_id);

–将子查询转换为一个内嵌视图，然后再与其外联的查询合并，联结子查询解嵌套如下：

select outer.last_name, outer.salary, outer.department_id from employees outer, (select department_id, avg(salary) sal from employees group by department_id) inner where outer.salary > inner.sal and outer.department_id = inner.department_id ;

子查询的解嵌套 是由 参数 \_unnest\_subquery 控制，修改语法 true 表示允许子查询嵌套，sql会被重写  
但是从oracle 10 开始 转换后的查询将有优化器进行复核，然后根据成本来确定解嵌套后的版本是不是更优，所以  
修改该参数强制允许子查询嵌套，不一定起作用。  
**3、谓语前推**  
意义：就是早点进行数据筛选  
例子：你从A城市搬到B城市 ，需要找搬家公司，按重量收费，而你把80%不需要的东西也搬走显然不是好主意，除非你是土豪，可以不在乎。所以在搬家公司帮你打包前，你先对东西进行整理是很明智的选择。

--  set autotrace traceonly explain 查看执行计划、统计信息不返回sql结果集  sqllpus 才可以用 plsql 客户端不能;
    --set linesize 160 设置sqlplus 窗口显示一行的宽度，默认值是80最大值999
              select e1.last_name, e1.salary
                from employees e1,
                     (select e.department_id,
                             avg(e.salary) sal from employees e group by e.department_id) e2
               where e1.department_id = e2.department_id
                 and e1.salary > e2.sal
                 and e1.department_id = 60;

执行计划  
– 执行计划中可以看到， 第6步 where e1.department\_id = 60 被推进视图中 使得视图仅计算一个部门的平均薪水  
![这里写图片描述][70 7]

select e1.last_name, e1.salary from employees e1, (select e.department_id, avg(e.salary) sal from employees e where rownum>1 --rownum 会禁止谓语前推 group by e.department_id) e2 where e1.department_id = e2.department_id and e1.salary > e2.sal and e1.department_id = 60;

![这里写图片描述][70 8]  
–而上面视图中并没有将where e1.department\_id = 60推进视图，所以视图需要计算每个部门的平均薪水，然后当外部查询块与内部查询块联结时再将不是60的部门剔除掉，当然次计划的成本会比第一个计划的成本高  
–在本例中使用了rownum来禁止谓语前推，当然rownum也能禁止视图合并  
**4、使用物化视图进行查询重写**

查询重写是一种发生在当一个查询或查询的一部分已经被保存为一个物化视图，转换器重写该查询以使用预先计算好的物化视图数据而不需要执行当前查询的转换。物化视图与普通视图的区别在于查询已经被执行并将结果集存人了一张表中。这样做的好处就是预先计算厂查询的结果并且在特定查询执行的时候可以直接调取该结果。也就是说所有的确定执行计划、执行查询以及收集所有数据的工作都已经做完了。因此，当同样的查询再一次执行的时候，就不需要再做一遍了。

查询转换器会将查询与可用的物化视图相匹配，然后重写该查询以直接从物化结果集中选取  
査询数据 。

select p.prod_id,
           p.prod_name,
           t.time_id,
           t.week_ending_day,
           s.channel_id,
           s.amount_sold,
           s.cust_id
      from sales s, products p, times t
     where s.time_id = t.time_id
       and s.prod_id = p.prod_id;

![这里写图片描述][70 9]

--关闭 返回查询计划 创建物化视图
      create materialized view sales_time_product_mv
       enable query rewrite as
       select p.prod_id,
           p.prod_name,
           t.time_id,
           t.week_ending_day,
           s.channel_id,
           s.amount_sold,
           s.cust_id
      from sales s, products p, times t
     where s.time_id = t.time_id
       and s.prod_id = p.prod_id;

--rewrite 打开查询重写转换
    select /*+rewrite(sales_time_product_mv)*/p.prod_id,
           p.prod_name,
           t.time_id,
           t.week_ending_day,
           s.channel_id,
           s.amount_sold,
           s.cust_id
      from sales s, products p, times t
     where s.time_id = t.time_id
       and s.prod_id = p.prod_id;

执行计划如下，在发生重写时，执行计划仅仅列出了对物化视图的完全访问而不是初始结果集的时候所需要执行的整个运算集。对于具有很大结果集的复杂查询，时间的节省会非常显著，特别是当查询包含聚合时。  
![这里写图片描述][70 10]

[70]: /images/20220515/5fe62114487a497cb51eaf42ad674a64.png
[70 1]: /images/20220515/c9abe67c894347d0a655ef119f6593c4.png
[70 2]: /images/20220515/e27fb14c19c9471bab916be488670c09.png
[70 3]: /images/20220515/a9a243de01e3495f83859c42dbabbb9a.png
[70 4]: /images/20220515/c4a8f064066846298c890405d3584b6d.png
[70 5]: /images/20220515/0bf2e72e631d427ca34ad0f4550ff00f.png
[70 6]: /images/20220515/72d1459d583f452186c74b5521b1cdc6.png
[70 7]: /images/20220515/76cf8a571dbb4374a2e0c57f5fa2cf87.png
[70 8]: /images/20220515/e01e378d63df4090aae85cf50bcc2d27.png
[70 9]: /images/20220515/c47bf7e8e0154599a62d7d0943d82c73.png
[70 10]: /images/20220515/f378878b63d84ae5b8ff0fbe564b9526.png