数据库查询优化器的多种转换技术解析

# 数据库查询优化器的多种转换技术解析 在数据库查询优化领域，存在着多种优化器转换技术，这些技术能够显著提升查询性能。下面将详细介绍几种常见的转换技术。 ## 1. 连接谓词下推（Join Predicate Pushdown，JPPD） ### 1.1 适用场景 当视图合并不可行或不合适时，JPPD 是一种备用的转换选项。例如，在查询涉及外连接时，简单和复杂视图合并都可能无法进行，此时 JPPD 就可以发挥作用。 ### 1.2 示例代码与分析 以下是一个 JPPD 的示例查询： ```sql WITH agg_q AS ( SELECT /*+ push_pred */ /* no_push_pred */ s.cust_id ,prod_id ,p.prod_name ,SUM (s.amount_sold) total_amt_sold FROM sh.sales s JOIN sh.products p USING (prod_id) GROUP BY s.cust_id, prod_id) SELECT cust_id ,c.cust_first_name ,c.cust_last_name ,c.cust_email ,agg_q.total_amt_sold FROM agg_q RIGHT JOIN sh.customers c USING (cust_id) WHERE cust_first_name = 'Abner' AND cust_last_name = 'Everett'; ``` 未转换的执行计划如下： | Id | Operation | Name | Cost (%CPU)| | --- | --- | --- | --- | | 0 | SELECT STATEMENT | | 5519 (1)| | *1 | HASH JOIN OUTER | | 5519 (1)| | *2 | TABLE ACCESS FULL | CUSTOMERS | 423 (1)| | 3 | VIEW | | 5095 (1)| | 4 | HASH GROUP BY | | 5095 (1)| | *5 | HASH JOIN | | 3021 (1)| | 6 | INDEX FULL SCAN | PRODUCTS_PK | 1 (0)| | 7 | VIEW | VW_GBC_6 | 3019 (1)| | 8 | HASH GROUP BY | | 3019 (1)| | 9 | PARTITION RANGE ALL| | 517 (2)| | 10 | TABLE ACCESS FULL | SALES | 517 (2)| 转换后的查询如下： ```sql SELECT c.cust_id ,agg_q.prod_id ,agg_q.prod_name ,c.cust_first_name ,c.cust_last_name ,c.cust_email ,agg_q.total_amt_sold FROM sh.customers c OUTER APPLY ( SELECT prod_id, p.prod_name, SUM (s.amount_sold) total_amt_sold FROM sh.sales s JOIN sh.products p USING (prod_id) WHERE s.cust_id = c.cust_id GROUP BY prod_id, p.prod_name) agg_q WHERE cust_first_name = 'Abner' AND cust_last_name = 'Everett'; ``` 转换后的执行计划如下： | Id | Operation | Name | Cost (%CPU)| | --- | --- | --- | --- | | 0 | SELECT STATEMENT | | 480 (0)| | 1 | NESTED LOOPS OUTER | | 480 (0)| | *2 | TABLE ACCESS FULL | CUSTOMERS | 423 (1)| | 3 | VIEW PUSHED PREDICATE | | 58 (0)| | 4 | SORT GROUP BY | | 58 (0)| | *5 | HASH JOIN | | 58 (0)| | 6 | VIEW | VW_GBC_6 | 55 (0)| | 7 | SORT GROUP BY | | 55 (0)| | 8 | PARTITION RANGE ALL | | 55 (0)| | 9 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES | 55 (0)| | 10 | BITMAP CONVERSION TO ROWIDS | | | | *11 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | | | 12 | TABLE ACCESS FULL | PRODUCTS | 3 (0)| 从执行计划可以看出，转换后的成本明显降低，从 5519 降低到了 480。这是因为 JPPD 可以提前过滤数据，减少后续处理的数据量。 ### 1.3 支持的视图类型 JPPD 只支持以下类型的视图： - UNION ALL/UNION 视图 - 外连接视图 - 反连接视图 - 半连接视图 - DISTINCT 视图 - GROUP - BY 视图 ### 1.4 成本与性能权衡 JPPD 是一种基于成本的转换技术，虽然它可以提前过滤数据带来好处，但需要多次执行子查询。如果多次执行子查询的成本过高，CBO（成本优化器）可能会选择不应用该转换，而使用哈希连接，使子查询只执行一次。 ## 2. 子查询解相关（Subquery Decorrelation） ### 2.1 与 JPPD 的关系 JPPD 和子查询解相关是相互逆的转换，就像复杂视图合并和分组放置是相互逆的转换一样。 ### 2.2 示例代码与分析 以下是一个子查询解相关的示例查询： ```sql SELECT o.order_id ,o.order_date ,o.order_mode ,o.customer_id ,o.order_status ,o.order_total ,o.sales_rep_id ,o.promotion_id ,agg_q.max_quantity FROM oe.orders o CROSS APPLY (SELECT /*+ decorrelate */ /* no_decorrelate */ MAX (oi.quantity) max_quantity FROM oe.order_items oi WHERE oi.order_id = o.order_id) agg_q WHERE o.order_id IN (2458, 2397); ``` 未转换的执行计划如下： | Id | Operation | Name | Cost (%CPU)| | --- | --- | --- | --- | | 0 | SELECT STATEMENT | | 8 (0)| | 1 | NESTED LOOPS | | 8 (0)| | 2 | INLIST ITERATOR | | | | 3 | TABLE ACCESS BY INDEX ROWID | ORDERS | 2 (0)| | *4 | INDEX UNIQUE SCAN | ORDER_PK | 1 (0)| | 5 | VIEW | VW_LAT_535DE542 | 3 (0)| | 6 | SORT AGGREGATE | | | | 7 | TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_ITEMS | 3 (0)| | *8 | INDEX RANGE SCAN | ITEM_ORDER_IX | 1 (0)| 转换后的查询如下： ```sql SELECT o.order_id order_id ,o.order_date order_date ,o.order_mode order_mode ,o.customer_id customer_id ,o.order_status order_status ,o.order_total order_total ,o.sales_rep_id sales_rep_id ,o.promotion_id promotion_id ,MAX (oi.quantity) max_quantity FROM oe.orders o LEFT JOIN oe.order_items oi ON oi.order_id = o.order_id AND (oi.order_id = 2397 OR oi.order_id = 2458) WHERE (o.order_id = 2397 OR o.order_id = 2458) GROUP BY o.order_id ,o.ROWID ,o.promotion_id ,o.sales_rep_id ,o.order_total ,o.order_status ,o.customer_id ,o.order_mode ,o.order_date ,o.order_id; ``` 转