22.读书笔记收获不止Oracle之 索引特性活用
来源：互联网 发布：ios开源app源码 编辑：程序博客网 时间：2024/04/16 17:42
22.读书笔记收获不止Oracle之 索引特性活用
以实际操作为例：
看看索引的特性灵活使用：
SQL> set autotrace traceonly
SQL> set linesize 1000
SQL> drop table t purge;
SQL> create table t as select * fromdba_objects;
SQL> set autotrace traceonly;
SQL> select * from t where object_id>2;
91720 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 91720 | 10M| 430 (1)| 00:00:01 |
|* 1| TABLE ACCESS FULL| T | 91720 | 10M| 430 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- filter("OBJECT_ID">2)
Statistics
----------------------------------------------------------
2 recursive calls
0 dbblock gets
7545 consistent gets
1539 physical reads
0 redosize
5005356 bytes sent via SQL*Net toclient
67805 bytes received via SQL*Netfrom client
6116 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
91720 rows processed
在执行一个类似的语句，增加ORDER BY关键字
SQL> select * from t where object_id>2 order by object_id;
91720 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 961378228
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0| SELECT STATEMENT | |91720 | 10M| | 2808 (1)| 00:00:01 |
| 1| SORT ORDER BY | | 91720 | 10M| 13M| 2808 (1)| 00:00:01 |
|* 2| TABLE ACCESS FULL| T |91720 | 10M| | 430 (1)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
2- filter("OBJECT_ID">2)
Statistics
----------------------------------------------------------
1 recursive calls
0 dbblock gets
1542 consistent gets
1539 physical reads
0 redosize
4852173 bytes sent via SQL*Net toclient
67805 bytes received via SQL*Netfrom client
6116 SQL*Net roundtrips to/fromclient
1 sorts (memory)
0 sorts (disk)
91720 rows processed
发现逻辑读从7545下降到1542.有排序的逻辑读更低。但是排序的COST成本更高。真正决定性能是COST的高低和真实完成的时间。
逻辑读是作为参考，在大部分情况下，逻辑读越少性能越快。
1. 通过索引消除排序
如果排序列是索引列，那么可以消除索引，给刚才表增加索引，并重新执行如下：
Create index idx_t_object_id on t(object_id);
Set autotrace traceonly;
SQL>select * from t where object_id>2 order byobject_id;
91720 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4285561625
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 91720 | 10M| 2732 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 91720 | 10M| 2732 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN |IDX_T_OBJECT_ID | 91720 | | 204 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
2- access("OBJECT_ID">2)
Statistics
----------------------------------------------------------
1 recursive calls
0 dbblock gets
14795 consistent gets
1745 physical reads
0 redosize
4852173 bytes sent via SQL*Net toclient
67805 bytes received via SQL*Netfrom client
6116 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
91720 rows processed
发下你没有排序了在执行计划中，因为索引已经进行排序过了。不过有没有发现逻辑读却多出了很多？
这是因为索引范围扫描一次只能读取一个块，要从索引中通过回表获取其他列的信息，需要读取的块就更多了。
虽然走索引消除了排序，但是增加了大量的逻辑读14795。这个需要优化器来综合判断COST大小.
2. 缩小返回列
不返回所有字段,只返回做过索引的字段.
执行如下:
SQL>select object_id from t where object_id>2 order byobject_id;
91720 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2498590897
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 91720 | 447K| 204 (0)| 00:00:01 |
|* 1| INDEX RANGE SCAN| IDX_T_OBJECT_ID |91720 | 447K| 204 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- access("OBJECT_ID">2)
Statistics
----------------------------------------------------------
1 recursive calls
0 dbblock gets
6305 consistent gets
0 physical reads
0 redosize
1726112 bytes sent via SQL*Net toclient
67805 bytes received via SQL*Netfrom client
6116 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
91720 rows processed
这样就避免了回表，在实际中要尽可能的避免回表机会，不要犯了业务允许只取一列而你却取了全部列遮掩的错误。
3. DISTINCT排重优化
DISTINCT是排除重复记录的大写。
示例如下：
SQL> drop table t purge;
SQL> create table t as select * from dba_objects;
SQL> alter table t modify object_id not null;
SQL> update t set object_id=2;
91721 rows updated.
SQL> update t set object_id=3 where rownum<=25000;
25000 rows updated.
SQL> commit;
进行查看
Set autotrace traceonly
Select distinct object_id from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1793979440
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
---
| 0| SELECT STATEMENT | |91721 | 447K| | 712 (1)| 00:00:0
1 |
| 1| HASH UNIQUE | | 91721 | 447K| 1088K| 712 (1)| 00:00:0
1 |
| 2| TABLE ACCESS FULL| T |91721 | 447K| | 429 (1)| 00:00:0
1 |
--------------------------------------------------------------------------------
---
Statistics
----------------------------------------------------------
42 recursive calls
0 dbblock gets
1589 consistent gets
1543 physical reads
0 redosize
597 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
2 rows processed
3.1 不使用distinct 查看
SQL> select object_id from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 91721 | 447K| 429 (1)| 00:00:01 |
| 1| TABLE ACCESS FULL| T | 91721 | 447K| 429 (1)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 dbblock gets
7560 consistent gets
1539 physical reads
0 redosize
1626969 bytes sent via SQL*Net toclient
67805 bytes received via SQL*Netfrom client
6116 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
91721 rows processed
去掉distinct后，COST降低了。因为DISTINCT是需要排序的。加上DISTINCT是会影响性能的。
3.2 创建索引
SQL>create indexidx_t_object_id on t (object_id);
Select distinct object_id from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 2729247865
--------------------------------------------------------------------------------
-----------------
| Id | Operation | Name |Rows | Bytes |TempSpc| Cost (
%CPU)| Time |
--------------------------------------------------------------------------------
-----------------
| 0| SELECT STATEMENT | | 91721 | 447K| | 334
(1)| 00:00:01 |
| 1| HASH UNIQUE | | 91721 | 447K| 1088K| 334
(1)| 00:00:01 |
| 2| INDEX FAST FULL SCAN| IDX_T_OBJECT_ID | 91721| 447K| | 51
(0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------
Statistics
----------------------------------------------------------
1 recursive calls
0 dbblock gets
188 consistent gets
180 physical reads
0 redosize
597 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rowsprocessed
创建索引，可以消除DISTINCT带来的排序。现实中，靠索引来优化往往收效不明显，因为大多数情况用到DISTINCT都是因为表记录有重复。
4. 索引全扫描与快速全扫
INDEX FULL SCAN 索引全扫描
INDEX FAST FULL SCAN索引快速全扫描
索引快速全扫描一次读取多个索引块，而索引全扫描一次只读一个块。
一次读取多个无法保证有序，排序动作就无法消除。无需排序的操作，一般都走INDEX FAST FULL SCAN，涉及排序语句时，就要开始权衡利弊。有时候宁愿排序无法避免，但是减少逻辑读。
刚才创建索引后执行select distinct object_id from t;走的是索引快速全扫描。
在执行如下
SQL>select object_id from t order by object_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 439494919
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0| SELECT STATEMENT | | 91721 | 447K| 181 (0)| 00:00:
01 |
| 1| INDEX FULL SCAN | IDX_T_OBJECT_ID |91721 | 447K| 181 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Statistics
----------------------------------------------------------
1 recursive calls
0 dbblock gets
6284 consistent gets
0 physical reads
0 redosize
1626969 bytes sent via SQL*Net toclient
67805 bytes received via SQL*Netfrom client
6116 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
91721 rows processed
走的是索引全扫描了。
Index full scan可以消除排序，但是逻辑读比索引快速全扫描要多。
5. UNION合并的优化
UNION合并后没有重复记录，UNION ALL合并后可能有重复记录。
UNION会进行排序。UNION ALL不会进行排序。
SQL> drop table t1 purge;
SQL> create table t1 as select * from dba_objects;
SQL> alter table t1 modify object_id not null;
SQL> drop table t2 purge;
SQL> create table t2 as select * fromdba_objects;
SQL> alter table t2 modify object_id notnull;
SQL> set linesize 1000
SQL> set autotrace traceonly
SQL> select object_id from t1
union select object_id fromt2;
91786 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3008085330
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 183K| 896K| | 1423 (1)| 00:00:01 |
| 1| SORT UNIQUE | | 183K| 896K| 2177K| 1423 (1)| 00:00:01 |
| 2| UNION-ALL | | | | | | |
| 3| TABLE ACCESS FULL| T1 | 91785 | 448K| | 429 (1)| 00:00:01 |
| 4| TABLE ACCESS FULL| T2 | 91784 | 448K| | 429 (1)| 00:00:01 |
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
35 recursive calls
0 dbblock gets
3121 consistent gets
0 physical reads
0 redosize
1727425 bytes sent via SQL*Net toclient
67860 bytes received via SQL*Netfrom client
6121 SQL*Net roundtrips to/fromclient
5 sorts (memory)
0 sorts (disk)
91786 rows processed
进行了排序操作。
5.1 使用索引
创建索引
SQL> create index idx_t1_object_id on t1(object_id);
SQL> create index idx_t2_object_id on t2(object_id);
SQL> select object_id from t1
union
select object_id from t2;
Execution Plan
----------------------------------------------------------
Plan hash value: 669167125
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 183K| 896K| | 680 (1)| 00:00:01 |
| 1| SORT UNIQUE | | 183K| 896K| 2177K| 680 (1)| 00:00:01 |
| 2| UNION-ALL | | | | | | |
| 3| INDEX FAST FULL SCAN|IDX_T1_OBJECT_ID | 91785 | 448K| | 57 (0)| 00:00:01 |
| 4| INDEX FAST FULL SCAN|IDX_T2_OBJECT_ID | 91784 | 448K| | 57 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 dbblock gets
424 consistent gets
408 physical reads
0 redosize
1727425 bytes sent via SQL*Net toclient
67860 bytes received via SQL*Netfrom client
6121 SQL*Net roundtrips to/fromclient
1 sorts (memory)
0 sorts (disk)
91786 rows processed
建索引后ORACEL会走索引快速全扫描，但是排序不可避免。
5.2 强制都索引全扫描
索引全扫描可以避免排序。
SQL> select /*+index(t1)*/ object_id from t1 union
select /*+index(t2)*/object_id from t2;
91786 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 243121257
--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CP
U)| Time |
--------------------------------------------------------------------------------
--------------
| 0| SELECT STATEMENT | | 183K| 896K| | 977 (
1)| 00:00:01 |
| 1| SORT UNIQUE | | 183K| 896K| 2177K| 977 (
1)| 00:00:01 |
| 2| UNION-ALL | | | | |
| |
| 3| INDEX FULL SCAN| IDX_T1_OBJECT_ID |91785 | 448K| | 205 (
0)| 00:00:01 |
| 4| INDEX FULL SCAN| IDX_T2_OBJECT_ID |91784 | 448K| | 205 (
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------
Statistics
----------------------------------------------------------
104 recursive calls
0 dbblock gets
540 consistent gets
414 physical reads
0 redosize
1727425 bytes sent via SQL*Net toclient
67860 bytes received via SQL*Netfrom client
6121 SQL*Net roundtrips to/fromclient
13 sorts (memory)
0 sorts (disk)
91786 rows processed
使用了IDNEX FULL SCAN，但是并没有消除排序。INDEX FULL SCAN不能消除排序。所以ORACLE当然弃用索引全扫描方式。因为是两个不同的结果集的筛选，各自索引无法奏效。
这个场景中索引是无法消除排序的，在一些两个表根本就不可能有重复的场景中个，使用UNION修改位UNION ALL。
6. 主外键设计
主外键有三大特点：主键本身是一种索引；可以保证表中主键所在列的唯一性；可以有效地限制外键依赖的表的记录的完整性。前两个特点和 CREATE UNIQUE INDEX建立的唯一性索引相同。
6.1 外键上的索引与性能
SQL> create table t_p (id number,namevarchar2(30));
SQL> alter table t_p add constraintt_p_id_pk primary key (id);
SQL> create table t_c (id number,fidnumber,name varchar2(30));
SQL> alter table t_c add constraintfk_t_c foreign key (fid) references t_p (id);
SQL> insert into t_p selectrownum,table_name from all_tables;
SQL> insert into t_c selectrownum,mod(rownum,1000)+1,object_name from all_objects;
Commit;
T_p表有主键，T_C表有外键。
执行如下：
Set autotrace traceonly
Set linesize 1000
SQL> select a.id,a.name,b.name from t_p a,t_c b where a.id=b.fidand a.id=880;
89 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 727955870
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 79 | 4740 | 136 (0)| 00:00:01 |
| 1| NESTED LOOPS | | 79 | 4740 | 136 (0)| 00:00:01 |
| 2| TABLE ACCESS BY INDEX ROWID| T_P | 1 | 30 | 0 (0)| 00:00:01 |
|* 3| INDEX UNIQUE SCAN | T_P_ID_PK | 1 | | 0 (0)| 00:00:01 |
|* 4| TABLE ACCESS FULL | T_C | 79 | 2370 | 136 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
3- access("A"."ID"=880)
4- filter("B"."FID"=880)
Note
-----
-dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
6 recursive calls
1 dbblock gets
576 consistent gets
0 physical reads
184 redo size
4822 bytes sent via SQL*Net toclient
606 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
89 rowsprocessed
6.2 建立外键索引观察
SQL> create index ind_t_c_fid ont_c(fid);
SQL> select a.id,a.name,b.name from t_p a,t_c b where a.id=b.fidand a.id=880;
89 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3784248896
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 89| 5340 | 115 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 89| 5340 | 115 (0)| 00:00:01 |
| 2| TABLE ACCESS BY INDEX ROWID | T_P | 1 | 30 | 0 (0)| 00:00:01 |
|* 3| INDEX UNIQUE SCAN | T_P_ID_PK | 1 | | 0 (0)| 00:00:01 |
| 4| TABLE ACCESS BY INDEX ROWID BATCHED|T_C | 89| 2670 | 115 (0)| 00:00:01 |
|* 5| INDEX RANGE SCAN | IND_T_C_FID | 89 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
3- access("A"."ID"=880)
5- access("B"."FID"=880)
Note
-----
-dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
0 dbblock gets
159 consistent gets
1 physical reads
0 redosize
4822 bytes sent via SQL*Net toclient
606 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
89 rowsprocessed
发现键索引后，逻辑读从 576 降低到了 159. 这个 和NESTED LOOPS链接方式有关。
外键索引是很有用的。
6.3 外键索引
外键索引还能有效避免锁的竞争。
SQL> select sid from v$mystat where rownum=1;
SID
----------
83
SQL>delete t_c where id=2;
1 row deleted.
开启第二个回话，就是一个新的链接
SQL> select sid from v$mystat where rownum=1;
SID
----------
84
SQL> delete t_p where id=2000;
1 row deleted.
本来很可能锁住，但是因为外键所在的列建了索引，所以避免了。
都执行rollback,然后删除外键索引。
6.4 删除外键索引
SQL> drop index ind_t_c_fid;
在第一个会话中执行：
SQL> delete t_c where id=2;
在第二个会话中执行：
SQL> delete t_p where id=2000;
发现卡主了。
外键所在的表，因为删除一条记录而导致T_P所在的表完全锁住，无法做任何DML更新操作。
外键所在表的外键列取值必须在主表中的主键列有记录。
FID是依赖于T_P的主键的。
SQL> select count(*) from t_c where fid=2;
COUNT(*)
----------
90
有90条记录依赖于主表ID=2的记录，先要删除外键中的FID=2记录，然后主表T_P就可轻易删除。
SQL> delete from t_c where fid=2;
90 rows deleted.
SQL> commit;
Commit complete.
SQL> delete t_p where id=2;
1 row deleted.
SQL> commit;
这些保证了夺标记录之间记录的制约性。
6.4.1 级联删除
级联删除，在原先增加外键的基础上增加ON DELETE CASCADE关键字即可。
示例如下：
SQL> alter table t_c drop constraintfk_t_c;
Table altered.
SQL> alter table t_c add constraintfk_t_c foreign key (fid) references t_p (id) on delete cascade;
Table altered.
SQL> select count(*) from t_c where fid=3;
COUNT(*)
----------
90
SQL> delete from t_p where id=3;
1 row deleted.
SQL>commit;
设置级联删除设置后，自动删除了t_c表中的55条记录。
SQL> select count(*) from t_c where fid=3;
COUNT(*)
----------
0
这个技术比较危险，一定要慎用。
6.4.2 改造列为主键
如果生产系统有一张大表的某字段符合主键的条件。可以删除该列索引，然后建上主键。
这个方法需要在没人使用系统的时候进行操作。
其实可以很方便的完成如下：
SQL> droptable t cascade constraints purge;
SQL> create table t (id number,name varchar2(30));
SQL> insert into t selectrownum,table_name from all_tables;
2426 rows created.
SQL> commit;
Commit complete.
SQL> create index idx_t_id on t(id);
然后为ID列增加主键索引，如下：
Alter table t add constraint t_id_pkprimary key (id);
- 22.读书笔记收获不止Oracle之 索引特性活用
- 14.读书笔记收获不止Oracle之 索引
- 12.读书笔记收获不止Oracle之 索引分区表
- 15.读书笔记收获不止Oracle之 索引高度
- 16.读书笔记收获不止Oracle之 分区索引
- 17.读书笔记收获不止Oracle之 索引存储列值
- 21.读书笔记收获不止Oracle之 索引回表效率
- 23.读书笔记收获不止Oracle之 组合索引
- 24.读书笔记收获不止Oracle之 索引的危害
- 25.读书笔记收获不止Oracle之 位图索引
- 26.读书笔记收获不止Oracle之 位图索引即席查询
- 27.读书笔记收获不止Oracle之 位图索引更新
- 28.读书笔记收获不止Oracle之 函数索引
- 【读书笔记】【收获，不止Oracle】索引组织表
- 1.读书笔记收获不止Oracle之开篇
- 2.读书笔记收获不止Oracle之 意识
- 13.读书笔记收获不止Oracle之 簇表
- 18.读书笔记收获不止Oracle之 索引SUM和AVG优化
- atcoder-077S
- 20.读书笔记收获不止Oracle之 索引回表和优化
- 21.读书笔记收获不止Oracle之 索引回表效率
- Leetcode 算法题07
- 编写c++程序过程中出现的错误 annonymous type with no linkage used to declare variable
- 22.读书笔记收获不止Oracle之 索引特性活用
- 二叉树的深度和广度优先遍历
- 用C语言协助办公_01 找出所有不对劲的人
- Servlet:相对路径、绝对路径以及路径跳转问题
- Orders POJ
- 十年后，程序员还会有今天的收入吗？
- codeforces 884E Binary Matrix 并查集,滚动数组
- 因为对Perl和Python不满？圣诞节无聊？编程语言的出现都这么随意吗？
- 精选11道Java技术面试题并有答案
