活用UNION ALL
来源:互联网 发布:启辰网络 编辑:程序博客网 时间:2024/06/16 12:56
要学会活学活用。把所有的知识点结合起来。 这样才能即快又准的做优化。
例:
11:23:42 SQL> create table test as select * from dba_objects;Table created.Elapsed: 00:00:04.5011:24:05 SQL> create index test_idx on test(object_id);Index created.Elapsed: 00:00:01.3411:25:03 SQL> select max(object_id), min(object_id) from test;Elapsed: 00:00:00.06Execution Plan----------------------------------------------------------Plan hash value: 1950795681---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 300 (1)| 00:00:04 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | TABLE ACCESS FULL| TEST | 81195 | 1030K| 300 (1)| 00:00:04 |---------------------------------------------------------------------------Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 4 recursive calls 0 db block gets 1148 consistent gets 1071 physical reads 0 redo size 613 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
分析下:
可以看到这个TEST有是有OBJECT_ID索引的, 我们只需查到最大的一个和最小一个OBJECT_ID。 为何就走了全表呢, 这是因为我们在创建索引的时候,索引默认是按照升序排列的。 ORACLE 没有办法去一次性去取最大的一个值和最小的一个值。
下面我们来用UNION ALL来做改写。
11:25:35 SQL> select max(object_id) from test11:27:40 2 union all11:27:42 3 select min(object_id) from test;Elapsed: 00:00:00.03Execution Plan----------------------------------------------------------Plan hash value: 2153991076----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 26 | 4 (50)| 00:00:01 || 1 | UNION-ALL | | | | | || 2 | SORT AGGREGATE | | 1 | 13 | | || 3 | INDEX FULL SCAN (MIN/MAX)| TEST_IDX | 1 | 13 | 2 (0)| 00:00:01 || 4 | SORT AGGREGATE | | 1 | 13 | | || 5 | INDEX FULL SCAN (MIN/MAX)| TEST_IDX | 1 | 13 | 2 (0)| 00:00:01 |----------------------------------------------------------------------------------------Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 7 recursive calls 0 db block gets 152 consistent gets 0 physical reads 0 redo size 588 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed
注: 在拿到语句的时候别急着看执行计划, 要先去分析下语句, 有可能不需要你去分析执行计划,优化方案就出来了。
还有一种方法就是使用标量子查询的方式。
select (select max(object_id) from test) maxid, (select min(object_id) from test) minid from dual;SQL> select (select max(object_id) from test) maxid, (select min(object_id) from test) minid from dual;Execution Plan----------------------------------------------------------Plan hash value: 4179602045-------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 6 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | INDEX FULL SCAN (MIN/MAX)| IDX_ID | 1 | 13 | 2 (0)| 00:00:01 || 3 | SORT AGGREGATE | | 1 | 13 | | || 4 | INDEX FULL SCAN (MIN/MAX)| IDX_ID | 1 | 13 | 2 (0)| 00:00:01 || 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |-------------------------------------------------------------------------------------Note----- - dynamic statistics used: dynamic sampling (level=2)Statistics---------------------------------------------------------- 9 recursive calls 0 db block gets 164 consistent gets 2 physical reads 0 redo size 611 bytes sent via SQL*Net to client 544 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
- 活用UNION ALL
- union all
- Union all
- union all
- union all
- union all
- union all
- union all
- union,union all 等
- union all 和union
- UNION,UNION ALL,MINUS
- union union all
- UNION and UNION All
- union 和 union all
- UNION与UNION ALL
- SQL Union / Union All
- UNION 和 UNION ALL
- union和union all
- 使用游标实现Oracle中的行列转换
- 下一座营销金矿:移动营销
- 在MFC程序中调用COM
- Code
- 任务一、数据库迁移的总结
- 活用UNION ALL
- DOS下文件共享
- Oracle字符集之乱码问题
- 网站被K后快捷收复小技巧
- Eclipse CDT Ubuntu下重新导入工程后make: *** No rule to make target `all'. Stop.
- 【Linux下配置NAT服务器共享上网】
- 关于Session
- jquerymobile header导航和footer底部固定
- CentOS 6.0 系统 LAMP(Apache+MySQL+PHP)安装步骤