with as 与临时表的区别_Oracle实验--从一个需求实现来看为什么要用with改写sql?

概述

前面已经介绍了with的一些用法，今天主要通过一个实验来简单测试下用不用with的一些不同。

---

1、环境准备

准备测试数据267万

CREATE TABLE t AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;insert into t select * from t;//.....

---

2、需求

需求：列出T表中用户包含对象数量大于平均用户对象数的记录。

这里大家可以想一下你们一般会怎么去实现？

---

3、方案1

set timing on;SELECT OWNER, COUNT(*) CN FROM T GROUP BY OWNER HAVING COUNT(*)>(SELECT AVG(COUNT(*)) FROM T GROUP BY OWNER);

查看执行计划：

---

4、方案2

当查询中多次用到某一部分时，可以用Oracle with语句创建一个公共临时表。因为子查询在内存临时表中，避免了重复解析，所以执行效率会提高不少。临时表在一次查询结束自动清除。

set timing on;WITH A AS(SELECT OWNER, COUNT(*) CN FROM T GROUP BY OWNER) SELECT A.* FROM A, (SELECT AVG(CN) ACN FROM A) B WHERE A.CN > B.ACN;

查看执行计划

可以看到先对T表做个全表扫描，然后产生一个公共临时表，后面就只用这个临时表做关联查询，避免了重复解析。

---

总结

总之对于同个表如果要避免重复读取这种情况可以考虑用with方式来改写。

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~