with as 搭配 hint materialize的使用

今天看《SQL核心优化思想》第九章 生产中实际的优化案例时候

发现几处子查询优化时候，用到了搭配 hint materialize 的with as用法。

实际的案例是在一个IN的子查询的SQL中，子查询中返回数据量较小，但是主表中数据量很大

之前在表连接中学习到，两表进行NL嵌套循环，应让小表作为驱动表，大表作为被驱动表，同时在大表的连接列建立索引。

那针对此案例，应该将子查询作为NL的驱动表，这时应该利用with as，将子查询写入。

但是如果如果主表子表连接列没有索引，with as又没有hint materialize，优化器就不会自动将with as作为嵌套循环的驱动表。

 

那么此时就要引入/*+ materialize */的用法了。那么他和普通的with as又有什么区别呢？

首先大家应该都是到with as的用法吧，可以将复杂的SQL简化，优化整体的可读性。例如同时多段SQL中的相同部分，用with as代替，就变得很规整，可读性很高，也可粗略的理解为一个临时表。

我们经常利用GTT(GLOBAL TEMPORARY)全局临时表、MATERIALIZE VIEW物化视图来提高查询的效率，

那么此时添加HINT /*+ materialize */ 就可以粗略的看做成GTT，但是需要注意的是，with as与/*+ materialize */联合使用时，应注意此如果结果记录集比较庞大，还是推荐使用GTT，因为那里面可以指定索引等.

 

所以当大家实际使用with as的时候，如果发现并没有起到临时表的作用，可以尝试添加此hint。