oracle sql 高级编程学习笔记（二十四)

痛定思痛。 2022-05-11 05:48 246阅读 0赞

###  ###

*   *  子查询因子化实例演示
     *  1、使用子查询因子化
     *  2、使用INLINE提示，查询因子做内联处理。
     *  3、再增加一次结果集的引用
     *  4、增加一次结果集 使用内联 \*+inline

在oracle 官方文档中有如下一段话：  
注意，oracle 可能将因子化的子查询作为临时表来处理，在一个表被多次引用的查询中，这可能是独特  
的性能优势，因为oracle可以物化查询结果集 从而避免多次执行一些非常耗费  
资源的数据库运算。在这里需要注意的是，这种优化只是可能的独特性能优势。需要牢记于心的一点是物化结果集需要创建  
一个临时表并将数据行插入其中。如果同一个结果集会被多次引用，这样做可能是很值得的，否则就有可能极大的降低性能。  
–用materialize提示将查询因子物化成临时表。（不加提示时，本例也会默认采用这种办法）

## 子查询因子化实例演示 ##

--测试前 先在dba权限清除缓存
    ALTER SYSTEM FLUSH SHARED_POOL;
    ALTER SYSTEM FLUSH BUFFER_CACHE;
    ALTER SYSTEM FLUSH GLOBAL CONTEXT;

## 1、使用子查询因子化 ##

with cust as
     (select /*+materialize gather_plan_statistics*/
       b.cust_income_level, a.country_name
        from customers b
        join countries a
          on a.country_id = b.country_id)
    select country_name,
           cust_income_level,
           count(country_name) country_cust_count
      from cust
    having count (country_name) > (select count(1) * 0.01 from cust c2) 
    or count (cust_income_level) >= 
    --median  用于计算中位数
    (select median(income_level_count) from (
    select cust_income_level,count(1) * 0.25 income_level_count from cust
       group by cust_income_level))
    group by  country_name,
           cust_income_level
           order by 1,2;
           --根据第一列和第二列排序 
    既相当于 order by  country_name,  cust_income_level

执行后  
获取sqlid

select  t.SQL_TEXT,t.SQL_ID,t.CHILD_NUMBER from  v$sql t  where t.SQL_TEXT like '%materialize%';

![在这里插入图片描述][70]

查看执行计划：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f26yd87mjkfwg',0,'ALLSTATS LAST'));

结果如下:  
![在这里插入图片描述][70 1]

可以看到customers以及countries表的联结经过了一个临时表转化( TEMP TABLE TRANSFORMATION),接下来的查询都会用到这个临时表SYS\_TEMP\_0FD9D6609\_2630CA，并且由于创建了临时表，产生了物理读取 reads 既IO

## 2、使用INLINE提示，查询因子做内联处理。 ##

with cust as
     (select /*+ inline gather_plan_statistics*/ 
       b.cust_income_level, a.country_name
        from customers b
        join countries a
          on a.country_id = b.country_id)
    select country_name,
           cust_income_level,
           count(country_name) country_cust_count
      from cust
    having count (country_name) > (select count(1) * 0.01 from cust c2) 
    or count (cust_income_level) >= 
    (select median(income_level_count) from (
    select cust_income_level,count(1) * 0.25 income_level_count from cust
       group by cust_income_level))
    group by  country_name,
           cust_income_level
           order by 1,2;

执行完后查看 sql\_id

select  t.SQL_TEXT,t.SQL_ID,t.CHILD_NUMBER from  v$sql t  where t.SQL_TEXT like '%inline%';
    执行计划

![在这里插入图片描述][70 2]

可以看到对customers表进行了三次全表扫描以及countries进行了一次全表扫描对cust子查询进行了两次索引全扫描  
同时由于全表扫描也产生了物理读取，有意思的是相对物化既第一个sql中执行计划，少了300多个IO。而时间也是比 第一个提高了一半；

## 3、再增加一次结果集的引用 ##

在查看执行计划时，发现需要执行两次sql，不然得不到执行计划 会有如下提示 未找到原因  
![在这里插入图片描述][70 3]

with cust as
    
     (select /*+materialize gather_plan_statistics*/
       b.cust_income_level, a.country_name
        from customers b
        join countries a
          on a.country_id = b.country_id),
          median_income_set as  
         (select /*+materialize*/ cust_income_level,count(1) income_level_count  from cust
          group by cust_income_level 
          having count(cust_income_level)>(
          select  median(income_level_count) income_level_count from   (
          select  cust_income_level,count(1) income_level_count from   cust group by cust_income_level
          )
          ))
    select country_name,
           cust_income_level,
           count(country_name) country_cust_count
      from cust c
    having count (country_name) > (select count(1) * 0.01 from cust c2) 
    or cust_income_level in(select  mis.cust_income_level from   median_income_set mis)
    group by  country_name,
           cust_income_level
           order by 1,2
    ;

执行计划  
![在这里插入图片描述][70 4]

## 4、增加一次结果集 使用内联 \*+inline ##

with cust as
     (select /*+inline gather_plan_statistics*/
       b.cust_income_level, a.country_name
        from customers b
        join countries a
          on a.country_id = b.country_id),
          median_income_set as  
         (select /*+inline*/ cust_income_level,count(1) income_level_count  from cust
          group by cust_income_level 
          having count(cust_income_level)>(
          select  median(income_level_count) income_level_count from   (
          select  cust_income_level,count(1) income_level_count from   cust group by cust_income_level
          )
          ))
    select country_name,
           cust_income_level,
           count(country_name) country_cust_count
      from cust c
    having count (country_name) > (select count(1) * 0.01 from cust c2) 
    or cust_income_level in(select  mis.cust_income_level from   median_income_set mis)
    group by  country_name,
           cust_income_level
           order by 1,2;

执行计划  
![在这里插入图片描述][70 5]

增加一次结果集的引用后发现，子查询因子话 的逻辑读取只有3000左右 比内联 少了仅2w，而且时间也是内联的一半。可见子查询因子话即将  
结果集写入到临时表中的效率在多次重用的效率明显提高。

[70]: /images/20220511/42f56c09595041e08b73478a01523949.png
[70 1]: /images/20220511/43fb60f59b104e2da5be997522ff69cd.png
[70 2]: /images/20220511/cc703278a3694cfc835b5d460096ea23.png
[70 3]: /images/20220511/6793f4593bc94004a604db34c2980d01.png
[70 4]: /images/20220511/b430ab0d95d24412aab5f21d662e5d78.png
[70 5]: /images/20220511/911f85b309124bdc8ce61371b33064cd.png