Oracle 19C OR 转换 UNION ALL

原创 Asher.HU 2021-02-04

2057

在OR 转换中，优化器将包含顶级析取关系的查询块转换为UNION ALL包含两个或多个分支的查询形式。

优化程序通过将析取运算拆分成多个组件，然后将每个组件与UNION ALL查询的分支相关联来实现此目标。优化器可以OR出于各种原因选择扩展。例如，它可以启用更有效的访问路径或其他避免使用笛卡尔积的联接方法。与往常一样，仅当转换后的语句的成本低于原始语句的成本时，优化器才会执行转换。

在以前的版本中，优化器使用CONCATENATION运算符执行OR转换。从Oracle Database 12c第2版（12.2）开始，优化UNION-ALL器改为使用运算符。该框架提供了以下增强功能：

实现各种转换之间的交互
避免共享查询结构
可以探索各种搜索策略
提供成本注释的重用
支持标准的SQL语法

示例5-1转换后的查询：UNION ALL条件

为了准备本示例，请以管理员身份登录数据库，执行以下语句以向hr.departments.department_name列添加唯一约束，然后向hr.employees表添加100,000行：

ALTER TABLE hr.departments ADD CONSTRAINT department_name_uk UNIQUE (department_name);
DELETE FROM hr.employees WHERE employee_id > 999;
DECLARE
v_counter NUMBER(7) := 1000;
BEGIN
 FOR i IN 1..100000 LOOP    
 INSERT INTO hr.employees
    VALUES (v_counter,null,'Doe','Doe' || v_counter || '@example.com',null,'07-JUN-02','AC_ACCOUNT',null,null,null,50);
 v_counter := v_counter + 1;
 END LOOP;
END;
/
COMMIT; 
EXEC DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'hr', tabname => 'employees');

然后，您以用户身份进行连接hr，并执行以下查询，该查询将employees和departments表连接在一起：

SELECT *
FROM   employees e, departments d
WHERE  (e.email='SSTILES' OR d.department_name='Treasury')
AND    e.department_id = d.department_id;

如果不进行OR 转换，则优化器将被e.email='SSTILES' OR d.department_name='Treasury'视为一个单元。因此，优化器不能在任一使用索引e.email或d.department_name列，等等执行的全表扫描employees和departments。

通过OR转换，优化器将析取谓词分为两个独立的谓词，如以下示例所示：

SELECT *
FROM   employees e, departments d
WHERE  e.email = 'SSTILES'
AND    e.department_id = d.department_id
UNION ALL
SELECT *
FROM   employees e, departments d
WHERE  d.department_name = 'Treasury'
AND    e.department_id = d.department_id;

此转换使e.emailand d.department_name列可以用作索引键。由于数据库使用两个唯一索引而不是两个全表扫描来过滤数据，因此性能得以提高，如以下执行计划所示：

Plan hash value: 2512933241

-----------------------------------------------------------------------------------------------
| Id| Operation                              | Name             |Rows|Bytes|Cost(%CPU)|Time   |
----------------------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT                       |                  |    |     |122 (100)|        |
| 1 |  VIEW                                  |VW_ORE_19FF4E3E   |9102|1679K|122  (5) |00:00:01|
| 2 |   UNION-ALL                            |                  |    |     |         |        |
| 3 |    NESTED LOOPS                        |                  |  1 |  78 |  4  (0) |00:00:01|
| 4 |     TABLE ACCESS BY INDEX ROWID        | EMPLOYEES        |  1 |  57 |  3  (0) |00:00:01|
|*5 |      INDEX UNIQUE SCAN                 | EMP_EMAIL_UK     |  1 |     |  2  (0) |00:00:01|
| 6 |     TABLE ACCESS BY INDEX ROWID        | DEPARTMENTS      |  1 |  21 |  1  (0) |00:00:01|
|*7 |      INDEX UNIQUE SCAN                 | DEPT_ID_PK       |  1 |     |  0  (0) |        |
| 8 |    NESTED LOOPS                        |                  |9101| 693K|118  (5) |00:00:01|
| 9 |     TABLE ACCESS BY INDEX ROWID        | DEPARTMENTS      |  1 |  21 |  1  (0) |00:00:01|
|*10|      INDEX UNIQUE SCAN                 |DEPARTMENT_NAME_UK|  1 |     |  0  (0) |        |
|*11|     TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES        |9101| 506K|117  (5) |00:00:01|
|*12|      INDEX RANGE SCAN                  |EMP_DEPARTMENT_IX |9101|     | 35  (6) |00:00:01|
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("E"."EMAIL"='SSTILES')
   7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
  10 - access("D"."DEPARTMENT_NAME"='Treasury')
  11 - filter(LNNVL("E"."EMAIL"='SSTILES'))
  12 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

35 rows selected.

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 19C OR 转换 UNION ALL

评论