本文深入探讨Oracle数据库的体系结构，包括实例、数据库、表空间、索引、表的设计等方面。详细介绍了实例的组成部分，如SGA、后台进程，以及表的类型，如普通堆表、全局临时表、分区表、索引组织表和簇表。重点讨论了索引在查询优化中的作用，包括聚合函数优化、排序优化、组合索引以及位图索引和函数索引。此外，还分析了不同表连接方式的特点和适用场景。
本文知识点和理论来源于《收获不止Oracle》一书，并对书中内容作出整理，以共同学习
文章整体结构： https://www.edrawsoft.cn/viewer/public/s/36020647239118
1、体系结构
学习oracle首先要了解其物理体系结构，也可以理解为oracle的整体架构。结合整理的思维导图，看出其体系结构可对应分为oracle实例和数据库文件。
1.1 实例instance
对图做下说明：
- 1、oracle由实例和数据库组成，上半部分为实例instance，下半部分为数据库Datebase
- 2、实例是由一个开辟的共享内存区SGA(system global area)和一系列后台进程组成。其中SGA主要被划分为共享池（共享缓冲区）、数据缓冲区和日志缓冲区，后台进程包括图示PMON、SMON、DBWR、LGWR、CKPT、LCKn、RECO、ARCH等一系列进程。
- 3、数据库是由数据文件，参数文件日志文件控制文件归档日志（重做）文件等系列文件组成，其中的归档日志最终可能会被转移到新的存储介质，用于数据备份和恢复。
- 4、图中有个PGA区，这也是一个开辟出来的内存区，和SGA最明显的区别在于，PGA不是共享内存区，是私有不共享的，S可以理解为共享的首字母。首先对于用户来讲，用户发起的无论查询还是更新的任何操作，都是在PGA先预处理，接下来才进入实例区域，由SGA和后台进程共同完成用户发起的请求。
PGA的具体作用主要三点：a) 保存用户连接信息，如会话属性，绑定变量
b) 保存用户权限等重要信息，当用户进程与数据库建立连接会话时，系统会将这个户的相关权限查询出来，保存在PGA区。
c) 当发起的指令需要排序时，就在PGA内存区内完成，如果放不下，超出部分就会在临时表空间中排序。也就是在磁盘中排序。
关于后台进程的解释，实际上oracle后台进程远不止图上列举的几个，这里列举的只是比较常见且比较重要的进程，下面依次介绍。
POMN
POMN含义为Process Monitor，是进程监视器。如果你在执行某更新语句，未提交时进程崩溃了，这个时候POMN会自动回滚操作，无须人工执行ROLLBACK，除此之外还可以干预后台进程，比如RECO进程异常失败了，此时POMN会重启RECO进程，如果遇得LGWR进程失败这样严重问题，POMN会做出终止实例这个激烈操作，用于防止数据错乱。
SMON
SMON 含义为System Monitor 理解为系统监视器。与POMN不同的是SMON关注的是系统级的进程而非单个进程，重点工作在于intance recovery，除此之外还有清理临时表空间，清理回滚表空间，合并空闲表空间，等。
LCKn
LCKn仅适用于RAC数据库，最多可以有10个进程（LCK0，LCK1，…，LCK9），用于实例间的封锁。
RECO
RECO用于分布式数据库的恢复，适用于两阶段提交下的应用场景。比如有多个数据库A,B,C，在发起的过程中A,B,C，都要成功，事务才会成功，只要有一个失败，就必须全部回滚。这里跟LCKn一样，使用场景比较特殊。
CKPT
CKPT由ORACLE的FAST_START_MTTR_TARGET参数控制，用于触发DBWR从数据缓存区写数据到磁盘，CKPT执行越频繁，DBWR写出就越频繁，这样数据库批量性能就越低，但是在数据库异常恢复的时候，会越迅速。
DBWR
DBWR是oracle最核心的进程之一，负责把数据从数据缓存区写入到磁盘，和CKPT进程相辅相成，因为是CKPT促进DBWR去写数据。不过DBWR也和LGWR也密切相关，因为DBWR将数据写到磁盘的时候，必须停止LGWR先完成日志缓冲区写到磁盘后，方才开工。
LGWR
这个进程的目的很简单，就是把日志缓冲区的数据从内存写到磁盘的redo文件中，完成数据库对象的创建，更新数据库操作过程的记录，redo文件的作用非同小可，可以用来对数据库的异常恢复，只要保护好redo文件和后续的对应归档文件，理论上计时数据库文件全部删光了，还是可以让数据库根据这些日志记录，把所有在数控中曾经发生的一切事情全部重做一遍，从而保证了数据库的安全。
因为日志文件如此重要，所以LGWR也和DBWR一样是数据库核心进程。
另外记录的日志需要保证有序，这样才能在数据恢复的时候才有意义，试想用户原本先执行创建表对象，再插入数据到表中去。在数据恢复的时候，因为日志没有保证顺序，导致先插入数据到表中，可是表还没有创建… >.<
为了解决这个问题，LGWR有5条严格的制度来执行：
- 每隔三秒钟，LGWR运行一次
- 任何commit触发，LGWR运行一次
- DBWR把数据从缓存写到磁盘，LGWR运行一次
- 日志缓冲区满三分之一或记录满1M，LGWR运行一次
- 联机日志文件切换，也触发LGWR
ARCH
ARCH是写日志写到需要覆盖重写的时候（日志缓冲区就那么大，写完会覆盖重写），触发ARCH进程去转移日志文件，复制出去形成归档日志文件，保存在磁盘，以防止日志丢失。
·
1.2 数据库
说到数据库，不得不涉及表空间（tablespace）、段（segment）、区（extent）、块（block）。ORACLE正是通过表空间，段，块，区控制磁盘的合理使用，如图：
可以看出，数据库由若干表空间组成，表空间由若干段组成，段由若干区组成，区又有oracle最小单元块组成。也就是说，一系列连续的块组成了区，一个或多个区组成了段，一个或多个段组成了表空间，而一个或多个表空间组成了DataBase（一个DataBase要想存在，必须至少需要有SYSTEM和UNDO表空间）。其中，表空间包含系统表空间，临时表空间，用户表空间，回滚表空间。除了用户表空间外，其他三个表空间各有各自用途，不可随意更改。
结合我们的思维导图（https://www.edrawsoft.cn/viewer/public/s/36020647239118 ），接下来要从最小的数据块说起。
我们知道所有数据在文件系统最小物理存储单位是字节，操作系统也有一个类似Oracle的块容量的参数block size，但是Oracle总是访问整个Oracle Block，而不是按照操作系统的block size来访问的。一般情况下，操作系统的块容量为512字节大小的整数倍，但是数据库块大小却一般默认为8Kb（也可以设置为2kb,4kb,16kb,32kb等其他大小），值为操作系统块容量的整数倍，因为可以减少IO操作。
至于怎么减少IO，举个列子，假如IO的大小设置为512字节（0.5kb）,如果DB的Block设置为1kb，正好需要两个IO系统块才可以容纳下，但是如果DB的block设置为0.8kb，必须要2个操作系统块区容纳，相当于1kb大小的空间只用了0.8kb，这样就浪费了0.2kb空间。
另外Oracle数据块并不是简单的往里插入数据，插满了就下一个块这么简单，除此之外他还额外提供了一定管理功能。数据块组成又细分为数据块头、表目录区、行目录区、可用空间区、行数据区五个部分。如图
对数据块5个部分的说明：
- 数据块头（header）中包含了次数据块的概要信息，列如块地址（block address）及此数据块所属的段（segment）的类型（比如到底是索引还是表）。
- 表目录区，只要有一行数据插入到数据块，那改行数据所在的表的信息将被存储在这个区域。
- 行目录区是什么呢？其实就是你存放你插入的行的地址。
- 可用空间区，就是块中的空余空间，这个空余空间多少由oracle的PCTFREE参数来设置，是可控制的。如果是10，表示该块将会空10%左右的空间。此外如果是表或者索引，该区域还会存储事务条目，大致有23字节左右的开销。
- 而行数据区就更简单理解了，就是存储具体的行的信息或者索引的信息，这部分占用了数据块的绝大部分空间。
对于块（block）的理解到位了，那么把连续的块组合到一起，就形成了区，Oracle中被称为EXTENT的数据逻辑存储分配单位就是这么形成的。EXTENT是Oracle的分配空间的最小单位，注意分配这个词。
接下来，当用户创建了一张T表，实质上也就是创建了一个Segment，只要segment创建成功，数据库就一定为其分配了若干数据块（data block）的初始数据扩展（initial extent），即便表中没有数据，但是这些初始数据扩展中的数据块已经为即将插入的数据源准备做好了。
每个segment的定义中都包含了数据扩展（extent）的存储参数（storage parameter），存储参数适用于各种类型的数据段。例如用户可以在create table语句中使用STORAGE 字句设定存储参数，来决定创建表时为其数据段（data segment）分配多少初始空间，或者限定一个表最多可以包含多少个数据扩展，如果用户没有指定存储参数，那么在创建表示使用表空间的默认存储参数。
若干个段（segment）又组成了表空间，根据表空间的功能，又可以划分为系统表空间，回滚表空间，临时表空间和用户表空间。
说了这么久，我们来用脚本试验一下,实际体会一下
以下查询说明数据块的大小是8kb（8192/1024），这是oracle的最小存储单位
也可以通过以下语句查询
表空间的创建：
-
普通数据表空间
-
临时表空间
注意 TEMPORARY和TEMPFILE关键字
-
回滚表空间
注意关键字UNDO
-
系统表空间
系统表空间和用户表空间都属于永久保留内容的表空间。
2、表的设计
根据思维导图的整理，我们知道oracle表可以分为普通堆表、全局临时表、分区表、索引组织表和簇表。可以说各种类型的表有各种类型的功能，这里我的理解为在遇到不同的业务场景下，该选则什么样的表才是最好的，即在什么场合选择什么技术。
2.1 普通堆表
2.11 更新开销大
先给出结论，普通堆表在更新操作时产生日志开销大。下面我们来验证：
首先准备分析数据库产生了多少日志脚本，如下：
SQL> SELECT a.name,b.value FROM v$statname a,v$mystat b WHERE a.STATISTIC#=b.STATISTIC# AND a.NAME='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 820
该脚本是利用 v$statname 和v $mystat两个动态性能来跟踪当前session操作产生的日志，使用方法简单：首先执行该脚本，查看日志大小，随即执行的的更新语句，再执行该脚本返回的日志大小，两者相减，就是你更新语句产生的日志大小。实验如下：
--首先该视图需要sys授权之后才可以使用执行。
SQL> grant all on v_$mystat to wmj;
授权成功。
SQL> grant all on v_$statname to wmj;
授权成功。
--创建测试表
SQL> CREATE TABLE t AS SELECT * FROM Dba_Objects;
Table created
-- 创建视图方便直接用SELECT * FROM v_redo_size查询；
SQL> CREATE OR REPLACE VIEW v_redo_size AS
2 SELECT A.NAME, B.VALUE
3 FROM V$STATNAME A, V$MYSTAT B
4 WHERE A.STATISTIC# = B.STATISTIC#
5 AND A.NAME = 'redo size';
View created
SQL>
--下面执行删除操作
SQL> SELECT * FROM v_redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 93904
SQL> DELETE FROM t;
72512 rows deleted
SQL> SELECT * FROM v_redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 26911740
SQL>
观察上图，可以看到删除语句产生了 26911740-93904=26817836的日志量，因为这个单位是字节，所以大概25M的日志。然后继续试验
SQL> INSERT INTO t SELECT * FROM dba_objects;
72513 rows inserted
SQL> SELECT * FROM v_redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 35155776
SQL>
插入语句35155776-26911740=8244036，大概7M的日志数据，接下来看修改更新
SQL> UPDATE t SET t.object_id = ROWNUM;
72513 rows updated
SQL> SELECT * FROM v_redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 47153872
SQL>
更新产生了47153872-35155776=11998096，大概11M数据。
以上代码说明无论删除更新还是插入，都会产生日志，这些日志用于数据库的备份和恢复。但是从性能来讲，写日志越多代表行动能越低。
2.12 删除无法释放空间
删除大家都知道怎么用，从以上看到删除産的日志最大，因为删除产生UNDO最多，而UNDO有需要REDO来保护，所以删除产生的redo 也最多，所以也有不少性能问题是和delete操作有关的。下面再看代码
SQL> drop table t PURGE;
表已删除。
SQL> CREATE TABLE t AS SELECT * FROM Dba_Objects;
表已创建。
SQL> set autotrace on;
SQL> select count(*) from t;
COUNT(*)
----------
72514
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 290 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 68280 | 290 (1)| 00:00:04 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
1104 consistent gets
1033 physical reads
0 redo size
529 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
SQL>
以上是为删除表示产生的物理读 1104 consistent gets，然后执行DELETE FROM t; ，在查看，推测物理读应该是减少。
SQL> DELETE FROM t;
已删除72514行。
SQL> commit;
提交完成。
SQL> set autotrace on;
SQL> select count(*) from t;
COUNT(*)
----------
0
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 290 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1 | 290 (1)| 00:00:04 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1038 consistent gets
0 physical reads
0 redo size
526 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
SQL>
对比发现 物理读 1038 consistent gets 并没有减少，为什么的呢？明明数据已经被删了，物理读却没有减少。下面在看代码
SQL> TRUNCATE TABLE t ;
表被截断。
SQL> set autotrace on;
SQL> select count(*) from t;
COUNT(*)
----------
0
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
20 recursive calls
1 db block gets
10 consistent gets
0 physical reads
96 redo size
526 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
SQL>
这时发现物理读 10 consistent gets大幅度下降，这说明什么？delete操作不能释放空间！虽然记录被删除了，但是这些空块依然存在，Oracle在查询时，依然会去查询这些空快，而TRUNCATE 是一种释放的高水平动作，直接把空块也回收掉，空间也就释放了。
2.13 表记录太大检索较慢
新建一张表，也就是一个SEGMENT，一般情况，对表查询，就是对SEGMENT所有的块进行遍历，依次找到我们的数据，但是数据越多，遍历的块也就越多，查询性能也就越慢。如何解决？Oracle提供两种技术，索引和分区。下面看代码
SQL> drop table t PURGE;
表已删除。
SQL> CREATE TABLE t AS SELECT * FROM Dba_Objects WHERE ROWNUM<=200;
表已创建。
SQL> CREATE INDEX idx_obj_id ON t(object_id);
索引已创建。
SQL> SET linesize 1000;
SQL> SET autotrace traceonly;
SQL> SELECT * FROM t WHERE t.object_id<=10;
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 134201588
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 1863 | 2 (0)|00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 9 | 1863 | 2 (0)|00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJ_ID | 9 | | 1 (0)|00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_ID"<=10)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
292 recursive calls
0 db block gets
40 consistent gets
0 physical reads
0 redo size
2319 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)
9 rows processed
SQL>
然后在看另外一个SQL:SELECT object_id FROM t WHERE t.object_id<=10;
SQL> SELECT object_id FROM t WHERE t.object_id<=10;
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 188501954
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 117 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_OBJ_ID | 9 | 117 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."OBJECT_ID"<=10)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
638 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)
9 rows processed
SQL>
对比执行计划，发现第二个SQL中少了个TABLE ACCESS BY INDEX ROWID，代表什么意思呢，就是说根据索引查数据时，会有一个先从索引找到记录，在根据索引列上的rowid定位到表中而返回索引列其他列的动作，这个就是TABLE ACCESS BY INDEX ROWID作用。同时，执行计划少了一步操作，在性能上也是由提升的。那么有没有办法让查询不走回表！！答案是有的，能实现这种功能的，普通表做不到，但是索引组织表可以，后面会介绍。
2.14 有序插入不能有序读出
在对普通表操作中，无法保证数据有序，理由就是把一行记录插入到块中，然后删除该行，接下来插入的数据就会填充空余的部分，这样读取出来的数据就无法保证有序。（当然，为了保证有序，可以用order by,但是数据量大的情况下，排序是非常消耗性能的操作）为了验证，看下面列子
SQL> drop table t PURGE;
表已删除。
SQL> CREATE TABLE t (
2 a INT ,
3 b VARCHAR2(4000) DEFAULT RPAD('*',4000,'*'),
4 c VARCHAR2(3000) DEFAULT RPAD('*',3000,'*')
5 );
表已创建。
SQL> INSERT INTO t(a)VALUES(1);
已创建 1 行。
SQL> INSERT INTO t(a)VALUES(2);
已创建 1 行。
SQL> INSERT INTO t(a)VALUES(3);
已创建 1 行。
SQL> SELECT A FROM t;
A
----------
1
2
3
SQL> DELETE FROM t WHERE a=2;
已删除 1 行。
SQL> INSERT INTO t(a)VALUES(4);
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> SELECT A FROM t;
A
----------
1
4
3
SQL>
2.2 全局临时表
何为全局临时表，全局临时表可以理解为中间表，只用于临时保存某些数据，全局临时表分为两类，一是基于session的，另一是基于事务的。下面创建临时表，创建语法大家百度一下
SQL> CREATE GLOBAL temporary TABLE t_tmp_session ON COMMIT PRESERVE ROWS AS SELE
CT * FROM dba_objects WHERE 1=2;
表已创建。
SQL> SELECT table_name,temporary,duration FROM user_tables WHERE table_name='T_T
MP_SESSION';
TABLE_NAME T DURATION
------------------------------ - ------------------------------
T_TMP_SESSION Y SYS$SESSION
SQL> CREATE GLOBAL temporary TABLE t_tmp_transaction ON COMMIT DELETE ROWS AS SE
LECT * FROM dba_objects WHERE 1=2;
表已创建。
SQL> SELECT table_name,temporary,duration FROM user_tables WHERE table_name='T_T
MP_TRANSACTION';
TABLE_NAME T DURATION
------------------------------ - ------------------------------
T_TMP_TRANSACTION Y SYS$TRANSACTION
SQL>
观察各类DML产生日志量
SQL> SELECT * FROM v_redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 132308
SQL> INSERT INTO t_tmp_transaction SELECT * FROM dba_objects ;
已创建72517行。
SQL> SELECT * FROM v_redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 536720
SQL>
---- insert 产生536720-132308=404412 （约0.3M）
SQL> INSERT INTO t_tmp_session SELECT * FROM dba_objects ;
已创建72517行。
SQL> SELECT * FROM v_redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 941040
SQL>
---- insert 产生941040-536720=404320（约0.3M）
SQL> UPDATE t_tmp_transaction t SET t.object_id=ROWNUM;
已更新72517行。
SQL> SELECT * FROM v_redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 5899324
SQL>
---- 更新产生5899324-941040=4958284（约4M）
SQL> UPDATE t_tmp_session t SET t.object_id=ROWNUM;
已更新72517行。
SQL> SELECT * FROM v_redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 10483600
SQL>
---- 更新产生10483600-5899324=4584276（约4M）
SQL> DELETE FROM t_tmp_session;
已删除72517行。
SQL> SELECT * FROM v_redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 32618336
SQL>
---- 删除产生32618336-10483600=22134736（约21M）
SQL> DELETE FROM t_tmp_transaction;
已删除72517行。
SQL> SELECT * FROM v_redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 54752720
SQL>
---- 删除产生54752720-32618336=22134384（约21M）
然后看下对普通表的操作个DML日志
SQL> drop table t PURGE;
表已删除。
SQL> CREATE TABLE t AS SELECT * FROM Dba_Objects WHERE 1=2;
表已创建。
SQL> SELECT * FROM v_redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 54783200
SQL> INSERT INTO t SELECT * FROM dba_objects ;
已创建72517行。
SQL> SELECT * FROM v_redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 63243384
SQL>
---- insert 产生941040-536720=8460184（约8M）
SQL> UPDATE t t SET t.object_id=ROWNUM;
已更新72517行。
SQL> SELECT * FROM v_redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 72290680
SQL>
---- 更新产生72290680-63243384=9047296（约8M）
SQL> DELETE FROM t;
已删除72517行。
SQL> SELECT * FROM v_redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 99131032
SQL>
---- 删除产生99131032-72290680=26840352（约25M）
通过上面实验脚本，我们发现全局临时表，在DML操作上产生的日志较普通表要少，但是仅仅是这些吗？并不是！！
全局临时表有两大特性。一是高效删除，基于session的临时表，在session会话退出时，表记录自动清空删除，而基于事务的临时表，在事务完成之后，表记录也会清空，这点大家可以分别开启两个事务或者两个session的会话来实验一下；另外一个特性是不同会话独立，比较容易理解，就是不同session或者不同事务下面的临时表互不影响，比如A会话在临时表a中新增数据，B也在临时表a中新增数据，那么A会话看到的数据不会因为B会话的操作而变化！事务也是一样。
2.3 分区表
分区表顾名思义，将表划分开，大表划分成小表，真是这样的嘛？下面且看
2.3.1分区表分类
分区表分为四类：范围分区、列表分区、哈希分区、组合分区。最常用的是范围分区，下面以范围分区为例展开分析，其他分区表和范围分区原理类似。
首先创建范围分区表，并插入2018年一整年随机日期(10W条)
SQL> CREATE TABLE range_part_tab(
2 ID NUMBER,
3 deal_date DATE,
4 area_code NUMBER,
5 CONTENTS VARCHAR(4000)
6 )
7 PARTITION BY RANGE(deal_date)
8 (
9 PARTITION p1 VALUES LESS THAN (to_date('2018-02-01','YYYY-MM-DD')),
10 PARTITION p2 VALUES LESS THAN (to_date('2018-03-01','YYYY-MM-DD')),
11 PARTITION p3 VALUES LESS THAN (to_date('2018-04-01','YYYY-MM-DD')),
12 PARTITION p4 VALUES LESS THAN (to_date('2018-05-01','YYYY-MM-DD')),
13 PARTITION p5 VALUES LESS THAN (to_date('2018-06-01','YYYY-MM-DD')),
14 PARTITION p6 VALUES LESS THAN (to_date('2018-07-01','YYYY-MM-DD')),
15 PARTITION p7 VALUES LESS THAN (to_date('2018-08-01','YYYY-MM-DD')),
16 PARTITION p8 VALUES LESS THAN (to_date('2018-09-01','YYYY-MM-DD')),
17 PARTITION p9 VALUES LESS THAN (to_date('2018-10-01','YYYY-MM-DD')),
18 PARTITION p10 VALUES LESS THAN (to_date('2018-11-01','YYYY-MM-DD')),
19 PARTITION p11 VALUES LESS THAN (to_date('2018-12-01','YYYY-MM-DD')),
20 PARTITION p12 VALUES LESS THAN (to_date('2019-01-01','YYYY-MM-DD')),
21 PARTITION p_max VALUES LESS THAN (MAXVALUE)
22 );
表已创建。
SQL> INSERT INTO RANGE_PART_TAB
2 (ID, DEAL_DATE, AREA_CODE, CONTENTS)
3 SELECT ROWNUM,
4 TO_DATE(TO_CHAR(SYSDATE - 365, 'J') +
5 TRUNC(DBMS_RANDOM.VALUE(0, 365)),
6 'J'),
7 CEIL(DBMS_RANDOM.VALUE(590, 599)),
8 RPAD('*', 400, '*')
9 FROM DUAL
10 CONNECT BY ROWNUM < 100000;
已创建99999行。
为了对比分区表原理，我们在创建一个普通表，也插入10w条数据。
SQL> CREATE TABLE norm_tab(
2 ID NUMBER,
3 deal_date DATE,
4 area_code NUMBER,
5 CONTENTS VARCHAR(4000)
6 );
表已创建。
SQL> INSERT INTO norm_tab
2 (ID, DEAL_DATE, AREA_CODE, CONTENTS)
3 SELECT ROWNUM,
4 TO_DATE(TO_CHAR(SYSDATE - 365, 'J') +
5 TRUNC(DBMS_RANDOM.VALUE(0, 365)),
6 'J'),
7 CEIL(DBMS_RANDOM.VALUE(590, 599)),
8 RPAD('*', 400, '*')
9 FROM DUAL
10 CONNECT BY ROWNUM < 100000;
已创建99999行。
SQL> commit;
提交完成。
SQL>
观察分区表与普通表在表段上的区别
SQL> SET linesize 666;
SQL> SET pagesize 5000;
SQL> COLUMN segment_name format a20;
SQL> COLUMN partition_name format a20;
SQL> COLUMN segment_type format a20;
SQL> SELECT SEGMENT_NAME,
2 PARTITION_NAME,
3 SEGMENT_TYPE,
4 BYTES / 1024 / 1024 "字节数（M）",
5 TABLESPACE_NAME
6 FROM USER_SEGMENTS
7 WHERE SEGMENT_NAME IN ('NORM_TAB', 'RANGE_PART_TAB');
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE 字节数（M）TABLESPACE_NAME
-------------------- -------------------- -------------------- ----------- -----------------------------
NORM_TAB TABLE 47 TBS_WMJ
RANGE_PART_TAB P1 TABLE PARTITION .0625 TBS_WMJ
RANGE_PART_TAB P10 TABLE PARTITION 5 TBS_WMJ
RANGE_PART_TAB P11 TABLE PARTITION 4 TBS_WMJ
RANGE_PART_TAB P12 TABLE PARTITION 5 TBS_WMJ
RANGE_PART_TAB P2 TABLE PARTITION .0625 TBS_WMJ
RANGE_PART_TAB P3 TABLE PARTITION .0625 TBS_WMJ
RANGE_PART_TAB P4 TABLE PARTITION .0625 TBS_WMJ
RANGE_PART_TAB P5 TABLE PARTITION .0625 TBS_WMJ
RANGE_PART_TAB P6 TABLE PARTITION .1875 TBS_WMJ
RANGE_PART_TAB P7 TABLE PARTITION 5 TBS_WMJ
RANGE_PART_TAB P8 TABLE PARTITION 4 TBS_WMJ
RANGE_PART_TAB P9 TABLE PARTITION 4 TBS_WMJ
RANGE_PART_TAB P_MAX TABLE PARTITION 23 TBS_WMJ
已选择14行。
SQL>
惊叹！发现了分区表会产生多个segment，而普通表只有一个segment。这也说明了分区表将达大表分成许多个小表，将数据分别存放在不同的segment中，从而使在指定的小对象定位到数据成为一种可能。另外哈希分区，组合分区，列表分区也是如此。
那划分成多个小表就能提高性能吗，我们看一个查询，分别对普通表和读分区表进行试验，查看执行计划。
SQL> SET linesize 1000;
SQL> SET autotrace traceonly;
SQL> SET timing ON;
SQL> SELECT *
2 FROM RANGE_PART_TAB T
3 WHERE T.DEAL_DATE >= TO_DATE('2018-09-04', 'YYYY-MM-DD')
4 AND T.DEAL_DATE <= TO_DATE('2018-09-07', 'YYYY-MM-DD');
已选择1131行。
已用时间: 00: 00: 00.06
执行计划
----------------------------------------------------------
Plan hash value: 16125146
--------------------------------------------------------------------------------
-------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
-------------------------
| 0 | SELECT STATEMENT | | 1047 | 2082K| 137 (1)| 00:00:02 | | |
| 1 | PARTITION RANGE SINGLE| | 1047 | 2082K| 137 (1)| 00:00:02 | 9 | 9 |
|* 2 | TABLE ACCESS FULL | RANGE_PART_TAB | 1047 | 2082K| 137 (1)| 00:00:02 | 9 | 9 |
--------------------------------------------------------------------------------
-------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."DEAL_DATE">=TO_DATE(' 2018-09-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"T"."DEAL_DATE"<=TO_DATE(' 2018-09-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
636 consistent gets
0 physical reads
0 redo size
37530 bytes sent via SQL*Net to client
1344 bytes received via SQL*Net from client
77 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1131 rows processed
SQL>
接下来查看普通表
SQL> SELECT *
2 FROM NORM_TAB T
3 WHERE T.DEAL_DATE >= TO_DATE('2018-09-04', 'YYYY-MM-DD')
4 AND T.DEAL_DATE <= TO_DATE('2018-09-07', 'YYYY-MM-DD');
已选择1115行。
已用时间: 00: 00: 00.10
执行计划
----------------------------------------------------------
Plan hash value: 278673677
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1220 | 2426K| 1608 (1)| 00:00:20 |
|* 1 | TABLE ACCESS FULL| NORM_TAB | 1220 | 2426K| 1608 (1)| 00:00:20 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."DEAL_DATE">=TO_DATE(' 2018-09-04 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "T"."DEAL_DATE"<=TO_DATE(' 2018-09-07
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
6075 consistent gets
0 physical reads
0 redo size
36858 bytes sent via SQL*Net to client
1333 bytes received via SQL*Net from client
76 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1115 rows processed
SQL>
通过分析，发现同样的记录，同样的SQL，分区表的代价为137，普通表却1608。简直天壤之别，性能方面分区表也比较出色。至于为什么会这样，在前面分析，分区表有多个segment，而分区表查询中根据分区条件却只要遍历了其中一个或几个segment就可以，从而避开了其他分区的查询，效率上是不是比普通表遍历一整个segment，然后从这个segment中找到我们的数据要快的多，这也叫做分区表的重要特性之一，分区消除。除此之外还有分区清理（trancate），分区转移（exchange…with table…），分区切割（split …at …into …），分区合并（merge…into…），分区增删（add/drop partition …），有兴趣可以了解一下。
当然也不是建了分区表就会用到分区消除，还要看查询SQL找那个要用到分区条件，比如刚刚脚本中用到过滤条件为deal_date ，才会用到分区消除，若我们不适用deal_date过滤，这样查询时跟查询普通表没有区别，甚至比普通表性能更低，这点要注意。
2.4 索引组织表
在前面2.13小节通过普通表的查询提到过回表查询（TABLE ACCESS BY INDEX ROWID），在一些查询中我们不希望查询到所有字段（这也是为什么SQL不建议写select * 的原因）而影响到SQL的性能，但是有些情况下有不得不避免回表查询，这时索引组织表就发挥作用了。
下面分别创建普通表和索引组织表查看结果：
SQL> CREATE TABLE heap_adress
2 (
3 empno NUMBER(10),
4 addr_type VARCHAR2(10),
5 street VARCHAR2(10),
6 city VARCHAR2(10),
7 stae VARCHAR2(10),
8 zip NUMBER,
9 PRIMARY KEY(empno)
10 );
表已创建。
SQL> CREATE TABLE iot_adress
2 (
3 empno NUMBER(10),
4 addr_type VARCHAR2(10),
5 street VARCHAR2(10),
6 city VARCHAR2(10),
7 stae VARCHAR2(10),
8 zip NUMBER,
9 PRIMARY KEY(empno)
10 )
11 ORGANIZATION INDEX;
表已创建。
SQL> INSERT INTO heap_adress
2 SELECT t.OBJECT_ID,'WORK','123street','WMJ','DC',2019 FROM all_objects t ;
已创建71547行。
SQL> INSERT INTO iot_adress
2 SELECT t.OBJECT_ID,'WORK','123street','WMJ','DC',2019 FROM all_objects t ;
已创建71547行。
SQL> commit;
提交完成。
然后分别对两个表进行查询，查看性能的比较：
SQL> SET linesize 1000;
SQL> SET autotrace traceonly;
SQL>
SQL> SELECT* FROM heap_adress WHERE empno=22;
执行计划
----------------------------------------------------------
Plan hash value: 2058416298
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 54 | 1 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HEAP_ADRESS | 1 | 54 | 1 (0) | 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0011279 | 1 | | 1 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=22)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
754 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT* FROM iot_adress WHERE empno=22;
执行计划
----------------------------------------------------------
Plan hash value: 426590870
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 54 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_74800 | 1 | 54 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPNO"=22)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
886 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
SQL>
结果发现普通表读取主键索引后会产生回表查询TABLE ACCESS BY INDEX ROWID，而索引组织表不会产生回表查询，从让oracle少了一步操作的过程中， 这也说明了索引组织表性能更高。其实索引组织表最大的特点就是，表就是索引，索引就是表，这也是一种特别的设计。但是对于索引，我们知道，在表进行更新删除的时候，会对索引进行维护，维护的过程是个比较耗费CUP性能的过程，这也间接的反应了索引组织表的缺点。所以呢，综合来看，索引组织表更适用于表在少量更新，读取更为频繁的场合下操作，这也充分说明了，在合适的场合下选择合适的技术。
2.5 簇表
关于簇表用的不多，但也有使用场景，比如 簇表可以避免排序操作。因为排序是比较消耗性能的。但是同样。由于 簇表特殊的表结构，也导致 簇表在更新操作的时候开销异常大，也需要谨慎。这里就不提了
综合来看，光表就分这么多类，对于业务场景来讲，在哪个业务下用哪个技术，是没有定论的，也不能说某个技术一定比你的好，只能说这个业务下某个技术比较适合它。
3、索引
还是先给出结论，索引具有的特性
1. 高度较低
2. 存储列值
3. 结构有序
3.1 索引结构
经过前面的体系结构，我们已经知道新建一张表T，就会产生一个T表的segment，其实索引页一样，在T表的列上新建一个索引IDX_T，就会产生一个索引的segment，索引的存在可以使得查询的效率得到提升，速度更快，那究竟是如何提升的呢，我们先看一下索引的整体结构。
通过图可以看到，索引是由Root（根块），Branck（茎块）和Leaf（叶子块）三部分组成。其中叶子块主要存储了key column value （索引列具体值）以及能具体定位数据块的位置的rowid（注意区分数据块和索引块，索引块也是占用磁盘空间的）。
那么有了索引，Oracle是怎么根据索引查询来提高速度呢，下面用查询为例：select * from test where id = 12;
假如该test表共有10050条数据，而id=12仅返回一条数据，test表id字段建了一个索引，检索数据如下图：
通过图片理解，该sql语句大致只要3个IO，此处是个例子，实际情况1w多数据量，索引高度应该只有2层，即2个IO左右。
首先查询定位到索引根部，接下来根据根块数据分布，定位到茎部数据，然后定位到叶子块。
有了索引，oracle只会去扫描部分索引块，而非全部，所以会提高性能。由于是select * 语句，不止只要id这个字段，所以不止要扫描索引块，还要根据 这个id=12的索引中的rowid，去回表扫描其他字段，所以要加上一次IO。
3.2 索引高度和结构的理解
实际上，索引并不是真的就是根茎叶3层，索引高度是根据数据量来体现的。还是以test表为例，当我们在对test表建索引 create index idx_id on test(id);执行这个动作到完毕，到底发生了什么，下面用图来解释一下。
1）建索引后，首先从test表的id列的值顺序取出来放在内存中，每个值对应的rowid也被一并取出。
2）依次将内存中顺序存放的列的值和对应的rowid存进oracle空闲的BLOCK中，形成了索引块，步骤如下图：
3）随着索引列值不断插入，索引块index block1很快装满了，比如接下来取出的id=9就无法插入到index block1中，只能插入到新的块中index block2，如下图；与此同时需要新写数据到另一个块index block3，这时为啥？原来块1和块2平起平坐，谁都不服，这时需要块3 index block3来负担管理角色，这个块中就记录了index block1和index block2的信息，并记录具体索引的键值。
4）随着叶子块的不远增加，块3也满了，那咋办，接着装呗，这样就要寻找一个新的块区装块3…如图：
最后知道索引完全建立完成，这样索引的建立过程也就清楚了。我们来做个总结
- 要建索引先排序 ，所以索引其实是有序的
- 列值入块成索引，索引块就是这么来的
- 填满一块接一块，数据量增大在索引的体现，就是叶子块横向扩展
- 同级两块需人管，叶子块有2个或以上时候，就需要有一个老大来管理多个叶子块，这个老大块里放的是没个叶子块的指针，所以索引的高度不容易变得特别大也是这个道理
综上，我们也应征了开头的结论：
1、索引高度较低；
2、索引由索引列值及rowid组成；
3、索引本身有序
3.3 索引与优化
建立索引，无非是想优化SQL体改了效率，究竟如何优化呢？
3.3.1 聚合函数优化
select count(*) from t 这样的SQL大家都会写，但是能走索引吗？能优化吗，下面还是用代码加上执行计划验证。
SQL> drop table t PURGE;
表已删除。
SQL> CREATE TABLE t AS SELECT * FROM Dba_Objects;
表已创建。
SQL> CREATE INDEX idx1_object_id ON t(object_id);
索引已创建。
SQL> SET linesize 1000;
SQL> SET autotrac ON;
SQL> SET timing ON;
SQL> SELECT COUNT(*) FROM t;
COUNT(*)
----------
72536
已用时间: 00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 290 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 71995 | 290 (1)| 00:00:04 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
1108 consistent gets
0 physical reads
0 redo size
529 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
SQL>
啊啊哦！！？发现没有走索引，结果走全表扫描，为什么呢，想想索引结构，原来索引不能存储空的记录，如果索引列有空值，肯定用不到索引，那么…再来
SQL> SELECT COUNT(*) FROM t WHERE t.object_id IS NOT NULL;
COUNT(*)
----------
72536
已用时间: 00: 00: 00.27
执行计划
----------------------------------------------------------
Plan hash value: 1296839119
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 50 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FAST FULL SCAN| IDX1_OBJECT_ID | 71995 | 913K| 50 (2)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."OBJECT_ID" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
237 consistent gets
161 physical reads
0 redo size
529 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
SQL>
验证正确！用到了索引，我们对比一下，没有走索引情况下逻辑读 1108 consistent gets，IO耗费290 ，走索引的话，逻辑读 237 consistent gets，IO仅为50，性能大大提升！！想不到简单的一个索引让性能得到如此提升特别是在大数据量的情况下效果更是明显。
同理，对于聚合函数SUM/AVG、MIN/MAX也是如此。
3.3.2 排序优化
前面提到过，排序是比较消耗性能的操作，但是索引又有一个特点，索引本身有序，那么我们思考在排序上怎么利用索引区优化呢，还是通SQL+执行计划验证查看。
先来一个普通的查询，不带排序，看下性能：
SQL> SET linesize 1000;
SQL> SET autotrace traceonly;
SQL> drop table t PURGE;
表已删除。
SQL> CREATE TABLE t AS SELECT * FROM Dba_Objects;
表已创建。
SQL> SET autotrace traceonly;
SQL> SELECT * FROM t WHERE t.object_id >2;
已选择72535行。
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71995 | 14M| 292 (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 71995 | 14M| 292 (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_ID">2)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
328 recursive calls
0 db block gets
5900 consistent gets
1033 physical reads
0 redo size
3757990 bytes sent via SQL*Net to client
53704 bytes received via SQL*Net from client
4837 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72535 rows processed
接下来在执行一个类似的语句，区别是增加了一个order by 排序：
SQL> SELECT * FROM t WHERE t.object_id >2 ORDER BY t.object_id;
已选择72535行。
执行计划
----------------------------------------------------------
Plan hash value: 961378228
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71995 | 14M| | 3536 (1)| 00:00:43 |
| 1 | SORT ORDER BY | | 71995 | 14M| 17M| 3536 (1)| 00:00:43 |
|* 2 | TABLE ACCESS FULL| T | 71995 | 14M| | 292 (2)| 00:00:04 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."OBJECT_ID">2)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
1105 consistent gets
0 physical reads
0 redo size
3755863 bytes sent via SQL*Net to client
53704 bytes received via SQL*Net from client
4837 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
72535 rows processed
SQL>
通过对比,发现没有排序的SQL中COST 是292 ，时间消耗1秒，而排序过后COST是3536，时间消耗43秒，并且确实有排序动作产生，通过1 sorts (memory) 可以看见，由此可见排序对SQL性能的影响。那么有没有办法消除排序呢，索引？对，就是索引，因为索引本身就是有序的，我们排序字段是object_id，则可以考虑在排序列上添加索引，来消除排序，准备实验脚本：
SQL> CREATE INDEX idx_object_id ON t(object_id);
索引已创建。
SQL> SET linesize 1000;
SQL> SET autotrace traceonly;
SQL> SELECT * FROM t WHERE t.object_id >2 ORDER BY t.object_id;
已选择72535行。
执行计划
----------------------------------------------------------
Plan hash value: 2041828949
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71995 | 14M| 1282 (1)| 00:00:16 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 71995 | 14M| 1282 (1)| 00:00:16 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 71995 | | 176 (1)| 00:00:03 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_ID">2)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
10933 consistent gets
163 physical reads
0 redo size
8316299 bytes sent via SQL*Net to client
53704 bytes received via SQL*Net from client
4837 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72535 rows processed
SQL>
通过添加索引，我们发现排序居然消除了 0 sorts (memory)，COST也变成1282 ，时间上从原先43秒，变成16秒，效率是大大提升！！！
既然Order By排序可以通过索引消除，那么像常见的 DISTINCT、UNION，就也可以通过索引来消除，因为DISTINCT实际上也是有排序的，优化方法同样在去重列上加上索引就好了，至于验证就不在验证了。
而UNION 我们知道是对查询结果取并集并去重复，去重过程中也会产生排序，在我们业务允许的情况下，优化UNION的方法居然是不让去重，这样就避免排序，产生不必要的IO消耗，优化只要将UNION 改写成UNION ALL就好~！
3.3.3 组合索引
上面的情况都是单个列建立的索引，那如果一个索引由多个列组成的，这种又叫组合索引，比如查询select a,b from t where a=1 and b=2;要在a，b列上建索引。那么问题来了，究竟两个列谁在前面更合适呢？不清楚，还是通过脚本来证明，准备脚本如下，分别用HINT来固定执行计划，让两个语句分别走两个不同的索引；
SELECT /*+index(t,idx1_object_id)*/ FROM t WHERE t.object_id=20AND t.object_type='TABLE';
SELECT /*+index(t,idx2_object_id)*/ FROM t WHERE t.object_id=20AND t.object_type='TABLE';
先构造t表，然后分别建立索引：
SQL> drop table t PURGE;
表已删除。
SQL> CREATE TABLE t AS SELECT * FROM Dba_Objects;
表已创建。
SQL> CREATE INDEX idx1_object_id ON t(object_id,object_type);
索引已创建。
SQL> CREATE INDEX idx2_object_id ON t(object_type,object_id);
索引已创建。
分别查看idx1_object_id和idx2_object_id两个索引下SQL的执行计划
SQL> SET linesize 1000;
SQL> SET autotrace traceonly;
--查看idx1_object_id的执行计划
SQL> SELECT /*+index(t,idx1_object_id)*/* FROM t WHERE t.object_id=20AND t.object_type='TABLE';
执行计划
----------------------------------------------------------
Plan hash value: 2486998213
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1449 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 7 | 1449 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX1_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_ID"=20 AND "T"."OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
288 recursive calls
0 db block gets
101 consistent gets
0 physical reads
0 redo size
1609 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
--查看idx2_object_id的执行计划
SQL> SELECT /*+index(t,idx2_object_id)*/* FROM t WHERE t.object_id=20AND t.object_type='TABLE';
执行计划
----------------------------------------------------------
Plan hash value: 1913591113
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1449 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 7 | 1449 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX2_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_TYPE"='TABLE' AND "T"."OBJECT_ID"=20)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
73 consistent gets
1 physical reads
0 redo size
1609 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
SQL>
对比发现idx1_object_id和idx2_object_id的COST 和物理读都一样，索引列在前在后没有影响。这里给先出一个结论：在等值查询下，组合索引无论哪个列在前性能都一样。
那么非等值查询呢，例如：select * from t where t.object_id>=20 and t.object_id<=2000 and t.object_type=‘TABLE’;这里也给出一个结论：组合索引两列，当一列是范围查询，一列是等值查询，等值查询列在前，范围查询列在后，这样的索引才是高效的。 在本例中idx2_object_id索引是高效，验证方法与上面类似，如果有兴趣可以验证一下。
3.3.4 索引缺陷
上面都是建立索引的好处，任何事物都有两面性，那所有有没有危害呢？索引也是如此，索引可以让查询变得更便捷，但是却会影响更新的性能。
因为在对数据库更新删除操作的时候，索引会带来额外的开销，具体在对下面三种更新语句上体现：
- 对于insert语句负面影响最大，有百害而无一利，只要有索引，插入越慢，索引越多越慢。
- 对delete语句，有好有坏，在海量数据中定位删除少量数据时，这个条件列时索引列显然是必要的，但是过多列有索引，还是会影响明显，因为其他列的索引也因此要被更新。在经常要大批量删除记录的时候，危害大于利！
- 对于update语句，负面影响最小。快速定位少量数据与delete类似，但是具体修改某列的时候却有差别，不会触及其他索引列的维护。
- 建立索引还会额外产生锁表。
以上索引的危害，前3条容易理解，第四条解释一下：因为建立索引是需要把索引列的值都取出来，然后排序后依次插入索引块的，如果此时有个语句把索引列值更新了，那岂不是影响了索引的工作，与排序后的索引列有形成冲突。所以在建立索引的同时，会加上锁，加锁的目的是为了避免此时列值被更新导致顺序有变化了。。鉴于这点，所以在日常操作中，特别是在生产中，一定不要随便乱建立索引。
3.3.5 索引监控
既然索引也有危害，看来索引也不是越多越好，那么怎么知道我建立了一个索引平时操作中有没有用到呢、实际上Oracle也提供了这种方案，索引监控。脚本如下：
--对需要跟踪的索引进行监控
ALTER INDEX 索引名 MONITORING USAGE;
--通过观察 v$object_usage进行跟踪
SELECT * FROM v$object_usage;
--取消对索引的监控
ALTER INDEX 索引名 NOMONITORING USAGE;
下面实验一下：
SQL> drop table t PURGE;
表已删除。
SQL> CREATE TABLE t AS SELECT * FROM Dba_Objects;
表已创建。
SQL> CREATE INDEX idx1_object_id ON t(object_id);
索引已创建。
SQL> CREATE INDEX idx1_object_name ON t(object_name);
索引已创建。
--没有监控索引时v$object_usage查询不到任何信息
SQL> SELECT * FROM v$object_usage;
未选定行
SQL>
然后对两列索引进行监控，观察v$object_usage;：
SQL> ALTER INDEX idx1_object_id MONITORING USAGE;
索引已更改。
SQL> ALTER INDEX idx1_object_name MONITORING USAGE;
索引已更改。
SQL> SELECT * FROM v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
---------------------- ---------------- --- ---------------------- -------------------
IDX1_OBJECT_ID T YES NO 07/01/2019 22:58:45
IDX1_OBJECT_NAME T YES NO 07/01/2019 22:58:51
SQL>
其中USE=NO表示从来没有用过索引，接下来，我们使用一下索引，观察v$object_usage的情况。
SQL> SELECT t.object_id FROM t WHERE t.object_id=19;
OBJECT_ID
----------
19
SQL> SELECT * FROM v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
IDX1_OBJECT_ID T YES YES 07/01/2019 22:58:45
IDX1_OBJECT_NAME T YES NO 07/01/2019 22:58:51
SQL>
发现IDX1_OBJECT_ID索引的USE变成YES了，说明被使用过！
那这种观察索引监控有什么用呢，如果你来跟踪某表的部分索引，跟踪了一个月，然后发现 SELECT * FROM v$object_usage where USE='NO’有返回结果，那么说明这些索引就可以删了，因为一个月都没有用过，留着也是浪费空间浪费性能。
实际上，任何技术都有两面性，监控也是有代价的，如果不想对索引进行监控了，只需要把MONITORING 改成NOMONITORING 就好了！
3.4 位图索引
前面介绍的是BTREE索引，也叫树状索引，下面说说位图索引。这里只说说位图索引原理和适用场景。
什么场景下适合建立位图索引：
- 该列大量重复（位图索引列大量重复）
- 该表极少更新
切记只有满足上面两个场景才可以适用位图索引！！
至于为什么有这样的要求，跟索引原理有关：前面分析，BTREE索引存储的是列值，但是位图所以存储的确是比特位，这就决定了位图索引的适用场景的局限性。位图索引原理可以参考：https://www.cnblogs.com/xqzt/p/4457184.html
3.5 函数索引
有时会发现，明明建立了索引，可是oracle并不按照计划走索引，这是为什么。原来建立了索引，用不到索引，跟SQL写法有大大关系。
比如准备如下脚本：
drop table t PURGE;
CREATE TABLE t AS SELECT * FROM Dba_Objects;
CREATE INDEX idx1_object_id ON t(object_id);
CREATE INDEX idx1_object_name ON t(object_name);
SELECT COUNT(*)FROM t;
该查询明显走索引，但是有这样一条SQL呢，SELECT * FROM t WHERE UPPER(t.object_name)=‘T’；看下执行计划：
SQL> SELECT * FROM t WHERE UPPER(t.object_name)='T';
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2484 | 291 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 12 | 2484 | 291 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("T"."OBJECT_NAME")='T')
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1040 consistent gets
0 physical reads
0 redo size
1732 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
没有走索引，表数据大概7W多条，查询一条数据，正适合用索引的情况却不走索引，那把upper函数去掉？
SQL> SELECT * FROM t WHERE t.object_name='T';
执行计划
----------------------------------------------------------
Plan hash value: 4242384558
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 414 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2 | 414 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX1_OBJECT_NAME | 2 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_NAME"='T')
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
11 recursive calls
4 db block gets
89 consistent gets
2 physical reads
528 redo size
1738 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
结果走了索引，看来还是upper有导致无法走索引。那如果我们硬是要使用upper，又想走索引呢！！这时函数索引就来了，可以这样建立索引：
SQL> CREATE INDEX idx1_upper_object_name ON t(upper(object_name));
索引已创建。
执行计划
----------------------------------------------------------
Plan hash value: 33067776
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 817 | 217K| 151 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 817 | 217K| 151 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IDX1_UPPER_OBJECT_NAME | 327 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("OBJECT_NAME")='T')
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
83 consistent gets
2 physical reads
0 redo size
1732 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
发现确实走索引了（INDEX RANGE SCAN | IDX1_UPPER_OBJECT_NAME）。但是在性能上，对比前两个执行计划，可以发现，函数索引介于普通索引和全表扫描之间。
以上我们也总结一下哪些情况下索引会失效
- 索引列参与运算，比如 where object_id-1<=40;
- 索引列使用函数，比如 where substr(object_name,1,4)=‘CLUS’;
- 日期函数，比如 where trunc(created)>=to_date(‘20190901’,‘yyyymmdd’);
虽然有函数索引可以帮助解决这些问题，但我们尽量避免使索引列参与计算，甚至使用其他情况代替计算才是上策！
4、多表连接
表连接指多张数据表在进行关联查询时，通过某一关系进行关联。在Oracle中，多张表之间的连接方式有三种方式：第一种方式是表连接种的循环嵌套连接（Nested Loops Join），第二种是排序合并连接（Merge Sort Join），三是哈希连接（Hash Join）。有三种方式，那么就肯定有各自的区别，下面通过脚本来说明下不同见解方式有何差异。
还是通过执行计划来研究，前面频繁使用下面语句来跟踪执行计划，来说下其功能
-- 这个语句的含义是不显示语句的返回结果，只进行跟踪。
SET AUTOTRACE TRACEONLY;
--有时候SQL执行返回大量结果,屏幕将不停的滚动，而下面语句有关闭显示的功能：
SET AUTOTRACE OFF;
下面将使用另外一个跟踪性能的工具，这个语句有个特点是，可以查看到被表访问的次数。脚本如下：
SET LINESIZE 1000;
ALTER SESSION SET STATISTICS_LEVEL =ALL;
-- 执行你要跟踪的SQL
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'runstast_last'));
以上是准备工作，下面我们研究下表连接的三种方式的区别。
既然是多表连接，肯定有多个表，我们先简单来构造两张测试表，脚本如下：
--准备两张表
CREATE TABLE t1
(
ID NUMBER NOT NULL,
n NUMBER,
CONTENTS VARCHAR(4000)
);
CREATE TABLE t2
(
ID NUMBER NOT NULL,
t1_id NUMBER NOT NULL,
n NUMBER,
CONTENTS VARCHAR(4000)
);
--在两张表中随机插入数据，其中t1表100条，t2表10万条
EXECUTE dbms_random.seed(0);
INSERT INTO T1
SELECT ROWNUM, ROWNUM, DBMS_RANDOM.STRING('a', 50)
FROM DUAL
CONNECT BY LEVEL <= 100
ORDER BY DBMS_RANDOM.RANDOM;
INSERT INTO T2
SELECT ROWNUM, ROWNUM, ROWNUM, DBMS_RANDOM.STRING('b', 50)
FROM DUAL
CONNECT BY LEVEL <= 100000
ORDER BY DBMS_RANDOM.RANDOM;
COMMIT;
--登陆SqlPlus查看下结果
SQL> SELECT COUNT(*) FROM t1;
COUNT(*)
----------
100
SQL> SELECT COUNT(*) FROM t2;
COUNT(*)
----------
100000
4.1 嵌套循环连接
表准备工作好后，先看下循环嵌套连接（NL JOIN）,用 SET STATISTICS_LEVEL =ALL的方式观察一下
SQL> SET LINESIZE 1000;
SQL> ALTER SESSION SET STATISTICS_LEVEL =ALL;
会话已更改。
SQL> SELECT /*+ LEADING(T1) USE_NL(T2)*/ * FROM T1,T2 WHERE T1.ID=T2.T1_ID;
--省略结果
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID 1yrdkns5rsyba, child number 0
-------------------------------------
SELECT /*+ LEADING(T1) USE_NL(T2)*/ * FROM T1,T2 WHERE T1.ID=T2.T1_ID
Plan hash value: 1967407726
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.38 |100K|
| 1 | NESTED LOOPS | | 1 | 100 | 100 |00:00:00.38 |100K|
| 2 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 14 |
|* 3 | TABLE ACCESS FULL| T2 | 100 | 1 | 100 |00:00:00.38 |100K|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."ID"="T2"."T1_ID")
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
- dynamic sampling used for this statement (level=2)
已选择24行。
以上执行计划中 Starts 这一列代表的含义是访问次数，从结果可以看出T1表访问1次，T2表被访问了100次。
继续看另一个SQL ：
SELECT /*+ LEADING(T1) USE_NL(T2)*/ * FROM T1,T2 WHERE T1.ID=T2.T1_ID and t1.n in(17,19);
执行这个SQL后再用刚才的方法看下执行计划，结果如下
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID 45faushn4tbgh, child number 0
-------------------------------------
SELECT /*+ LEADING(T1) USE_NL(T2)*/ * FROM T1,T2 WHERE T1.ID=T2.T1_ID and t1.n in(17,19)
Plan hash value: 1967407726
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 2019 |
| 1 | NESTED LOOPS | | 1 | 2 | 2 |00:00:00.01 | 2019 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 2 | 2 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T2 | 2 | 1 | 2 |00:00:00.01 | 2011 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("T1"."N"=17 OR "T1"."N"=19))
3 - filter("T1"."ID"="T2"."T1_ID")
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
已选择26行。
SQL>
结果T1 表还是访问一次，T2访问2次（Starts =2）；仅仅加了一个条件 and t1.n in(17,19) ，那若条件在变一下，sql改成,SELECT /*+ LEADING(T1) USE_NL(T2)*/ * FROM T1,T2 WHERE T1.ID=T2.T1_ID and t1.n =19呢，试验下。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID 7u7wvc0u6g5cz, child number 0
-------------------------------------
SELECT /*+ LEADING(T1) USE_NL(T2)*/ * FROM T1,T2 WHERE T1.ID=T2.T1_ID and t1.n=19
Plan hash value: 1967407726
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1014 |
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 1014 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 1 | 1 |00:00:00.01 | 1006 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"="T2"."T1_ID")
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
已选择26行。
SQL>
呵呵，这下T1访问一次，T2访问一次了，那再改SQL：
SELECT /*+ LEADING(T1) USE_NL(T2)*/ * FROM T1,T2 WHERE T1.ID=T2.T1_ID and t1.n=9999999
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID c82jw210a2vzk, child number 0
-------------------------------------
SELECT /*+ LEADING(T1) USE_NL(T2)*/ * FROM T1,T2 WHERE T1.ID=T2.T1_ID and t1.n=9999999
Plan hash value: 1967407726
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 7 |
| 1 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 7 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 0 |00:00:00.01 | 7 |
|* 3 | TABLE ACCESS FULL| T2 | 0 | 1 | 0 |00:00:00.01 | 0 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=9999999)
3 - filter("T1"."ID"="T2"."T1_ID")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
已选择26行。
SQL>
这下，T2表访问0次了，不知道刚刚实验了几个SQL，大家是什么感觉的哈，会不会跟查询的结果数有关？我们带上刚刚的条件看下T1表记录
SQL> SELECT COUNT(*) FROM t1;
COUNT(*)
----------
100
SQL> SELECT COUNT(*) FROM t1 WHERE t1.n IN(17,19);
COUNT(*)
----------
2
SQL> SELECT COUNT(*) FROM t1 WHERE t1.n=19;
COUNT(*)
----------
1
SQL> SELECT COUNT(*) FROM t1 WHERE t1.n=9999999;
COUNT(*)
----------
0
结果很明确，确实是跟表的记录数有关，大致上可以总结一下，在嵌套循环连接中，驱动表（先访问的表）返回多少条记录，被驱动表就访问多少次。这里注意SQL中 /*+ LEADING(T1) USE_NL(T2)*/这样的写法是加HINT,使用HINT固定住执行计划，LEADING(T1) 表示先访问T1表，也就是驱动表，USE_NL表示强制使用嵌套循环的连接方式
4.2 哈希连接
关于测试的方法，跟循环嵌套链接相同，我们直接进入代码
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3tfuz5cyh1mpc, child number 0
-------------------------------------
SELECT /*+ LEADING(T1) USE_HASH(T2)*/ * FROM T1,T2 WHERE T1.ID=T2.T1_ID
Plan hash value: 1838229974
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.03 | 1018 | | | |
|* 1 | HASH JOIN | | 1 | 100 | 100 |00:00:00.03 | 1018 | 821K| 821K| 1252K (0)|
| 2 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 1011 | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T2"."T1_ID")
已选择20行。
发现T1表和T2表都访问一次，这里先给出结论，在HASH连接中驱动表额被驱动表都只会访问0次或者1次，关于0次的验证，大家可以验证一下，把SQL中的条件改成T1.n=9999或者其他条件让查询没有返回即可。
4.3 排序合并
同理我们可以用同样的方法来看看排序合并连接的访问次数
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9jfjuj926c75g, child number 0
-------------------------------------
SELECT /*+ ordered USE_MERGE(T2)*/ * FROM T1,T2 WHERE T1.ID=T2.T1_ID
Plan hash value: 412793182
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.05 |1012 | | | |
| 1 | MERGE JOIN | | 1 | 100 | 100 |00:00:00.05 |1012 | | | |
| 2 | SORT JOIN | | 1 | 100 | 100 |00:00:00.01 | 7 | 13312 | 13312 |12288 (0)|
| 3 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | |
|* 4 | SORT JOIN | | 100 | 100K| 100 |00:00:00.05 |1005 | 9762K| 1209K| 8677K (0)|
| 5 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 |1005 | | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
已选择23行。
这里也可以看出来，在访问次数上，排序合并连接和哈希连接是一样的，T1表和T2表都只访问一次或者0次。但有点需要说明的是，排序合并连接没有驱动和被驱动的概念，而循环嵌套连接额哈希连接有分驱动表和被驱动表，并且小表作为驱动表性能更高！
由此可见驱动表的顺序也会影响Oracle的查询性能。
4.3 各个连接排序情况分析
从前面的执行计划可以看出，除了嵌套循环不需要排序外，哈希连接和排序合并连接都需要排序。（Used-Mem代表有排序）。
4.3 各个连接限制情况分析
虽然各个连接方式有各自好处，但是在哪些情况下能用，哪些情况下不能用，又有什么限制呢，下面分析
哈希连接
SELECT /+ LEADING(T1) USE_HASH(T2)/ * FROM T1,T2 WHERE T1.ID=T2.T1_ID AND T1.N=19;这个SQL前面分析过，肯定是走哈希连接的，不过现在我换一种写法将连接条件改成不等连接<>看看
SQL> EXPLAIN PLAN FOR SELECT /*+ LEADING(T1) USE_HASH(T2)*/ * FROM T1,T2 WHERE T 1.ID<>T2.T1_ID AND T1.N=19;
已解释。
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1967407726
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99999 | 11M| 277 (1)| 00:00:04 |
| 1 | NESTED LOOPS | | 99999 | 11M| 277 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 57 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 99999 | 6445K| 274 (1)| 00:00:04 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"<>"T2"."T1_ID")
已选择16行。
发现使用了USE_HASH，但Oracle根本不走哈希连接，而走嵌套循环 NESTED LOOPS，只能说明这里不支持哈希连接。那换成大于或小于呢
SQL> EXPLAIN PLAN FOR SELECT /*+ LEADING(T1) USE_HASH(T2)*/ * FROM T1,T2 WHERE T 1.ID<T2.T1_ID AND T1.N=19;
已解释。
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1967407726
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99950 | 11M| 277 (1)| 00:00:04 |
| 1 | NESTED LOOPS | | 99950 | 11M| 277 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 57 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 99950 | 6442K| 274 (1)| 00:00:04 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"<"T2"."T1_ID")
已选择16行。
SQL>
实验发现使用大于或小于 ，Oracle也不支持走哈希连接。实际上，对于like ，Oracle也是不支持哈希连接的，这里就不实验了。
对于排序合并连接和嵌套循环连接，也可以通过类似的SQL查看，这里就贴代码了，有兴趣可以自己试验看一下结果。这里直接给出结论：排序合并连接不支持<>的连接条件，也不支持like，HASH连接不支持<>，也不支持 > 和 < 跟like。循环嵌套连接都支持
可以参考思维导图：
(完)
扫一扫
367
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
