深入解析Oracle优化器中的查询转换操作

摘要：本文将详细介绍Oracle数据库优化器中的查询转换操作。查询转换是优化器在执行查询计划选择过程中的关键步骤之一，它可以通过重写和改变查询语句的结构，提供更高效的执行计划，从而优化查询性能。本文将深入探讨Oracle优化器中的查询转换技术，包括谓词下推、子查询转换、视图合并等常见的转换操作，并提供一些实用的示例。

1. 查询转换的背景和意义

在查询执行过程中，Oracle优化器的任务是选择最佳的执行计划，以提供最优的查询性能。而查询转换作为其中的一项关键技术，它通过改变查询语句的结构，使得优化器能够更有效地评估和选择查询计划。查询转换可以用于优化查询的性能、简化查询的语法和逻辑，从而提高查询执行效率和可读性。

2. 常见的查询转换操作

2.1 谓词下推（Predicate Pushdown）

谓词下推是一种常见的查询转换操作，它将查询中的谓词条件（例如WHERE子句中的条件）尽早地应用于表之上。这种转换操作可以减少查询所需的数据量，提高查询性能。

示例：

-- 未进行谓词下推的查询
SELECT * FROM (SELECT * FROM t1 UNION SELECT * FROM t2) WHERE id=1;

-- 进行谓词下推的查询
SELECT *
FROM (
SELECT * FROM t1 WHERE id=1
UNION
SELECT * FROM t2 WHERE id=1);

过滤谓词下推将限制条件(id=1)下推到内联视图的内部并产生了下面的查询。现在，这两张表不仅可以通过索引来访问，同时也保证了UNION集合运算符需要的排序操作所处理的记录尽可能少：

2.2 子查询转换（Subquery Unnesting）

子查询转换是指将子查询（嵌套在主查询中的查询语句）转换为等效的连接操作，以提高查询性能。优化器会尝试将子查询转换为连接查询，从而减少查询的复杂性和执行时间。

示例：

-- 子查询转换前的查询
SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 100);

-- 子查询转换后的查询
SELECT employees.* FROM employees JOIN departments ON employees.department_id = departments.department_id
  WHERE departments.location_id = 100;

2.3 视图合并（View Merging）

视图合并是指将涉及到多个视图的查询转换为等效的单个查询，以减少查询的复杂性和执行时间。视图合并可以将多个视图的定义合并为一个查询，从而避免了查询中的冗余计算和表连接操作。

示例：

-- 视图合并前的查询
SELECT *
FROM (SELECT t1.* FROM t1, t2 WHERE t1.id = t2.t1_id) t12,
     (SELECT * FROM t3 WHERE id > 6) t3
WHERE t12.id = t3.t1_id;

-- 视图合并后的查询
SELECT t1.*,t3.*
FROM t1, t2,t3
WHERE t1.id = t3.t1_id and t1.id = t2.t1_id and t3.id > 6;

2.4 “或”扩张（Or Expansion）

“或”扩张的目的是将查询的WHERE条件中包含分隔谓词的语句转化为使用一个或多个UNION ALL集合运算符的复合查询。通常情况下，每个分隔的谓词被转化成为一个单独的查询从而启用额外的索引访问路径。

示例1：

-- 原SQL
select t.employee_id from employees t where t.department_id = 10 or t.job_id = 'SH_CLERK';

-- 查询优化器会评估一次基于表扫描的成本是否高于两次单独的基于索引扫描的成本
-- 如果两次索引扫描的成本更低就会转换为以下查询
-- 添加lnnvl(n1=1)这个谓词是为了避免多重记录。lnnvl函数在作为参数传递的条件为FALSE或NULL时返回TRUE。因此，第二个组件查询只会在第一个组件查询未返回某条记录的情况下才返回这条记录：

select t.employee_id from employees t where t.department_id = 10
union all
select t.employee_id from employees t where t.job_id = 'SH_CLERK' and lnnvl(t.department_id = 10);