WITH AS 测试
来源：互联网 发布：java集合类迭代器 编辑：程序博客网 时间：2024/04/30 07:22
WITH AS 子句定义SQL语句级的临时表、该临时表仅对本次执行的SQL有效。
SQL> select count(*) from t3 where table_name='test' and tablespace_name='tbs1'; COUNT(*)---------- 299999SQL> select count(*) from t3 where table_name='test' and tablespace_name='tbs2'; COUNT(*)---------- 300000SQL> select count(*) from t3 where table_name='test' and tablespace_name='tbs3'; COUNT(*)---------- 400000SQL> select count(*) from t3 ; COUNT(*)---------- 3233792SQL> with tmp as 2 (select blocks,num_rows,tablespace_name from t3 where table_name='test') 3 select blocks,num_rows from tmp where tablespace_name='tbs3' 4 union all 5 select blocks,num_rows from tmp where tablespace_name='tbs2' 6 union all 7 select blocks,num_rows from tmp where tablespace_name='tbs1';已选择999999行。已用时间: 00: 00: 24.51执行计划----------------------------------------------------------Plan hash value: 2052080865-------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 129 | 6 (67)| 00:00:01 || 1 | TEMP TABLE TRANSFORMATION | | | | | || 2 | LOAD AS SELECT | | | | | ||* 3 | TABLE ACCESS FULL | T3 | 1 | 32 | 43 (0)| 00:00:01 || 4 | UNION-ALL | | | | | ||* 5 | VIEW | | 1 | 43 | 2 (0)| 00:00:01 || 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6604_CF1A0 | 1 | 13 | 2 (0)| 00:00:01 ||* 7 | VIEW | | 1 | 43 | 2 (0)| 00:00:01 || 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6604_CF1A0 | 1 | 13 | 2 (0)| 00:00:01 ||* 9 | VIEW | | 1 | 43 | 2 (0)| 00:00:01 || 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6604_CF1A0 | 1 | 13 | 2 (0)| 00:00:01 |-------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("TABLE_NAME"='test') 5 - filter("TABLESPACE_NAME"='tbs3') 7 - filter("TABLESPACE_NAME"='tbs2') 9 - filter("TABLESPACE_NAME"='tbs1')统计信息---------------------------------------------------------- 912 recursive calls 4405 db block gets 172745 consistent gets 86161 physical reads 1476 redo size 15545728 bytes sent via SQL*Net to client 733711 bytes received via SQL*Net from client 66668 SQL*Net roundtrips to/from client 23 sorts (memory) 0 sorts (disk) 999999 rows processedSQL> select blocks,num_rows from t3 where table_name='test' and tablespace_name='tbs3' 2 union all 3 select blocks,num_rows from t3 where table_name='test' and tablespace_name='tbs2' 4 union all 5 select blocks,num_rows from t3 where table_name='test' and tablespace_name='tbs1';已选择999999行。已用时间: 00: 00: 42.96执行计划----------------------------------------------------------Plan hash value: 2294035736---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 96 | 130 (67)| 00:00:02 || 1 | UNION-ALL | | | | | ||* 2 | TABLE ACCESS FULL| T3 | 1 | 32 | 43 (0)| 00:00:01 ||* 3 | TABLE ACCESS FULL| T3 | 1 | 32 | 43 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL| T3 | 1 | 32 | 43 (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("TABLESPACE_NAME"='tbs3' AND "TABLE_NAME"='test') 3 - filter("TABLESPACE_NAME"='tbs2' AND "TABLE_NAME"='test') 4 - filter("TABLESPACE_NAME"='tbs1' AND "TABLE_NAME"='test')统计信息---------------------------------------------------------- 1 recursive calls 0 db block gets 343125 consistent gets 245980 physical reads 0 redo size 15545728 bytes sent via SQL*Net to client 733711 bytes received via SQL*Net from client 66668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999999 rows processedSQL> with tmp as 2 (select /*+ materialize */ blocks,num_rows,tablespace_name from t3 where table_name='test') 3 select blocks,num_rows from tmp where tablespace_name='tbs3' 4 union all 5 select blocks,num_rows from tmp where tablespace_name='tbs2' 6 union all 7 select blocks,num_rows from tmp where tablespace_name='tbs1';已选择999999行。已用时间: 00: 00: 24.60执行计划----------------------------------------------------------Plan hash value: 94255643-------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 129 | 6 (67)| 00:00:01 || 1 | TEMP TABLE TRANSFORMATION | | | | | || 2 | LOAD AS SELECT | | | | | ||* 3 | TABLE ACCESS FULL | T3 | 1 | 32 | 43 (0)| 00:00:01 || 4 | UNION-ALL | | | | | ||* 5 | VIEW | | 1 | 43 | 2 (0)| 00:00:01 || 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6606_CF1A0 | 1 | 13 | 2 (0)| 00:00:01 ||* 7 | VIEW | | 1 | 43 | 2 (0)| 00:00:01 || 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6606_CF1A0 | 1 | 13 | 2 (0)| 00:00:01 ||* 9 | VIEW | | 1 | 43 | 2 (0)| 00:00:01 || 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6606_CF1A0 | 1 | 13 | 2 (0)| 00:00:01 |-------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("TABLE_NAME"='test') 5 - filter("TABLESPACE_NAME"='tbs3') 7 - filter("TABLESPACE_NAME"='tbs2') 9 - filter("TABLESPACE_NAME"='tbs1')统计信息---------------------------------------------------------- 153 recursive calls 4405 db block gets 172346 consistent gets 86157 physical reads 1532 redo size 15545728 bytes sent via SQL*Net to client 733711 bytes received via SQL*Net from client 66668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999999 rows processed--SYS_TEMP_0FD9D6606_CF1A0为系统产生的临时表。--一般写法SQL> select blocks,num_rows from t3 where table_name='test' 2 and (tablespace_name='tbs3' or tablespace_name='tbs2' or tablespace_name='tbs1');已选择999999行。已用时间: 00: 00: 22.94执行计划----------------------------------------------------------Plan hash value: 4161002650--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 32 | 43 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T3 | 1 | 32 | 43 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("TABLE_NAME"='test' AND ("TABLESPACE_NAME"='tbs1' OR "TABLESPACE_NAME"='tbs2' OR "TABLESPACE_NAME"='tbs3'))统计信息---------------------------------------------------------- 1 recursive calls 0 db block gets 157529 consistent gets 81835 physical reads 0 redo size 14157443 bytes sent via SQL*Net to client 733711 bytes received via SQL*Net from client 66668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 999999 rows processed某些时候，with as 的性能要比union all好些，物理读和逻辑读更少。一般写法因为对表只扫描一次，物理读和逻辑读最少。在一些情况下根据业务使用with as 改写union all，也许可能提升性能。
- WITH AS 测试
- with as
- with as
- with as
- WITH AS
- with...as...
- WITH AS
- with...as
- WITH AS
- with as 用法
- ORACLE WITH AS 用法
- ORACLE WITH AS 用法
- with as 用法
- oracle with as
- WITH AS的用法
- With as 的用法
- WITH AS的含义
- WITH AS 用法
- xen虚拟windows使用vnc桌面鼠标位置偏移现象的解决
- Linux TCP/IP 协议栈的关键数据结构Socket Buffer(sk_buff )
- 软件性能
- java多线程笔记
- 文件访问（库函数）
- WITH AS 测试
- The Xen Store
- I/O复用
- 基于verilog按键消抖设计
- xenstored为什么不能重启?
- 如何引用一个已经定义过的全局变量 与 全局变量可不可以定义在可被多个.C文件包含的头文件中
- FCKeditor自定义验证
- 反垄断沦为利益工具让人哭笑不得
- Qt中的QString和QByteArray
