Oracle内联视图及其等价改写优化方法

Oracle内联视图（Inline View）是一种在SQL查询中直接嵌入子查询的构造，它允许您在FROM子句中定义一个临时的、只在当前查询上下文中存在的逻辑视图。这个“视图”实际上是子查询的结果集，它在查询执行期间被计算，但不会存储为持久的数据库对象。内联视图有助于简化复杂查询、抽象数据处理逻辑以及实现查询优化。

一、内联视图的特点

1. 临时性：内联视图仅在使用它的查询执行时存在，不会保存到数据字典中作为独立的对象，不像常规视图那样需要预先创建并命名。

2. 可嵌套：内联视图可以嵌套在其他内联视图内部，形成多层次的查询结构，以适应复杂的分析需求。

3. 绑定变量：在内联视图中可以使用绑定变量，允许动态传递参数，增强查询的灵活性和重用性。

4. 查询优化：Oracle的Cost-Based Optimizer (CBO)会对内联视图进行分析，可能对其进行视图合并（View Merge）、谓词推断（Predicate Pushdown）等优化操作，以生成高效的执行计划。

二、内联视图的用途

1. 简化复杂查询：通过将复杂的子查询封装成内联视图，可以将查询逻辑分解为易于理解和管理的部分。

2. 数据聚合：用于计算聚合值（如SUM、AVG、COUNT等）或分组统计，特别是在JOIN操作中提供中间结果。

3. 筛选和过滤：用于应用特定条件筛选数据，尤其是在涉及多表连接时，通过内联视图提前过滤掉不必要的行，减轻后续处理负担。

4. 分页查询：结合ROWNUM或RANK函数，实现分页结果的获取，特别是在不支持窗口函数的旧版Oracle中。

5. 模拟多表JOIN：当实际不存在物理关联时，可以通过内联视图模拟多表JOIN的效果，如实现自连接或交叉表查询。

三、内联视图的示例

示例1：简单内联视图

SELECT customer_name, total_sales
FROM (
  SELECT customer_id, SUM(sales_amount) AS total_sales
  FROM sales_table
  GROUP BY customer_id
) AS sales_summary
JOIN customers_table
ON sales_summary.customer_id = customers_table.customer_id;

在这个示例中，内联视图sales_summary计算每个客户的总销售额，然后与customers_table联接，以显示客户名称及其对应的总销售额。

示例2：嵌套内联视图与视图合并

SELECT product_name, AVG(quantity_sold)
FROM (
  SELECT p.product_name, s.quantity_sold
  FROM products p
  JOIN (
    SELECT product_id, SUM(quantity) AS quantity_sold
    FROM sales
    WHERE sale_date BETWEEN :start_date AND :end_date
    GROUP BY product_id
  ) s ON p.product_id = s.product_id
)
GROUP BY product_name
HAVING AVG(quantity_sold) > 100;

此查询使用了两个内联视图：一个计算指定日期范围内各产品的销售总量，另一个查询则将产品名称与销售量联接，并按产品名称分组，计算平均销售量。如果CBO认为合适，它可能会将内联视图合并到外部查询中进行优化。

示例3：分页查询

SELECT *
FROM (
  SELECT t.*, ROWNUM AS rn
  FROM (
    SELECT *
    FROM employees
    ORDER BY hire_date DESC
  ) t
)
WHERE rn BETWEEN :page_start AND :page_end;

这个例子展示了如何使用内联视图结合ROWNUM函数实现分页查询。外部查询选择内联视图中行号（rn）在给定分页范围内的记录。

四、注意事项

• 性能影响：虽然内联视图提供了查询的灵活性，但不当使用可能导致性能下降。确保CBO能够有效优化内联视图，避免不必要的计算重复或全表扫描。

• 可读性：过度使用内联视图或嵌套过深可能降低查询的可读性和维护性。合理组织查询结构，保持代码清晰。

• 绑定变量：使用绑定变量代替硬编码值，可以提高查询效率，尤其是对于频繁执行的查询，避免SQL解析重复。

五、内联视图等价改写优化方法

1.内联视图等价改写的目的

1. 简化查询结构：将复杂的内联视图改写为更直观、易于理解的形式。
2. 优化性能：调整内联视图的结构或表达方式，以便Oracle Cost-Based Optimizer (CBO)能生成更高效的执行计划。
3. 遵循特定规范或要求：在某些情况下，可能需要遵循特定的编码规范、避免使用内联视图，或者满足特定的查询接口要求。

2.内联视图等价改写的方法

方法一：将内联视图替换为临时表或物化视图

将内联视图的结果集保存到临时表或物化视图中，然后在主查询中引用这些临时对象。这适用于内联视图计算结果复杂且需要多次使用的情况，或者为了改善查询性能。

示例：

-- 原内联视图查询
SELECT * 
FROM (
  SELECT customer_id, SUM(amount) AS total_amount
  FROM transactions
  GROUP BY customer_id
) v
WHERE total_amount > 1000;

-- 改写为临时表
CREATE GLOBAL TEMPORARY TABLE temp_customers_totals (
  customer_id INT,
  total_amount DECIMAL(10,2)
) ON COMMIT PRESERVE ROWS;

INSERT INTO temp_customers_totals
SELECT customer_id, SUM(amount) AS total_amount
FROM transactions
GROUP BY customer_id;

SELECT * 
FROM temp_customers_totals
WHERE total_amount > 1000;

-- 或者，改写为物化视图（适用于长期重复使用的场景）
CREATE MATERIALIZED VIEW mv_customers_totals
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT customer_id, SUM(amount) AS total_amount
FROM transactions
GROUP BY customer_id;

SELECT * 
FROM mv_customers_totals
WHERE total_amount > 1000;

方法二：将内联视图展开到主查询中

将内联视图的子查询直接嵌入到主查询的相应位置，合并为一个单一查询。这适用于内联视图相对简单，展开后不会导致查询过于冗长或复杂的情况。

示例：

-- 原内联视图查询
SELECT customer_name, v.total_amount
FROM (
  SELECT customer_id, SUM(amount) AS total_amount
  FROM transactions
  GROUP BY customer_id
) v
JOIN customers ON v.customer_id = customers.customer_id;

-- 改写为直接嵌入子查询
SELECT customers.customer_name, SUM(transactions.amount) AS total_amount
FROM transactions
JOIN customers ON transactions.customer_id = customers.customer_id
GROUP BY customers.customer_id;

方法三：使用CTE（Common Table Expression，公用表表达式）

将内联视图替换为CTE（如果数据库版本支持），可以提高查询的可读性。CTE本质上也是一种临时结果集，但与内联视图相比，它提供了更清晰的命名和更好的代码组织。

示例：

-- 原内联视图查询
SELECT * 
FROM (
  SELECT customer_id, SUM(amount) AS total_amount
  FROM transactions
  GROUP BY customer_id
) v
WHERE total_amount > 1000;

-- 改写为CTE
WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_amount
  FROM transactions
  GROUP BY customer_id
)
SELECT * 
FROM customer_totals
WHERE total_amount > 1000;

方法四：将内联视图中的多层嵌套拆分为多个查询

如果内联视图包含多层嵌套，可以尝试将其拆分为多个单独的查询，然后通过临时表、JOIN操作或其他手段组合结果。这有助于逐步简化复杂查询，便于理解和调试。