在 Oracle 数据库中,执行计划是 SQL 优化最为复杂也是最为关键的一部分,因为它实际上 代表了目标 SQL 在 Oracle 数据库内部的具体执行步骤,只有知道并理解了这些执行步骤,我们才能知道优化器选择的执行计划是否为当前情形下最优的执行计划,也才能知道是否需要对当前的执行计划做出调整。

权限/xplan包权限

SQL> grant dba to optimizer;
SQL> grant select any dictionary to optimizer;
SQL> grant select on v_$database to optimizer;
SQL> grant select on v_$sql_plan to optimizer;
SQL> grant select on v_$session to optimizer;
SQL> grant select on v_$mystat to optimizer;
SQL> grant select on dba_hist_sql_plan to optimizer;
SQL> grant create type to optimizer;  
SQL> grant create procedure to optimizer;

SQL> conn optimizer/optimizer;
SQL> CREATE OR REPLACE TYPE xplan_ot AS OBJECT(plan_table_output VARCHAR2(300));
SQL> CREATE OR REPLACE TYPE xplan_ntt AS TABLE OF xplan_ot;

数据准备

sqlplus optimizer/optimizer@ip:port/instance

SQL> create table t1 as select * from dba_objects;

SQL> create table t2 as select * from dba_objects;

SQL> create index idx_t1 on t1(object_id);

SQL> create index idx_t2 on t2(object_id);

执行计划是什么?

Oracle 用来执行目标 SQL 语句的这些步骤的组合就称为执行计划

执行计划会保持至 SGA 的 Shared Pool 中

获取执行计划的方式

explain plan for

SQL> set linesize 1000;
SQL> set pagesize 1000;
SQL> set long 10000;

SQL> explain plan for 
select * from t1 inner join t2 on t1.object_id = t2.object_id where t1.object_id in (18, 19);

SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------
Plan hash value: 2809694817

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |   117 | 48438 |     8   (0)| 00:00:01 |
|*  1 |  HASH JOIN                    |        |   117 | 48438 |     8   (0)| 00:00:01 |
|   2 |   INLIST ITERATOR             |        |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T2     |    15 |  3105 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_T2 |     2 |       |     3   (0)| 00:00:01 |
|   5 |   INLIST ITERATOR             |        |       |       |            |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| T1     |    15 |  3105 |     4   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | IDX_T1 |     2 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   4 - access("T2"."OBJECT_ID"=18 OR "T2"."OBJECT_ID"=19)
   7 - access("T1"."OBJECT_ID"=18 OR "T1"."OBJECT_ID"=19)

Note
-----
   - dynamic sampling used for this statement (level=2)

优点:
无需真正执行,快捷方便

缺点:

  • 无法获取 逻辑读 、物理读 、表访问次数 、递归调用次数
  • 并不是真正的统计信息,因为 SQL 语句并未真正执行

set autotrace on

SQL> set autotrace traceonly

SQL> select * from t1 inner join t2 on t1.object_id = t2.object_id where t1.object_id in (18, 19);
执行计划
----------------------------------------------------------
Plan hash value: 2809694817

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |   117 | 48438 |     8   (0)| 00:00:01 |
|*  1 |  HASH JOIN                    |        |   117 | 48438 |     8   (0)| 00:00:01 |
|   2 |   INLIST ITERATOR             |        |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T2     |    15 |  3105 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_T2 |     2 |       |     3   (0)| 00:00:01 |
|   5 |   INLIST ITERATOR             |        |       |       |            |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| T1     |    15 |  3105 |     4   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | IDX_T1 |     2 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   4 - access("T2"."OBJECT_ID"=18 OR "T2"."OBJECT_ID"=19)
   7 - access("T1"."OBJECT_ID"=18 OR "T1"."OBJECT_ID"=19)

Note
-----
   - dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
          7  recursive calls        
          0  db block gets          
        132  consistent gets        
          6  physical reads         
          0  redo size
       2399  bytes sent via SQL*Net to client
        472  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

