其实二者没有什么可比性，但是对于不包含聚集函数的GROUP BY操作来说，和DISTINCT操作是等价的。不过虽然二者的结果是一样的，但是二者的执行计划并不相同。
在Oracle9i中：
SQL& SELECT * FROM V$VERSION;
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 -&Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL& CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已创建。
SQL& CREATE INDEX IND_T_CREATED ON T (CREATED);
索引已创建。
SQL& ALTER TABLE T MODIFY CREATED NOT NULL;
表已更改。
SQL& ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
会话已更改。
SQL& EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
PL/SQL&过程已成功完成。
SQL& SET AUTOT ON EXP
SQL& SELECT COUNT(*) FROM (SELECT DISTINCT CREATED FROM T);
----------
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=65 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=65 Card=4794)
3 2 SORT (UNIQUE) (Cost=65 Card=4794 Bytes=38352)
4 3 INDEX (FAST FULL SCAN) OF 'IND_T_CREATED' (NON-UNIQUE) (Cost=4 Card=41802 Bytes=334416)
SQL& SELECT COUNT(*) FROM (SELECT CREATED FROM T GROUP BY CREATED);
----------
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=65 Card=1 Bytes=2)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=65 Card=4794 Bytes=9588)
3 2 SORT (GROUP BY) (Cost=65 Card=4794 Bytes=38352)
4 3 INDEX (FAST FULL SCAN) OF 'IND_T_CREATED' (NON-UNIQUE) (Cost=4 Card=41802 Bytes=334416)
从执行计划上看，DISTINCT的操作是SORT (UNIQUE)，而GROUP BY是SORT
(GROUP BY)。DISTINCT操作只需要找出所有不同的值就可以了。而GROUP BY操作还要为其他聚集函数进行准备工作。从这一点上将，GROUP BY操作做的工作应该比DISTINCT所做的工作要多一些。
除了这一点，基本上看不到DISTINCT和GROUP BY（没有聚集函数的情况）有什么区别，而且从执行效率上也看不到明显的差异。
不过从10g开始，二者的差异开始体现出来了。
SQL& CONN YANGTK/YANGTK@YTK已连接。
SQL& SET AUTOT OFF
SQL& SET TIMING OFF
SQL& CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已创建。
SQL& CREATE INDEX IND_T_CREATED ON T (CREATED);
索引已创建。
SQL& ALTER TABLE T MODIFY CREATED NOT NULL;
表已更改。
SQL& ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
会话已更改。
SQL& EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
PL/SQL&过程已成功完成。
SQL& SET AUTOT ON
SQL& SET TIMING ON
建立好测试环境后，看一看标准分页函数中，两个操作的差异：
SQL& SELECT *&
4 SELECT ROWNUM RN, A.*&
7 SELECT CREATED&
9 GROUP BY CREATED
11 WHERE ROWNUM & 20
13 WHERE RN &= 10;
RN CREATED
---------- -------------------
已选择10行。
已用时间: 00: 00: 00.06
----------------------------------------------------------
Plan hash value:
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 418 | 1 (0)|
|* 1 | VIEW | | 19 | 418 | 1 (0)|
|* 2 | COUNT STOPKEY | | | | |
| 3 | VIEW | | 969 | 8721 | 1 (0)|
|* 4 | SORT GROUP BY STOPKEY| | 969 | 7752 | 1 (0)|
| 5 | INDEX FULL SCAN | IND_T_CREATED | 969 | 7752 | 1 (0)|
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(&RN&&=10)
2 - filter(ROWNUM&20)
4 - filter(ROWNUM&20)
----------------------------------------------------------
1 recursive calls
0 db block gets
67 consistent gets
0 physical reads
0 redo size
642 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
SQL& SELECT *&
4 SELECT ROWNUM RN, A.*&
7 SELECT DISTINCT CREATED&
10 WHERE ROWNUM & 20
12 WHERE RN &= 10;
RN CREATED
---------- -------------------
已选择10行。
已用时间: 00: 00: 00.03
----------------------------------------------------------
Plan hash value:
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 418 | 14 (36)|
|* 1 | VIEW | | 19 | 418 | 14 (36)|
|* 2 | COUNT STOPKEY | | | | |
| 3 | VIEW | | 987 | 8883 | 14 (36)|
|* 4 | SORT GROUP BY STOPKEY| | 987 | 7896 | 14 (36)|
| 5 | INDEX FAST FULL SCAN| IND_T_CREATED | 50333 | 393K| 10 (10)|
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(&RN&&=10)
2 - filter(ROWNUM&20)
4 - filter(ROWNUM&20)
----------------------------------------------------------
1 recursive calls
0 db block gets
73 consistent gets
0 physical reads
0 redo size
642 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
出乎意料的是，GROUP BY操作的COST更低，而且逻辑读也小，这似乎与二者的工作量成反比。仔细观察执行计划发现，问题的根源来自于GROUP
BY使用INDEX FULL SCAN，而DISTINCT使用了INDEX FAST FULL SCAN。也许有人会感到奇怪，索引的快速全扫描不是要比索引全扫描效率更高吗？对于读取所有数据的情况下，确实是索引快速全扫效率更高。但是由于这里采用了分页，只取前20条数据，而且Oracle的10g增加了GROUP
BY STOPKEY这种新的执行路径，因此在这里GROUP BY操作的效率更高。
观察执行计划中的处理行数可以发现，索引全扫描由于是按照索引的顺序扫描，因此利用了STOPKEY，仅仅处理了969条记录就停了下来。而对于DISTINCT操作的快速索引全速而言，显然没有使用STOPKEY，读取了所有的50333条记录。这就是GROUP
BY和DISTINCT的性能差异原因。
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：1037552次
积分：11387
积分：11387
排名：第1153名
原创：79篇
转载：830篇
评论：101条
(26)(5)(42)(46)(105)(81)(67)(37)(4)(1)(12)(12)(24)(28)(69)(32)(34)(20)(1)(7)(10)(13)(36)(1)(2)(12)(21)(6)(8)(16)(1)(11)(4)(3)(6)(4)(2)(2)(2)(3)(4)(3)(2)(1)(5)(4)(1)(6)(6)(4)(2)(6)(5)(7)(1)(7)(15)(5)(1)(16)(5)> 博客详情
首先看下postgresql 执行计划中的一些术语和关键字。
执行计划运算类型
是否有启动时间
无启动时间
Index Scan
无启动时间
Bitmap Index Scan
有启动时间
Bitmap Heap Scan
有启动时间
Subquery Scan
无启动时间
ctid = …条件
无启动时间
Function Scan
无启动时间
Nested Loop
无启动时间
Merge Join
有启动时间
有启动时间
排序，ORDER BY操作
有启动时间
有启动时间
函数扫描，和具体的表无关
无启动时间
DISTINCT，UNION操作
有启动时间
LIMIT，OFFSET操作
有启动时间
count, sum,avg, stddev聚集函数
有启动时间
GROUP BY分组操作
有启动时间
无启动时间
Materialize
有启动时间
INTERCECT，EXCEPT
例子，查询1：
explain analyze select r.*,a.username
from t_portal_resource r
left join t_uc_account a on r.userid=a.id where
r.id in (select resourceid
from t_portal_cate_res where categoryid in (1))
and ( r.title like '%低调%' or r.tags like '%net%' )
order by istop desc, r.id desc limit 10 offset 0
这里explain后加analyze来通过真实执行这个SQL来获得真实的执行计划和执行时间。
postgresql查询计划是按照成本计算的，也就是基于成本的查询计划(cost-based plan)，其中影响成本计算的参数包括(后面括号的值为其缺省值)：
cpu_index_tuple_cost (0.005)
cpu_operator_cost (0.0025)
cpu_tuple_cost (0.01)
random_page_cost (4.0)
seq_page_cost (1.0)
&&&&从第一行起，主要查看 cost。 例如上面&cost=11.61..11.61。cost=说明：第一个数字11.61表示启动cost，这是执行到返回第一行时需要的cost值。第二个数字11.61表示执行整个的cost
&&&&actual time=0.183..0.191 rows=3 loops=1。actual time=中的第一个数字表示返回第一行需要的时间(叫启动时间)，第二个数字表示执行这个整个花的时间。后面的rows=3是实际的行数。
一个查询的总代价包括读取数据的I/O代价和其他各种操作的代价之和。 I/O代价包括顺序读取数据或索引页（seq_scan_cost）和随机读取数据页（random_scan_cost）的代价，操作代价包括处理表元组（cpu_tuple_cost）、处理比较操作（cpu_operator_cost）和处理索引元组（cpu_index_tuple_cost）。
比如，如果在一个表上做全表顺序扫描，那么其代价公式为:
&&&&Cost = seq_scan_cost*relpages + cpu_tuple_cost*reltuples
如果是在一个表上做全表顺序扫描并执行过滤，则代价公式为:
&&&&Cost = seq_scan_cost*relpages + cpu_tuple_cost*reltuples + cpu_operator_cost*reltuples
对于预算要返回的行数量，其计算公式为:
&&&&rows = reltuples*估算频率
这里，估算频率通过sys_stats视图中统计的列值和出现频率计算得出
relpages磁盘页，reltuples是行数(与实际不一定相符，一般略小)
select relpages,reltuples from pg_class where relname = 't_portal_resource'; 可以查看对象的详细信息。pg_class中的relpages,reltuples数据不是实时更新的，一般在vacuum analyze和少部分DDL（如建立索引）后更新。
优化需要查看的系统表：& &&&&pg_stats& &&&&pg_statistic& &&&&pg_class& &&&&pg_stat是任何人都可以看的，而且可读性高，比较直观，pg_statistic只有superuser才能读，并且可读性差，普通人员建议看pg_stats，pg_stats是pg_statistic的视图。 这两个表也不是实时更新的，需要vacuum analyze时会更新& 所涉及的系统变量：& &&&&default_statistics_target& &&&&geqo_threshold& &&&&join_collapse_limit& &&&&from_collapse_limit
可以通过 explain 加参数查看更详细的信息，
&&&&ANALYZE ：执行命令并显示执行事件，默认false& &&&&VERBOSE ：对执行计划提供额外的信息，如查询字段信息等，默认false& &&&&COSTS ：显示执行计划的，默认true& &&&&BUFFERS ：默认false，前置条件是analyze& &&&&FORMAT ：默认格式是text&
explain (analyze,verbose,costs,buffers) select ...
更详细的资料可以
我将上面的sql 修改了一下。查询2：
explain analyze select r.*,a.username
from t_portal_resource r
left join t_uc_account a on r.userid=a.id left join (select resourceid
from t_portal_cate_res where categoryid in (1)) t on r.id=t.resourceid where
(r.title like '%低调%' or r.tags like '%net%') order by istop desc, r.id desc limit 10 offset 0 将第一个in 语句改为左连接，小表查询，减少了连接的次数，性能有了明显提高：
一般来说sql 中的连接 join 对应在查询计划里有下面几种
嵌套循环连接(Nested loop join)
带内表顺序扫描
带内表索引扫描
合并连接(Merge join)
哈希连接(Hash join)
可以看到，在查询2中，有nested loop left join 连接，但当我把 sql where 条件去掉后，sql 语句中的连接对应在查询计划中都变成了 hash连接，而且性能差很多，因此尽量在有join的多表查询中 包含where 条件，
explain analyze select r.*,a.username
from t_portal_resource r
left join t_uc_account a on r.userid=a.id left join (select resourceid
from t_portal_cate_res where categoryid in (1)) t on r.id=t.resourceid
order by istop desc, r.id desc limit 10 offset 0
那么什么时候会参数合并merge join 呢。many-to-many 的查询，例如t_portal_cate_res(resourceid,categoryid) 和 t_portal_cate_user(categoryid,userid)
explain analyze select rs.resourceid from t_portal_cate_res rs
left join t_portal_cate_user ca on rs.categoryid=ca.categoryid
但是当把查询4 加上where条件后 where ca.userid=1. 就会变成nest loop 查询。
所以在many-to-many 的表连接查询的时候尽量转换为小表 one-to-many 查询，加上where 条件等等方式优化sql。
人打赏支持
参与源创会
领取时间：
“”在线下联结了各位 OSCer，推广开源项目和理念，很荣幸有你的参与~
领取条件：参与过开源中国“源创会”的 OSCer 可以领取
码字总数 29813
嗯，谢谢提醒，我改下。 index-only scan 这块看了下，但没深入，不知道是不是当查询的 列，连接的列，where子句的列都在索引上，就会触发 index-only scan？不知道理解对不对
嗯，谢谢提醒，我改下。 index-only scan 这块看了下，但没深入，不知道是不是当查询的 列，连接的列，where子句的列都在索引上，就会触发 index-only scan？不知道理解对不对这个功能其他数据库一般都有，pg是9.2开始才支持的：当通过索引查询到符合条件的记录（编号）时如果支持index-only scan则此（字段的）的值可以直接从索引中获得，如果不支持该功能或者visibility map表中没更新那么就去访问堆表从中获得值。简单来说就是索引中保存了该索引字段的值
嗯，谢谢提醒，我改下。 index-only scan 这块看了下，但没深入，不知道是不是当查询的 列，连接的列，where子句的列都在索引上，就会触发 index-only scan？不知道理解对不对这个功能其他数据库一般都有，pg是9.2开始才支持的：当通过索引查询到符合条件的记录（编号）时如果支持index-only scan则此（字段的）的值可以直接从索引中获得，如果不支持该功能或者visibility map表中没更新那么就去访问堆表从中获得值。简单来说就是索引中保存了该索引字段的值哦，了解了。打开index-only scan时，目标列上建立了索引，就直接从索引中获取数据，不必须去原始表中查找
嗯，谢谢提醒，我改下。 index-only scan 这块看了下，但没深入，不知道是不是当查询的 列，连接的列，where子句的列都在索引上，就会触发 index-only scan？不知道理解对不对这个功能其他数据库一般都有，pg是9.2开始才支持的：当通过索引查询到符合条件的记录（编号）时如果支持index-only scan则此（字段的）的值可以直接从索引中获得，如果不支持该功能或者visibility map表中没更新那么就去访问堆表从中获得值。简单来说就是索引中保存了该索引字段的值哦，了解了。打开index-only scan时，目标列上建立了索引，就直接从索引中获取数据，不必须去原始表中查找是的。不过索引上的数据是否可用由底层的visibility map来决定，这个表通过vacuum来维护——如果某条记录刚被update过那么index-only scan是不会从索引获取值的
支付宝支付
微信扫码支付
打赏金额: ￥
已支付成功
打赏金额: ￥[数据库]其他典型的执行计划
你的位置：
[数据库]其他典型的执行计划
&一、AND-EQUAL（INDEX MERGE）谓词中多个列等值条件，并且这些列上都有单键值的索引，oracle会合并扫描单个索引的rowid集合。SQL_ID 3zmhhz4cbg12f, child number 0-------------------------------------select /*+and_equal(a index_emp_DEPTNO IND_EMP_JOB)*/ * from scott.emp a where a.deptno=20 and a.job='SALESMAN' Plan hash value:
------------------------------------------------------------------------------------------------| Id | Operation
| Rows | Bytes | Cost (%CPU)| Time
|------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
||* 1 | TABLE ACCESS BY INDEX ROWID| EMP
(0)| 00:00:01 ||
INDEX RANGE SCAN
| IND_EMP_JOB
(0)| 00:00:01 ||* 4 |
INDEX RANGE SCAN
| INDEX_EMP_DEPTNO |
(0)| 00:00:01 |------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id):---------------------------------------------------
1 - filter(("A"."JOB"='SALESMAN' AND "A"."DEPTNO"=20))
3 - access("A"."JOB"='SALESMAN')
4 - access("A"."DEPTNO"=20) 通过先访问IND_EMP_JOB、INDEX_EMP_DEPTNO这两个索引后，在过滤rowid相同的在filter(("A"."JOB"='SALESMAN' AND "A"."DEPTNO"=20)) ，访问表的数据&二、INDEX JOINindex join是针对单表上的不同索引之间的连接SQL_ID 7qdwg0qwn6tgm, child number 0-------------------------------------select /*+index_join(a index_emp_DEPTNO IND_EMP_JOB)*/ deptno,job from scott.emp a where a.deptno=20 and a.job='SALESMAN' Plan hash value:
---------------------------------------------------------------------------------------| Id | Operation
| Rows | Bytes | Cost (%CPU)| Time
|---------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
||* 1 | VIEW
| index$_join$_001 |
3 (34)| 00:00:01 ||* 2 |
INDEX RANGE SCAN| IND_EMP_JOB
(0)| 00:00:01 ||* 4 |
INDEX RANGE SCAN| INDEX_EMP_DEPTNO |
(0)| 00:00:01 |--------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------
1 - filter(("A"."JOB"='SALESMAN' AND "A"."DEPTNO"=20))
2 - access(ROWID=ROWID)
3 - access("A"."JOB"='SALESMAN')
4 - access("A"."DEPTNO"=20) 通过IND_EMP_JOB取出索引信息，通过INDEX_EMP_DEPTNO取出索引信息，这两个索引信息关联，rowid=rowid，在过滤条件filter(("A"."JOB"='SALESMAN' AND "A"."DEPTNO"=20))，取出信息&三、VIEWOracle处理包含SQL时，根据视图是否能够视图合并（VIEW Merging），对应的执行计划有两种。视图合并SQL语句有视图，在语句中会展开，在执行计划中很可能不会出现VIEW，但是又可能还是存在,查看视图合并的例子&create or replace view emp_view as select * from scott.emp where deptno=30select * from emp_view where job='SALESMAN'select * from table(dbms_xplan.display_cursor(null,null))SQL_ID dwtdzmud7wdqs, child number 0-------------------------------------select * from emp_view where job='SALESMAN' Plan hash value:
----------------------------------------------------------------------------------------------| Id | Operation
| Rows | Bytes | Cost (%CPU)| Time
|----------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
||* 1 | TABLE ACCESS BY INDEX ROWID| EMP
(0)| 00:00:01 ||* 2 |
INDEX RANGE SCAN
| IND_EMP_JENAME |
(0)| 00:00:01 |---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------
1 - filter("DEPTNO"=30)
2 - access("JOB"='SALESMAN') 第一步走了索引 IND_EMP_JENAME& access("JOB"='SALESMAN')，第二部过滤filter("DEPTNO"=30) ，视图已经合并不做视图合并执行计划中出现关键字&VIEW&，定义视图中存在ROWNUMcreate or replace view emp_view as select * from scott.emp where deptno=30 and rownum&10select * from emp_view where job='SALESMAN'select * from table(dbms_xplan.display_cursor(null,null))SQL_ID dwtdzmud7wdqs, child number 0-------------------------------------select * from emp_view where job='SALESMAN' Plan hash value:
--------------------------------------------------------------------------------------------------| Id | Operation
| Rows | Bytes | Cost (%CPU)| Time
|--------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
||* 1 | VIEW
| EMP_VIEW
(0)| 00:00:01 ||* 2 |
COUNT STOPKEY
TABLE ACCESS BY INDEX ROWID| EMP
(0)| 00:00:01 ||* 4 |
INDEX RANGE SCAN
| INDEX_EMP_DEPTNO |
(0)| 00:00:01 |-------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------
1 - filter("JOB"='SALESMAN')
2 - filter(ROWNUM&10)
4 - access("DEPTNO"=30)执行计划中存在VIEW，视图为单独执行&四、FILTER得到一个驱动结果集根据一定的过滤条件从上述驱动结果集中滤除不满足条件的记录结果集中剩下的记录就会返回给最终用户或者继续参与下一个执行步骤select /*+gather_plan_statistics*/ * from scott.emp where deptno in (select /*+no_unnest*/ deptno from scott.dept)select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS +COST'))SQL_ID 4xu8ns03jbd69, child number 0-------------------------------------select /*+gather_plan_statistics*/ * from scott.emp where deptno in (select /*+no_unnest*/ deptno from scott.dept) Plan hash value:
-----------------------------------------------------------------------------------------------------| Id | Operation
| Starts | E-Rows | Cost (%CPU)| A-Rows |
| Buffers |-----------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
11 |00:00:00.01 |
9 ||* 1 | FILTER
11 |00:00:00.01 |
TABLE ACCESS FULL| EMP
14 |00:00:00.01 |
INDEX UNIQUE SCAN| PK_DEPT |
2 |00:00:00.01 |
3 |----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------
1 - filter( IS NOT NULL)
3 - access("DEPTNO"=:B1)FILTER访问跟nested loop不同，驱动表在访问被驱动表时，会对关联字段做DISTINCT，如EMP.DEPTNO做DISTINCT为3，实际运行的次数（START）为3次。不是实际行数14的次数。如果是NESTED LOOP就需要14次了以后是NESTED LOOP的例子对比select /*+gather_plan_statistics*/ * from scott.emp where deptno in (select deptno from scott.dept)select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS +COST'))SQL_ID bku72zf75w5rk, child number 0-------------------------------------select /*+gather_plan_statistics*/
* from scott.emp where deptno in (select deptno from scott.dept) Plan hash value:
-----------------------------------------------------------------------------------------------------| Id | Operation
| Starts | E-Rows | Cost (%CPU)| A-Rows |
| Buffers |-----------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
11 |00:00:00.01 |
1 | NESTED LOOPS
11 |00:00:00.01 |
TABLE ACCESS FULL| EMP
14 |00:00:00.01 |
INDEX UNIQUE SCAN| PK_DEPT |
11 |00:00:00.01 |
4 |----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------
3 - access("DEPTNO"="DEPTNO")可以清晰看到被驱动表，执行次数是14次FILTER类型的执行计划实际上是一种改良的嵌套循环连接，他并不像嵌套循环连接那样，驱动结果中的有多少记录就得访问多少次被驱动表&五、SORTSORT AGGREGATESORT UNIQUESORT JOINSORT GROUP BYSORT ORDER BYBUFFER SORT执行计划中出现关键字&SORT&，也不一定意味着就需要排序，如SORT AGGREGATE和BUFFER SORT不一定需要排序（一）、SORT AGGREGATE& set autotrace & select sum(sal) from scott.emp where deptno=30
2 ;执行计划----------------------------------------------------------Plan hash value: -------------------------------------------------------------------------------------------------| Id | Operation
| Rows | Bytes | Cost (%CPU)| Time
|-------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
(0)| 00:00:01 ||
1 | SORT AGGREGATE
TABLE ACCESS BY INDEX ROWID| EMP
(0)| 00:00:01 ||* 3 |
INDEX RANGE SCAN
| INDEX_EMP_DEPTNO |
(0)| 00:00:01 |-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------
3 - access("DEPTNO"=30)统计信息----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
535 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed&看到sorts（memory）、sorts（disk）为0，无任何排序，但是在执行计划中可以看到sort aggregate（二）、SORT UNIQUE& select distinct job from scott.emp where deptno=30执行计划----------------------------------------------------------Plan hash value: -------------------------------------------------------------------------------------------------| Id | Operation
| Rows | Bytes | Cost (%CPU)| Time
|-------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
4 (50)| 00:00:01 ||
1 | SORT UNIQUE
3 (34)| 00:00:01 ||
TABLE ACCESS BY INDEX ROWID| EMP
(0)| 00:00:01 ||* 3 |
INDEX RANGE SCAN
| INDEX_EMP_DEPTNO |
(0)| 00:00:01 |-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------
3 - access("DEPTNO"=30)统计信息----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
605 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed查看sorts（memory）有存在排序（三）、SORT JOIN& select /*+use_merge(a b)*/ * from scott.emp a,scott.dept b where a.deptno=b.已选择11行。执行计划----------------------------------------------------------Plan hash value: ----------------------------------------------------------------------------------------| Id | Operation
| Rows | Bytes | Cost (%CPU)| Time
|----------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
6 (17)| 00:00:01 ||
1 | MERGE JOIN
6 (17)| 00:00:01 ||
TABLE ACCESS BY INDEX ROWID| DEPT
(0)| 00:00:01 ||
INDEX FULL SCAN
| PK_DEPT |
(0)| 00:00:01 ||* 4 |
4 (25)| 00:00:01 ||
TABLE ACCESS FULL
(0)| 00:00:01 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------
4 - access("A"."DEPTNO"="B"."DEPTNO")
filter("A"."DEPTNO"="B"."DEPTNO")统计信息----------------------------------------------------------
3 recursive calls
0 db block gets
16 consistent gets
1 physical reads
0 redo size
1730 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
11 rows processed1 sorts (memory)存在排序&（五）、SORT GROUP BY& select job from scott.emp where deptno=30 group执行计划----------------------------------------------------------Plan hash value: -------------------------------------------------------------------------------------------------| Id | Operation
| Rows | Bytes | Cost (%CPU)| Time
|-------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
3 (34)| 00:00:01 ||
1 | SORT GROUP BY
3 (34)| 00:00:01 ||
TABLE ACCESS BY INDEX ROWID| EMP
(0)| 00:00:01 ||* 3 |
INDEX RANGE SCAN
| INDEX_EMP_DEPTNO |
(0)| 00:00:01 |-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------
3 - access("DEPTNO"=30)统计信息----------------------------------------------------------
38 recursive calls
0 db block gets
51 consistent gets
0 physical reads
0 redo size
605 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
3 rows processed7 sorts (memory) 通过group by order by，当列为非NULL索引时，是不会排序的&（六）、SORT ORDER BY& select job from scott.emp where deptno=30已选择6行。执行计划----------------------------------------------------------Plan hash value: -------------------------------------------------------------------------------------------------| Id | Operation
| Rows | Bytes | Cost (%CPU)| Time
|-------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
3 (34)| 00:00:01 ||
1 | SORT ORDER BY
3 (34)| 00:00:01 ||
TABLE ACCESS BY INDEX ROWID| EMP
(0)| 00:00:01 ||* 3 |
INDEX RANGE SCAN
| INDEX_EMP_DEPTNO |
(0)| 00:00:01 |-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------
3 - access("DEPTNO"=30)统计信息----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
620 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed1 sorts (memory) order by子句会产生排序，执行计划的体现sort order by&（七）、BUFFER SORT& select * from scott.emp a,scott.dept b
2 ;已选择42行。执行计划----------------------------------------------------------Plan hash value: -----------------------------------------------------------------------------| Id | Operation
| Name | Rows | Bytes | Cost (%CPU)| Time
|-----------------------------------------------------------------------------|
0 | SELECT STATEMENT
42 | 2394 |
(0)| 00:00:01 ||
1 | MERGE JOIN CARTESIAN|
42 | 2394 |
(0)| 00:00:01 ||
TABLE ACCESS FULL | DEPT |
(0)| 00:00:01 ||
BUFFER SORT
(0)| 00:00:01 ||
TABLE ACCESS FULL | EMP |
(0)| 00:00:01 |-----------------------------------------------------------------------------统计信息----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
5 physical reads
0 redo size
3449 bytes sent via SQL*Net to client
541 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
42 rows processedbuffer sort表示ORACLE会用PGA把扫描结果load进去，这样的好处是省掉相对应的缓存在SGA的开销buffer sort可能排序，可能也不会的。还有一种方式查看是否存在排序，在执行计划中存在Column Projection Information (identified by operation id):&& 1 - (#keys=1) "JOB"[VARCHAR2,9]#keys=1，大于1，说明排序数量为1，如果为0，没有排序select distinct job from scott.emp where deptno=30 select * from table(dbms_xplan.display_cursor(null,null,'advanced'))SQL_ID 27vj2ut1x96m3, child number 0-------------------------------------select distinct job from scott.emp where deptno=30 order by job Plan hash value:
-------------------------------------------------------------------------------------------------| Id | Operation
| Rows | Bytes | Cost (%CPU)| Time
|-------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
1 | SORT UNIQUE
3 (34)| 00:00:01 ||
TABLE ACCESS BY INDEX ROWID| EMP
(0)| 00:00:01 ||* 3 |
INDEX RANGE SCAN
| INDEX_EMP_DEPTNO |
(0)| 00:00:01 |------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------
2 - SEL$1 / $1
3 - SEL$1 / $1 Outline Data-------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('optimizer_dynamic_sampling' 0)
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."DEPTNO"))
END_OUTLINE_DATA */ Predicate Information (identified by operation id):---------------------------------------------------
3 - access("DEPTNO"=30) Column Projection Information (identified by operation id):-----------------------------------------------------------
1 - (#keys=1) "JOB"[VARCHAR2,9]
2 - "JOB"[VARCHAR2,9]
3 - "EMP".ROWID[ROWID,10] 六、UNION/UNION ALLUNION 是将两个结果集合并，去掉重复并排序。union 先做UNION ALL，在做SORT UNIQUEselect deptno from scott.emp unionselect deptno from scott.dept
select * from table(dbms_xplan.display_cursor(null,null))SQL_ID 9r3apuuwjtbgx, child number 0-------------------------------------select deptno from scott.emp
union select deptno from scott.dept Plan hash value:
--------------------------------------------------------------------------------------| Id | Operation
| Rows | Bytes | Cost (%CPU)| Time
|--------------------------------------------------------------------------------------|
0 | SELECT STATEMENT |
1 | SORT UNIQUE
4 (75)| 00:00:01 ||
INDEX FULL SCAN| INDEX_EMP_DEPTNO |
(0)| 00:00:01 ||
INDEX FULL SCAN| PK_DEPT
(0)| 00:00:01 |-------------------------------------------------------------------------------------- union all就是两个结果合并，不做任何处理select deptno from scott.emp union allselect deptno from scott.dept
select * from table(dbms_xplan.display_cursor(null,null))SQL_ID f42g872sqp9hd, child number 0-------------------------------------select deptno from scott.emp
union all select deptno from scott.dept Plan hash value:
-------------------------------------------------------------------------------------| Id | Operation
| Rows | Bytes | Cost (%CPU)| Time
|-------------------------------------------------------------------------------------|
0 | SELECT STATEMENT |
1 | UNION-ALL
INDEX FULL SCAN| INDEX_EMP_DEPTNO |
(0)| 00:00:01 ||
INDEX FULL SCAN| PK_DEPT
(0)| 00:00:01 |-------------------------------------------------------------------------------------union all比union的性能好很多，尽量用union all&七、CONCATCONCAT就是 IN-LIST扩展（IN-LIST EXPANSION） 或OR扩展（OR EXPANSION），执行计划中对应CONCATENATION。select * from scott.emp where job in ('SALESMAN','MANAGER')select * from table(dbms_xplan.display_cursor(null,null))SQL_ID 1sz0ywa9m6k1u, child number 0-------------------------------------select * from scott.emp where job in ('SALESMAN','MANAGER') Plan hash value:
-----------------------------------------------------------------------------------------------| Id | Operation
| Rows | Bytes | Cost (%CPU)| Time
|-----------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
1 | INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID| EMP
(0)| 00:00:01 ||* 3 |
INDEX RANGE SCAN
| IND_EMP_JENAME |
(0)| 00:00:01 |----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------
3 - access(("JOB"='MANAGER' OR "JOB"='SALESMAN')) 在未扩展之前，采用的是INLIST ITERATOR，可以指定hint（use_concate）,事件设置alter session set events '10142 trace name context forever'alter session set events '10157 trace name context forever' select /*+use_concat*/
* from scott.emp where job in ('SALESMAN','MANAGER')select * from table(dbms_xplan.display_cursor(null,null))SQL_ID 6u1d9uaruw10d, child number 0-------------------------------------select /*+use_concat*/
* from scott.emp where job in ('SALESMAN','MANAGER') Plan hash value:
-----------------------------------------------------------------------------------------------| Id | Operation
| Rows | Bytes | Cost (%CPU)| Time
|-----------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
1 | CONCATENATION
TABLE ACCESS BY INDEX ROWID| EMP
(0)| 00:00:01 ||* 3 |
INDEX RANGE SCAN
| IND_EMP_JENAME |
(0)| 00:00:01 ||
TABLE ACCESS BY INDEX ROWID| EMP
(0)| 00:00:01 ||* 5 |
INDEX RANGE SCAN
| IND_EMP_JENAME |
(0)| 00:00:01 |----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------
3 - access("JOB"='MANAGER')
5 - access("JOB"='SALESMAN') 通常INLIST ITERATOR比CONCATENATION性能好。&内容来源：《基于oracle的SQL优化》
、 、 、 、 、