Where与Having
时间:2014-12-08 14:24 来源:linux.it.net.cn 作者:IT
最近做实验中使用到了group和having，想到where和having之间存在一些共性特点，都是对于结果集合的筛选处理。那么，在获取结果集合相同的情况下，两者的执行计划有什么差异呢？
试验一下：
首先，准备实验环境，还是选择sys下的dba_objects视图作为数据来源，构建数据表t。
//数据表
SQL> create table t as select * from dba_objects where 1=0;
Table created
//数据生成脚本
declare
i number;
begin
for i in 1..10 loop
insert /*+ append */ into t
select * from dba_objects order by i;
commit;
end loop;
end;
/
//约五十万数据量
SQL> select count(*) from t;
COUNT(*)
----------
513520
//收集统计信息
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
语句目的，是获取到SYS下属所有对象的个数。
语句1：使用where条件，指定SYS的owner之后，再进行group by操作。
SQL> select owner,count(*) from t where wner='SYS' group by owner;
已用时间: 00: 00: 00.07
//借用autotrace 获取到的执行计划
执行计划
----------------------------------------------------------
Plan hash value: 2913913920
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 228K| 1340K| 1570 (2)| 00:00:19 |
| 1 | SORT GROUP BY NOSORT| | 228K| 1340K| 1570 (2)| 00:00:19 |
|* 2 | TABLE ACCESS FULL | T | 228K| 1340K| 1570 (2)| 00:00:19 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
统计信息
----------------------------------------------------------
42 recursive calls
0 db block gets
7074 consistent gets
0 physical reads
764 redo size
469 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
说明：Oracle执行思路比较清晰，首先进行全表扫描，采用filter操作，将owner=’SYS’的条件加入进去，获取到筛选后的结果集合，之后再进行group分组操作。Filter操作在最内层进行。
语句2：采用分组方法，再having中，将owner=’sys’作为一个条件进行处理。
SQL> select owner,count(*) from t group by owner having wner='SYS';
已用时间: 00: 00: 00.17
执行计划
----------------------------------------------------------
Plan hash value: 1381620754
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1642 (6)| 00:00:20 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 6 | 1642 (6)| 00:00:20 |
| 3 | TABLE ACCESS FULL| T | 512K| 3001K| 1566 (2)| 00:00:19 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7070 consistent gets
0 physical reads
764 redo size
469 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
说明：Oracle在处理这个语句时，是先对所有数据进行hash分组操作，作为一个临时数据集合。再将having条件加入进去，作为filter的一部分进行处理。
两种方法对比，应该说第一种方法在时间消耗上存在一定程度的优势，同时空间消耗也相对较少。在海量数据的时候，是可以作为优先的选择。
进一步将，having的最大使用之处，也不是对分组变量条件的设置，而是对分组后聚合操作函数的条件筛选。如果要看对象数据超过1000的owner情况，就需要在having后面加入count(*)>1000的条件了。
最后，可能很多读者都会疑惑我写作的目的。其实，这个实验只是想说明一件事，就是Oracle中SQL书写问题。SQL语言是一种比较特殊的程序设计语言，相对于常见的Java/C++等，它本质上是一种描述类语言。对于数据内容，我们在SQL中只是描述出需要数据集合的特性，而不需要制定获取数据的手段方法。
对SQL，所有的DBMS都要进行语法验证和解析工作，分析称不同的实际操作原语，执行语句后获取到结果。Oracle也是如此，对执行计划的研究探讨，本质上就是对Oracle是如何获取数据进行的探讨。
做同一件事情，描述同一个事物，我们通常有不同的方式手段。同一个数据操作需要，我们可以借助不同的SQL去实现，Oracle也会依据不同的标准生成不同的执行计划。当然，现代DBMS对于SQL语句在解析过程中，是有优化处理，将一些低效率不合理的语句加以替换（通常在exists和in会出现），但是这种自动优化的行为是很有限的。决定一个SQL执行效率的因素很多，但是我们一般可控的两大因素：数据对象的特性（数据表、索引等的设置）以及SQL的书写。数据对象的特性可以说是其中静态的因素，而SQL语句的书写是其中动态的因素。
“Nerver Treate Your Database as A Blackbox”，是笔者在《Oracle专家编程》中印象深刻的一句话。书写高效的SQL是一件不容易的事情，需要了解很多内部的知识和原理。但首先，我们需要意识到我们的SQL是会造成这些影响，之后再想办法加以优化。
最后，还是想说下那位高人的名言“Nerver Treate Your Database as A Blackbox”。
(责任编辑：IT)
| 最近做实验中使用到了group和having，想到where和having之间存在一些共性特点，都是对于结果集合的筛选处理。那么，在获取结果集合相同的情况下，两者的执行计划有什么差异呢？ 试验一下： 首先，准备实验环境，还是选择sys下的dba_objects视图作为数据来源，构建数据表t。 //数据表 SQL> create table t as select * from dba_objects where 1=0; Table created //数据生成脚本 declare i number; begin for i in 1..10 loop insert /*+ append */ into t select * from dba_objects order by i; commit; end loop; end; / //约五十万数据量 SQL> select count(*) from t; COUNT(*) ---------- 513520 //收集统计信息 SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true); PL/SQL procedure successfully completed 语句目的，是获取到SYS下属所有对象的个数。 语句1：使用where条件，指定SYS的owner之后，再进行group by操作。 SQL> select owner,count(*) from t where wner='SYS' group by owner; 已用时间: 00: 00: 00.07 //借用autotrace 获取到的执行计划 执行计划 ---------------------------------------------------------- Plan hash value: 2913913920 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 228K| 1340K| 1570 (2)| 00:00:19 | | 1 | SORT GROUP BY NOSORT| | 228K| 1340K| 1570 (2)| 00:00:19 | |* 2 | TABLE ACCESS FULL | T | 228K| 1340K| 1570 (2)| 00:00:19 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OWNER"='SYS') 统计信息 ---------------------------------------------------------- 42 recursive calls 0 db block gets 7074 consistent gets 0 physical reads 764 redo size 469 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 说明：Oracle执行思路比较清晰，首先进行全表扫描，采用filter操作，将owner=’SYS’的条件加入进去，获取到筛选后的结果集合，之后再进行group分组操作。Filter操作在最内层进行。 语句2：采用分组方法，再having中，将owner=’sys’作为一个条件进行处理。 SQL> select owner,count(*) from t group by owner having wner='SYS'; 已用时间: 00: 00: 00.17 执行计划 ---------------------------------------------------------- Plan hash value: 1381620754 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 1642 (6)| 00:00:20 | |* 1 | FILTER | | | | | | | 2 | HASH GROUP BY | | 1 | 6 | 1642 (6)| 00:00:20 | | 3 | TABLE ACCESS FULL| T | 512K| 3001K| 1566 (2)| 00:00:19 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OWNER"='SYS') 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 7070 consistent gets 0 physical reads 764 redo size 469 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 说明：Oracle在处理这个语句时，是先对所有数据进行hash分组操作，作为一个临时数据集合。再将having条件加入进去，作为filter的一部分进行处理。 两种方法对比，应该说第一种方法在时间消耗上存在一定程度的优势，同时空间消耗也相对较少。在海量数据的时候，是可以作为优先的选择。 进一步将，having的最大使用之处，也不是对分组变量条件的设置，而是对分组后聚合操作函数的条件筛选。如果要看对象数据超过1000的owner情况，就需要在having后面加入count(*)>1000的条件了。 最后，可能很多读者都会疑惑我写作的目的。其实，这个实验只是想说明一件事，就是Oracle中SQL书写问题。SQL语言是一种比较特殊的程序设计语言，相对于常见的Java/C++等，它本质上是一种描述类语言。对于数据内容，我们在SQL中只是描述出需要数据集合的特性，而不需要制定获取数据的手段方法。 对SQL，所有的DBMS都要进行语法验证和解析工作，分析称不同的实际操作原语，执行语句后获取到结果。Oracle也是如此，对执行计划的研究探讨，本质上就是对Oracle是如何获取数据进行的探讨。 做同一件事情，描述同一个事物，我们通常有不同的方式手段。同一个数据操作需要，我们可以借助不同的SQL去实现，Oracle也会依据不同的标准生成不同的执行计划。当然，现代DBMS对于SQL语句在解析过程中，是有优化处理，将一些低效率不合理的语句加以替换（通常在exists和in会出现），但是这种自动优化的行为是很有限的。决定一个SQL执行效率的因素很多，但是我们一般可控的两大因素：数据对象的特性（数据表、索引等的设置）以及SQL的书写。数据对象的特性可以说是其中静态的因素，而SQL语句的书写是其中动态的因素。 “Nerver Treate Your Database as A Blackbox”，是笔者在《Oracle专家编程》中印象深刻的一句话。书写高效的SQL是一件不容易的事情，需要了解很多内部的知识和原理。但首先，我们需要意识到我们的SQL是会造成这些影响，之后再想办法加以优化。 最后，还是想说下那位高人的名言“Nerver Treate Your Database as A Blackbox”。 (责任编辑：IT) |