关键字释义:

序号 关键字 说明
1 recursive calls 递归调用次数
2 db block gets 从磁盘获取的数据块
3 consistent gets 逻辑读
4 physical reads 物理读
5 sorts (memory) 内存排序次数
6 sorts (disk) 磁盘排序次数

优点:

  • 可以看到 逻辑读 、物理读 、递归调用次数
  • 真实的执行计划

缺点:
无法看到 表的访问次数

statistics_level=all

SQL> alter session set statistics_level = all;

SQL> select * from t1 inner join t2 on t1.object_id = t2.object_id where t1.object_id in (18, 19);

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
Plan hash value: 2809694817

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |      1 |        |      2 |00:00:00.01 |      12 |       |       |          |
|*  1 |  HASH JOIN                    |        |      1 |     97 |      2 |00:00:00.01 |      12 |   874K|   874K|  520K (0)|
|   2 |   INLIST ITERATOR             |        |      1 |        |      2 |00:00:00.01 |       5 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T2     |      2 |     14 |      2 |00:00:00.01 |       5 |       |       |          |
|*  4 |     INDEX RANGE SCAN          | IDX_T2 |      2 |      2 |      2 |00:00:00.01 |       4 |       |       |          |
|   5 |   INLIST ITERATOR             |        |      1 |        |      2 |00:00:00.01 |       7 |       |       |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| T1     |      2 |     14 |      2 |00:00:00.01 |       7 |       |       |          |
|*  7 |     INDEX RANGE SCAN          | IDX_T1 |      2 |      2 |      2 |00:00:00.01 |       5 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------

关键字释义:

序号 关键字 说明
1 Starts 该 sql 执行的次数
2 E-Rows 执行计划预计返回的行数
3 A-Rows 执行计划实际返回的行数
4 A-Time 每一步实际执行的时间
5 Buffers 每一步实际执行的逻辑读或一致性读
6 OMem 当前操作完成所有内存工作区(Work Aera)操作所总共使用私有内存(PGA)中工作区的大小
7 1Mem 当工作区大小无法满足操作所需的大小时,需要将部分数据写入临时磁盘空间中,该列数据为语句最后一次执行中,单次写磁盘所需要的内存大小
8 User-Mem 语句最后一次执行中,当前操作所使用的内存工作区大小

优点:

  • 可以看出 SQL执行次数(表访问次数)
  • 可以看出 清晰地从 E-RowsA-Rows 中得到预测的行数和真实的行数,从而可以准确判断 Oracle 评估是否准确
  • 可以看出 Buffers 逻辑读大小
  • 真实执行计划

缺点:

  • 必须要等到语句真正执行完毕后,才可以出结果
  • 无法查看 递归调用次数 、排序次数

dbms_xplan/xplan

SQL> col sql_text format a60;

SQL> select /*+ xplan_example1 */ * from t1 inner join t2 on t1.object_id = t2.object_id where t1.object_id in (18, 19);

SQL> select sql_text, sql_id, child_number from v$sql where sql_text like 'select /*+ xplan_example1 */%';

SQL_TEXT                                                     SQL_ID        CHILD_NUMBER
------------------------------------------------------------ ------------- ------------
select /*+ xplan_example1 */ * from t1 inner join t2 on t1.o 6jn30n1x0kntc            0
bject_id = t2.object_id where t1.object_id in (18, 19)
  • dbms_xplan.display_cursor()
