物化（Materialization）

物化应该是指将子查询的结果存储到一个临时表中，这样后续的查询可以直接使用这个临时表，而不需要每次都重新执行子查询。这样可以减少重复计算，尤其是在子查询结果集较大的情况下，可能提高性能。

适用场景：

1. 非相关子查询：子查询不依赖外层查询的值，可以独立执行并缓存结果。
2. 子查询结果集较大：如果子查询返回的结果集较大，物化可以避免重复计算，提高效率。
3. 需要多次访问子查询结果：例如子查询被用于JOIN或WHERE条件多次时，物化临时表可以复用。
4. 子查询复杂度高：若子查询包含聚合、排序或复杂过滤（如GROUP BY, DISTINCT），物化可减少计算开销。

示例

SELECT * FROM customers 
WHERE id IN (SELECT customer_id FROM orders WHERE amount > 100);

若子查询SELECT customer_id FROM orders的结果集大且无索引，优化器可能物化结果到临时表，再与customers表JOIN。

优化提示：

使用SUBQUERY提示强制物化：

SELECT /*+ SUBQUERY(MATERIALIZATION) */ * FROM customers ...;

半连接

半连接通常用于存在性检查，比如使用IN或者EXISTS子句时。半连接和普通连接的不同之处在于，半连接只需要返回外层表中存在匹配的记录，而不会返回多条匹配记录导致重复。

适用场景：

1. 存在性检查：使用IN或EXISTS时，只需判断外层记录是否存在匹配，无需返回所有匹配项。
2. 相关子查询：子查询依赖外层查询的值，需逐行判断。
3. 索引可用性高：若子查询字段有索引（如customer_id上的索引），半连接可通过索引快速定位匹配项。
4. 结果集较小：子查询结果集较小时，半连接避免物化开销，直接通过索引高效匹配。

SELECT * FROM departments d 
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

若employees.dept_id有索引，优化器可能选择半连接策略，仅检查是否存在匹配记录。

优化提示：

使用SEMIJOIN提示强制半连接策略：

SELECT /*+ SEMIJOIN(DUPSWEEDOUT) */ * FROM departments ...;

关键选择因素

调试与优化建议

1. 使用EXPLAIN分析：通过执行计划查看优化器选择的策略（如MATERIALIZED或SEMIJOIN）。
2. 强制策略：通过优化器提示（如/*+ SEMIJOIN(...) */）干预选择。
3. 索引优化：为子查询字段添加索引，提升半连接效率。
4. 权衡资源：物化可能占用更多内存/磁盘，需根据系统资源调整。

总结：非相关大结果集优先物化，相关或索引友好场景选半连接，结合EXPLAIN和实际性能测试进行调优。