有需要用到临时表，就准备看下WITH as的效率怎么样，为了能让大家都可以执行，我就直接以user_objects表为例：
下面是with as 建临时表查询的方法：
WITH objtype AS
(SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'TABLE'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'INDEX'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'SEQUENCE'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'PROCEDURE'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'FUNCTION'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'MATERIALIZED VIEW'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'TRIGGER'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'DATABASE LINK'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'LOB'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'PACKAGE'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'PACKAGE BODY'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'TYPE'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'TYPE BODY'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'VIEW'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'SYNONYM'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'JAVA CLASS'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'JAVA SOURCE')
SELECT *
FROM objtype;复制代码下面是直接from（子查询）的代码：
SELECT * FROM (SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'TABLE'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'INDEX'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'SEQUENCE'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'PROCEDURE'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'FUNCTION'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'MATERIALIZED VIEW'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'TRIGGER'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'DATABASE LINK'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'LOB'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'PACKAGE'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'PACKAGE BODY'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'TYPE'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'TYPE BODY'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'VIEW'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'SYNONYM'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'JAVA CLASS'
UNION ALL
SELECT COUNT(*) cnt
FROM user_objects
WHERE object_type = 'JAVA SOURCE');复制代码
当然，这里只是为了看看withas 和 from（子查询）来进行效率对比，要实现此功能，其实非常的简单，下面这条语句就搞定了:
SELECTCOUNT(*)
FROM user_objects
WHERE object_type IN ('SEQUENCE','PROCEDURE','DATABASE LINK','LOB','PACKAGE','PACKAGE BODY','TYPE BODY','TRIGGER','MATERIALIZED VIEW','TABLE','INDEX','FUNCTION','VIEW','SYNONYM','JAVA CLASS','JAVA SOURCE','TYPE')
GROUP BY object_type;复制代码 下面是他们的执行效率：
with as 的执行效率如下：
with.png (15.95 KB, 下载次数: 18)
下载附件
oraclewithas临时表查询效率
2014-11-11 17:09 上传
如下为from(子查询)查询效率：
from.png (18.01 KB, 下载次数: 12)
下载附件
oraclefrom(子查询)查询效率
2014-11-11 17:09 上传
下面为in的查询效率：
in.png (14.38 KB, 下载次数: 11)
下载附件
oracle查询效率
2014-11-11 17:09 上传
关于执行计划这里就不说了
|