SQL> select * from table(dbms_xplan.display_cursor('6jn30n1x0kntc', 0, 'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------

SQL_ID  6jn30n1x0kntc, child number 0
-------------------------------------
select /*+ xplan_example1 */ * from t1 inner join t2 on t1.object_id =
t2.object_id where t1.object_id in (18, 19)

Plan hash value: 2809694817

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |       |       |     8 (100)|          |
|*  1 |  HASH JOIN                    |        |    97 | 40158 |     8   (0)| 00:00:01 |
|   2 |   INLIST ITERATOR             |        |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T2     |    14 |  2898 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_T2 |     2 |       |     3   (0)| 00:00:01 |
|   5 |   INLIST ITERATOR             |        |       |       |            |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| T1     |    14 |  2898 |     4   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | IDX_T1 |     2 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
  • xplan.display_cursor()
SQL> select * from table(xplan.display_cursor('6jn30n1x0kntc', 0, 'advanced'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
-------------------------------
SQL_ID  6jn30n1x0kntc, child number 0
-------------------------------------
select /*+ xplan_example1 */ *  from t1 inner join t2 on t1.object_id = t2.object_id where t1.object_id in (18, 19)

Plan hash value: 2809694817

------------------------------------------------------------------------------------------------
| Id  | Order | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 |     8 | SELECT STATEMENT              |        |       |       |     8 (100)|          |
|*  1 |     7 |  HASH JOIN                    |        |   117 | 48438 |     8   (0)| 00:00:01 |
|   2 |     3 |   INLIST ITERATOR             |        |       |       |            |          |
|   3 |     2 |    TABLE ACCESS BY INDEX ROWID| T2     |    15 |  3105 |     4   (0)| 00:00:01 |
|*  4 |     1 |     INDEX RANGE SCAN          | IDX_T2 |     2 |       |     3   (0)| 00:00:01 |
|   5 |     6 |   INLIST ITERATOR             |        |       |       |            |          |
|   6 |     5 |    TABLE ACCESS BY INDEX ROWID| T1     |    15 |  3105 |     4   (0)| 00:00:01 |
|*  7 |     4 |     INDEX RANGE SCAN          | IDX_T1 |     2 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

优点:

  • 根据 sql_id 即可获取执行计划
  • 可以得到执行计划的顺序
  • 真实执行计划

缺点:

  • 无法得到 逻辑读 、物理读 、递归调用
  • 无法得到 表的访问次数

10046

SQL> set linesize 120;
SQL> set pagesize 1000;
SQL> set long 5000;

SQL> alter session set statistics_level=typical;

激活 10046

SQL> alter session set events '10046 trace name context forever, level 12';

执行目标 SQL

SQL> select * from t1 inner join t2 on t1.object_id = t2.object_id where t1.object_id in (18, 19);

关闭 10046

SQL> alter session set events '10046 trace name context off';

查看 trace 文件路径

SQL> select 
  d.value || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || 
  '_ora_' || p.spid || '.trc' trace_file_name 
from (
  select p.spid from v$mystat m, v$session s, v$process p 
  where m.statistic#=1 and s.sid=m.sid and p.addr = s.paddr
) p, (
  select t.INSTANCE from v$thread t, v$parameter v 
  where v.name='thread' 
  and (v.value=0 or t.thread#=to_number(v.value))
) i, (
  select value from v$parameter where name='user_dump_dest'
) d;

TRACE_FILE_NAME
----------------------------------------------------------------
E:\app\diag\rdbms\cjdc\cjdc\trace/cjdc_ora_3400.trc

利用 tkprof 格式化输入

SQL> exit

C:\Windows\System32> tkprof E:\app\diag\rdbms\cjdc\cjdc\trace/cjdc_ora_3400.trc D:\10046.trc

********************************************************************************
SQL ID: 9acq8c8d3ctzx Plan Hash: 2809694817

select * 
from
 t1, t2 where t1.object_id = t2.object_id and t1.object_id in (18, 19)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          4          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          2         12          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          2         16          0           2
********************************************************************************

优点:

  • 可以看出 SQL 语句对应的等待事件
  • 可以看出处理的行数,产生的物理读和逻辑读
  • 可以看出解析时间和执行时间

缺点:

  • 无法判断表被访问了多少次
  • 无法看到条件谓词部分

awrreport

# 运行AWR脚本
SQL> @ORACLE_HOME/RDBMS/ADMIN/awrsqrpt.sql

# 选择快照的 begin_snap 和 end_snap

# 输入SQL_Id 的值

条件谓词

执行计划信息中,有两个重要的条件谓词 filteraccess
如果显示 access,就表示这个谓词条件的值将会影响数据的访问路径(表还是索引),而 filter 表示 SQL 语句中的查询条件并不会影响数据访问路径,只起到过滤的作用。

SQL> create table t3 as select * from dba_objects;

SQL> create table t4 as select * from dba_objects;

SQL> create table t5 as select * from dba_objects;

SQL> create index idx_t3 on t3(object_id);

SQL> create index idx_t4 on t4(object_id, object_name);

全表扫描

SQL> set autotrace traceonly;

SQL> select * from t5 where object_id = 100;

执行计划
----------------------------------------------------------
Plan hash value: 2002323537

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    15 |  3105 |   379   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T5   |    15 |  3105 |   379   (1)| 00:00:05 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)  

无索引,走全表扫描,谓词条件在这里只是起到数据过滤的作用,所以使用了 filter

索引扫描

SQL> select * from t3 where t3.object_id = 100;

执行计划
----------------------------------------------------------
Plan hash value: 636101163

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |   207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T3     |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T3 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T3"."OBJECT_ID"=100)

有索引,谓词条件影响到数据访问的路径,选择了索引,所以用 access

索引扫描(索引列)

SQL> select object_id from t4 where object_id > 20000;

执行计划
----------------------------------------------------------
Plan hash value: 507314723

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        | 80218 |  1018K|   152   (1)| 00:00:02 |
|*  1 |  INDEX FAST FULL SCAN| IDX_T4 | 80218 |  1018K|   152   (1)| 00:00:02 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID">20000)

仅查询 object_id 列(索引列, 而返回结果集是无序的,故索引的扫描方式为 INDEX FAST FULL SCAN,且条件谓词是 filter

SQL> select object_id from t4 where object_id > 20000 order by object_id;

执行计划
----------------------------------------------------------
Plan hash value: 2149312467

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        | 80218 |  1018K|   458   (1)| 00:00:06 |
|*  1 |  INDEX RANGE SCAN| IDX_T4 | 80218 |  1018K|   458   (1)| 00:00:06 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID">20000 AND "OBJECT_ID" IS NOT NULL)

仅查询 object_id 列(索引列), 且有序查询, 故索引的扫描方式为 INDEX RANGE SCAN,且条件谓词是 access

执行计划分类

目录索引

序号 执行计划分类 明细
1 表访问相关执行计划 全表扫描
2 USER ROWID 扫描
3 INDEX ROWID 扫描
4 索引相关执行计划 索引唯一扫描
5 索引范围扫描
6 索引全扫描
7 索引快速全扫描
8 索引跳跃扫描
9 表连接相关执行计划 嵌套循环连接
10 哈希连接
11 排序合并连接
12 反连接
13 半连接
14 其它执行计划 AND-EQUAL
15 VIEW
16 FILTER
17 SORT
18 UNION/UNION ALL
19 CONNECT BY

表访问相关执行计划

  • 全表扫描(TABLE ACCESS FULL)
TABLE ACCESS FULL
  • ROWID 扫描(TABLE ACCESS BY USER ROWID)
TABLE ACCESS BY USER ROWID
  • ROWID 扫描(TABLE ACCESS BY INDEX ROWID)
TABLE ACCESS BY INDEX ROWID

索引相关执行计划

  • 索引唯一扫描(INDEX UNIQUE SCAN)
INDEX UNIQUE SCAN

说明:
至多只会返回一条记录
  • 索引范围扫描(INDEX RANGE SCAN)
INDEX RANGE SCAN

说明:
可能返回多条记录
  • 索引全扫描(INDEX FULL SCAN)
INDEX FULL SCAN

说明:
1. 按顺序扫描目标索引所有叶子块的所有索引行
2. 返回记录有序(索引键值列顺序)
  • 索引快速全扫描(INDEX FAST FULL SCAN)
INDEX FAST FULL SCAN

特性: 
1. 按顺序扫描目标索引所有叶子块的所有索引行,但可以使用多块读
2. 返回记录不一定有序
3. 不能回表
4. 其原理就是将索引当成表来操作了
  • 索引跳跃扫描(INDEX SKIP SCAN)
INDEX SKIP SCAN

特性:
1. 用于复合索引且仅以非前导列做查询条件
2. 前导列distinct值大小会影响索引跳跃扫描的效率

表连接相关执行计划

表连接的特性

  • 嵌套循环连接(NESTED LOOPS)
NESTED LOOPS

说明:
1. 驱动表的限制条件要考虑建立索引
2. 被驱动表的限制条件要考虑建立索引
3. 小结果集表作为驱动表, 大结果集表作为被驱动表。
  • 哈希连接(HASH JOIN)
HASH JOIN

说明:
1. 两表的限制条件有索引(根据返回结果集数量)
2. 小结果集表作为驱动表, 大结果集的表作为被驱动表
3. 尽量保证PGA能容纳Hash运算。
  • 排序合并连接(MERGE JOIN & SORT JOIN)
MERGE JOIN & SORT JOIN

说明:
1. 两表的限制条件有索引(根据返回结果集数量)
2. 避免SQL语句获取多余列的值导致排序尺寸过大
3. 确保PGA能完成排序,避免磁盘排序。
  • 反连接(ANTI)
HASH JOIN ANTI
MERGE JOIN ANTI
NESTED LOOPS ANTI
  • 半连接(SEMI)
HASH JOIN SEMI
MERGE JOIN SEMI
NESTED LOOPS SEMI

其它执行计划

  • AND-EQUAL(INDEX MERGE) HERE 条件中出现了多个针对不同单列的等值条件,并且这些列上都有单键值的索引。
SQL> drop table t purge;

SQL> create table t as select * from dba_objects;
SQL> create index idx_t1 on t(object_id);
SQL> create index idx_t2 on t(data_object_id);

SQL> set autotrace traceonly;

SQL> select /*+ and_equal(t idx_t1 idx_t2) */ object_id, data_object_id from t where t.object_id = 20 and t.data_object_id = 2;
执行计划
----------------------------------------------------------
Plan hash value: 1275876533

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     7 |   182 |     2   (0)| 00:00:01 |
|*  1 |  AND-EQUAL        |        |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T2 |    12 |       |     1   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN| IDX_T1 |    12 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T"."OBJECT_ID"=20 AND "T"."DATA_OBJECT_ID"=2)
   2 - access("T"."DATA_OBJECT_ID"=2)
   3 - access("T"."OBJECT_ID"=20)
  • VIEW Oracle 在处理包含视图的SQL时,根据该视图是否能做视图合并(View Merging),其对应的执行计划有:
# 1. 可以做视图合并
则Oracle在执行该SQL语句时可以直接针对该视图的基表(源表),此时SQL的执行计划很可能不会出现关键字 "VIEW"。

# 2. 不能做视图合并 则Oracle会把该视图看做一个整体并单独地执行它,此时SQL的执行计划会出现关键字 "VIEW"。
  • FILTER FILTER 是一种特殊的执行计划,它所对应的执行步骤是如下三步:   a. 得到一个驱动结果集;
      b. 根据一定的过滤条件从上述驱动结果集中过滤不满足条件的记录;
      c. 结果集中剩下的记录就会返回给最终用户或者继续参与下一个执行步骤。

  • SORT SORT就是排序的意思,常会以组合形式出现。

1. SORT UNIQUE 
排序 + 去重

2. SORT JOIN
排序合并

3. SORT ORDER BY
单纯的排序

4. SORT GROUP BY
排序 + 分组
  • UNION/UNION ALL UNION/UNION ALL 表示对两个结果集进行合并。

  • CONNECT BY 层次查询所对应的关键字。

参考资料

《基于 Oracle 的 SQL 优化 -- 崔华》
《收获,不止SQL优化 -- 梁敬彬》

原创文章,转载请注明出处:http://www.opcoder.cn/article/4/