Oracle WITH AS用法优点缺点分析

一、简介

Oracle WITH AS是一种SQL语法，用于在一个查询中定义一个临时的命名结果集，并在查询中引用该结果集，它是Oracle中实现递归查询的一种方式。当一次查询需要多次引用同一个子查询时，使用WITH AS语法可以便于编写和维护SQL查询语句。

二、优点

1、提高查询效率

使用WITH AS语法可以提高SQL查询效率。因为在多次使用同一个子查询时，如果每次都重新执行该子查询，会造成SQL语句的冗余和性能底下。而使用WITH AS可以将该子查询的结果集存储在内存中，并在多次引用时直接使用它，避免重复执行子查询，从而大大提高SQL查询效率。

2、简化SQL语句

使用WITH AS语法可以让SQL语句更易于编写和维护。当在一个查询中需要多次引用同一个子查询结果集时，不使用WITH AS语法需要在SQL语句中多次复制子查询的代码，而使用WITH AS可以将子查询的结果集给命名为一个临时表，并在后续查询中仅仅需要引用这一个表，从而避免了多次复制和粘贴的操作，使SQL语句更为简洁。

3、支持递归查询

WITH AS语法支持递归查询，是一种实现树形结构查询的有效手段。通过建立递归查询的一个公共表达式，使得在同一个查询中实现多个层次或深度的查询，简化了查询的逻辑实现，使得SQL查询变得更加易于理解和处理。

三、缺点

1、内存占用较高

在WITH AS语法中，子查询的结果集会被存储在内存中，如果该子查询结果集较大，会占用大量的内存资源，导致内存使用率较高，如果处理大量复杂的数据，可能会导致内存不足的问题。

2、可读性较差

尤其是当WITH AS语法的数据表达比较复杂的时候，SQL语句的可读性会比较差，阅读、理解、维护难度较大。所以，对需要复杂嵌套的SQL语句，需要慎重使用WITH AS语法。

3、语句冗余

使用WITH AS语句处理较小复杂度的递归查询比较常见。然而，在处理递归查询时，因为递归查询需要使用自己的结果集，所以必须要将公共子查询嵌套在WITHIN 语句中。这样就会出现 SQL 语句嵌套较深，变得复杂，语句冗余等问题。同时，还有FROMtoWHERE的查询范围问题，对于常规sql语句，查询的结果集是由FROM 语句中的表决定的，而对于WITH AS 语句，结果集是由查询部分决定的，同时查询部分的执行次数，也将会影响到结果集的大小和查询数据的时间。

四、代码

下面是一个使用WITH AS语法实现递归查询的示例代码：

WITH recursivequery AS
(
  SELECT id, name, parent_id FROM organization_structure WHERE id = 1

  UNION ALL

  SELECT o.id, o.name, o.parent_id FROM organization_structure o, recursivequery r WHERE o.parent_id = r.id
)
SELECT * FROM recursivequery;