执行计划SQL写法差异改变之with子句
来源:互联网 发布:JS授权系统源码 编辑:程序博客网 时间:2024/06/05 10:46
/*
好处: 1. 性能更好,一份复制(类似SYS_TMP...),多份使用。
2. 结构清晰,预先定义。
3. 代码修改不必修改多处。
请注意观察语句1和语句2执行计划的差异,尤其是语句2的SYS_TEMP_0FD9D6605_3B91BA4这些奇怪的命名。其实这就表示是
复制在内存中的数据,一次复制,多次使用。
*/
drop table t_with;
CREATE TABLE T_WITH AS SELECT ROWNUM ID, A.* FROM DBA_SOURCE A WHERE ROWNUM < 100001;
SET autotrace traceonly
Set linesize 1000
--语句1
SELECT ID, NAME FROM T_WITH
WHERE ID IN
(SELECT MAX(ID) FROM T_WITH
UNION ALL
SELECT MIN(ID) FROM T_WITH
UNION ALL
SELECT TRUNC(AVG(ID)) FROM T_WITH);
执行计划
----------------------------------------------------------------------------------
Plan hash value: 647530712
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 | 1382 (1)| 00:00:17 |
|* 1 | HASH JOIN | | 3 | 129 | 1382 (1)| 00:00:17 |
| 2 | VIEW | VW_NSO_1 | 3 | 39 | 1035 (1)| 00:00:13 |
| 3 | HASH UNIQUE | | 3 | 39 | 1035 (67)| 00:00:13 |
| 4 | UNION-ALL | | | | | |
| 5 | SORT AGGREGATE | | 1 | 13 | | |
| 6 | TABLE ACCESS FULL| T_WITH | 91060 | 1156K| 345 (1)| 00:00:05 |
| 7 | SORT AGGREGATE | | 1 | 13 | | |
| 8 | TABLE ACCESS FULL| T_WITH | 91060 | 1156K| 345 (1)| 00:00:05 |
| 9 | SORT AGGREGATE | | 1 | 13 | | |
| 10 | TABLE ACCESS FULL| T_WITH | 91060 | 1156K| 345 (1)| 00:00:05 |
| 11 | TABLE ACCESS FULL | T_WITH | 91060 | 2667K| 345 (1)| 00:00:05 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"="MAX(ID)")
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4969 consistent gets
0 physical reads
0 redo size
558 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
--语句2
WITH AGG AS (SELECT MAX(ID) MAX, MIN(ID) MIN, TRUNC(AVG(ID)) AVG FROM T_WITH)
SELECT ID, NAME FROM T_WITH
WHERE ID IN
( SELECT MAX FROM AGG UNION ALL SELECT MIN FROM AGG UNION ALL SELECT AVG FROM AGG);
执行计划
--------------------------------------------------------------------------------------------------------
Plan hash value: 3705751949
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 | 697 (1)| 00:00:09 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6605_3B91BA4 | | | | |
| 3 | SORT AGGREGATE | | 1 | 13 | | |
| 4 | TABLE ACCESS FULL | T_WITH | 91060 | 1156K| 345 (1)| 00:00:05 |
|* 5 | HASH JOIN | | 3 | 129 | 352 (1)| 00:00:05 |
| 6 | VIEW | VW_NSO_1 | 3 | 39 | 6 (0)| 00:00:01 |
| 7 | HASH UNIQUE | | 3 | 39 | 6 (67)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
| 9 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_3B91BA4 | 1 | 13 | 2 (0)| 00:00:01 |
| 11 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_3B91BA4 | 1 | 13 | 2 (0)| 00:00:01 |
| 13 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_3B91BA4 | 1 | 13 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | T_WITH | 91060 | 2667K| 345 (1)| 00:00:05 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("ID"="MAX")
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
2 recursive calls
8 db block gets
2496 consistent gets
1 physical reads
600 redo size
558 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
好处: 1. 性能更好,一份复制(类似SYS_TMP...),多份使用。
2. 结构清晰,预先定义。
3. 代码修改不必修改多处。
请注意观察语句1和语句2执行计划的差异,尤其是语句2的SYS_TEMP_0FD9D6605_3B91BA4这些奇怪的命名。其实这就表示是
复制在内存中的数据,一次复制,多次使用。
*/
drop table t_with;
CREATE TABLE T_WITH AS SELECT ROWNUM ID, A.* FROM DBA_SOURCE A WHERE ROWNUM < 100001;
SET autotrace traceonly
Set linesize 1000
--语句1
SELECT ID, NAME FROM T_WITH
WHERE ID IN
(SELECT MAX(ID) FROM T_WITH
UNION ALL
SELECT MIN(ID) FROM T_WITH
UNION ALL
SELECT TRUNC(AVG(ID)) FROM T_WITH);
执行计划
----------------------------------------------------------------------------------
Plan hash value: 647530712
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 | 1382 (1)| 00:00:17 |
|* 1 | HASH JOIN | | 3 | 129 | 1382 (1)| 00:00:17 |
| 2 | VIEW | VW_NSO_1 | 3 | 39 | 1035 (1)| 00:00:13 |
| 3 | HASH UNIQUE | | 3 | 39 | 1035 (67)| 00:00:13 |
| 4 | UNION-ALL | | | | | |
| 5 | SORT AGGREGATE | | 1 | 13 | | |
| 6 | TABLE ACCESS FULL| T_WITH | 91060 | 1156K| 345 (1)| 00:00:05 |
| 7 | SORT AGGREGATE | | 1 | 13 | | |
| 8 | TABLE ACCESS FULL| T_WITH | 91060 | 1156K| 345 (1)| 00:00:05 |
| 9 | SORT AGGREGATE | | 1 | 13 | | |
| 10 | TABLE ACCESS FULL| T_WITH | 91060 | 1156K| 345 (1)| 00:00:05 |
| 11 | TABLE ACCESS FULL | T_WITH | 91060 | 2667K| 345 (1)| 00:00:05 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"="MAX(ID)")
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4969 consistent gets
0 physical reads
0 redo size
558 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
--语句2
WITH AGG AS (SELECT MAX(ID) MAX, MIN(ID) MIN, TRUNC(AVG(ID)) AVG FROM T_WITH)
SELECT ID, NAME FROM T_WITH
WHERE ID IN
( SELECT MAX FROM AGG UNION ALL SELECT MIN FROM AGG UNION ALL SELECT AVG FROM AGG);
执行计划
--------------------------------------------------------------------------------------------------------
Plan hash value: 3705751949
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 | 697 (1)| 00:00:09 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6605_3B91BA4 | | | | |
| 3 | SORT AGGREGATE | | 1 | 13 | | |
| 4 | TABLE ACCESS FULL | T_WITH | 91060 | 1156K| 345 (1)| 00:00:05 |
|* 5 | HASH JOIN | | 3 | 129 | 352 (1)| 00:00:05 |
| 6 | VIEW | VW_NSO_1 | 3 | 39 | 6 (0)| 00:00:01 |
| 7 | HASH UNIQUE | | 3 | 39 | 6 (67)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
| 9 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_3B91BA4 | 1 | 13 | 2 (0)| 00:00:01 |
| 11 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_3B91BA4 | 1 | 13 | 2 (0)| 00:00:01 |
| 13 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_3B91BA4 | 1 | 13 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | T_WITH | 91060 | 2667K| 345 (1)| 00:00:05 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("ID"="MAX")
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
2 recursive calls
8 db block gets
2496 consistent gets
1 physical reads
600 redo size
558 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
阅读全文
0 0
- 执行计划SQL写法差异改变之with子句
- 执行计划SQL写法差异改变之insert all
- SQL之执行计划
- 数据库迁移导致sql 执行计划改变
- sql子句执行顺序
- sql性能之执行计划
- oracle读懂执行计划之得到sql的执行计划
- SQL中的with ties子句
- 如何使用ordered提示改变SQL执行计划
- Scrips:打印AWR中执行计划的改变awr_plan_change.sql
- 使用hint改变SQL执行计划的方法
- sql子句的执行顺序
- SQL 之 HAVING 子句
- SQL之WHERE子句
- SQL性能分析之执行计划
- MySQL SQL优化之 执行计划 explain
- Oracle执行计划之SQL优化
- Hive 之 SQL执行计划深度解析
- 反转单向链表的几种方法
- 深入理解java异常处理机制(目前最好的讲解异常的博文)
- python参数的引用传递
- Gym 100112CCookie Selection ,不停插入输出中位数
- elasticsearch为什么那么快
- 执行计划SQL写法差异改变之with子句
- 详解ANN
- 二分索引和斐波那契索引Java实现
- 笔记
- MySQL 第四篇:SQL查询强化使用
- 类加载问题
- Java中,IO流(三):字符流的过滤器
- ActiveMQ简介
- 动态解析包含JSONObject的JSONArray