oracle - 我们什么时候使用 WITH 子句，它的主要好处是什么？

Question

我正在处理有关优化查询的任务。其中一种改进方法是使用 WITH 子句。我注意到它做得非常好，它导致执行时间更短，但我现在不确定，什么时候应该使用 WITH 子句，使用它有风险吗？

这是我正在处理的查询之一:

WITH MY_TABLE AS 
    (   SELECT PROD_KY,
               sum(GROUPISPRIVATE) AS ISPRIVATE,
               sum(GROUPISSHARED) AS ISSHARED
        FROM
             (
                SELECT GRP_PROD_CUSTOMER.PROD_KY,
                       1 as ISPRIVATE,
                       0 as ISSHARED
                FROM CUSTOMER
                JOIN GRP_CUSTOMER ON GRP_CUSTOMER.CUST_KY = CUSTOMER.CUST_KY
                JOIN GRP_PROD_CUSTOMER ON GRP_PROD_CUSTOMER.GRP_KY = GRP_CUSTOMER.GRP_KY                                                                                                                                                                                                                                                                                                            
                GROUP BY GRP_PROD_CUSTOMER.PROD_KY
             ) 
   GROUP BY PROD_KY
)
SELECT * FROM MY_TABLE;

Answer 1

is there any risk of using it?

是的。 Oracle 可能决定实现子查询，这意味着将其结果集写入磁盘，然后再将其读回(除非它可能不意味着在 12cR2 或更高版本中)。意外的 I/O 可能会影响性能。并非总是如此，通常我们可以相信优化器会做出正确的选择。但是，Oracle 已经为我们提供了提示，告诉优化器如何处理结果集: /*+ materialize */嗯实现它和 /*+ inline */把它留在内存中。

我从这个潜在的缺点开始，因为我认为理解 WITH 子句不是 Elixir 很重要，它不会改善每个查询，甚至可能会降低性能。例如，我与其他评论者一样怀疑您发布的查询在任何方面都更快，因为您将其重写为公用表表达式。

通常， WITH 子句的用例是:

我们想多次使用子查询的结果集

with cte as
  ( select blah from meh )
select * 
from t1
     join t2 on t1.id = t2.id
where t1.col1 in ( select blah from cte )
and   t2.col2 not in ( select blah from cte)

我们想要构建一个级联的子查询:

with cte as
  ( select id, blah from meh )
  , cte2 as 
   ( select t2.*, cte.blah
     from cte
          join t2 on t2.id = cte.id)
  , cte3 as 
    ( select t3.*, cte2.*
      from cte2
           join t3 on t3.col2 = cte2.something ) 
   ….

第二种方法很吸引人，对于在纯 SQL 中实现复杂的业务逻辑很有用。但它可能会导致程序性思维方式并失去权力集和加入。这也是一种风险。

我们想使用递归 WITH 子句。这允许我们用更标准的方法替换 Oracle 自己的 CONNECT BY 语法。 Find out more

在 12c 及更高版本中，我们可以在 WITH 子句中编写用户定义的函数。这是一个强大的功能，特别是对于需要在 PL/SQL 中实现一些逻辑但对数据库只有 SELECT 访问权限的用户。 Find out more

作为记录，我已经看到了第二类 WITH 子句的一些非常成功且高性能的用法。但是，当编写内联 View 同样容易时，我也看到了 WITH 的使用。例如，这只是使用 WITH 子句作为语法糖......

with cte as
  ( select id, blah from meh )
select t2.*, cte.blah 
from  t2
      join cte on cte.id = t2.id

......并且会更清楚......

select t2.*, cte.blah 
from  t2
      join ( select id, blah from meh ) cte on cte.id = t2.id