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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

----------------------------------------------------------

2recursive 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

0sorts (memory)

0sorts (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

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

| 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

----------------------------------------------------------

1recursive 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

1sorts (memory)

0sorts (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

-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------

1recursive 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

0sorts (memory)

0sorts (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

------------------------------------------------------------------------------------

------------------------------------------------------------------------------------

| 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

----------------------------------------------------------

1recursive calls

0 dbblock gets

6305 consistent gets

0physical reads

0 redosize

1726112 bytes sent via sql*Net toclient

67805 bytes received via sql*Netfrom client

6116 sql*Net roundtrips to/fromclient

0sorts (memory)

0sorts (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

--------------------------------------------------------------------------------

---

--------------------------------------------------------------------------------

---

| 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

----------------------------------------------------------

42recursive 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

2sql*Net roundtrips to/from client

5sorts (memory)

0sorts (disk)

2rows processed

3.1 不使用distinct 查看

sql> select object_id from t;

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0| SELECT STATEMENT | | 91721 | 447K|429 (1)| 00:00:01 |

| 1| TABLE ACCESS FULL| T | 91721 | 447K|429 (1)| 00:00:01 |

--------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1recursive 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

0sorts (memory)

0sorts (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

--------------------------------------------------------------------------------

-----------------

%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

----------------------------------------------------------

1recursive 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

2sql*Net roundtrips to/from client

0sorts (memory)

0sorts (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

--------------------------------------------------------------------------------

----

--------------------------------------------------------------------------------

----

| 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

----------------------------------------------------------

1recursive calls

0 dbblock gets

6284 consistent gets

0physical reads

0 redosize

1626969 bytes sent via sql*Net toclient

67805 bytes received via sql*Netfrom client

6116 sql*Net roundtrips to/fromclient

0sorts (memory)

0sorts (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

------------------------------------------------------------------------------------

------------------------------------------------------------------------------------

| 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

----------------------------------------------------------

35recursive calls

0 dbblock gets

3121 consistent gets

0physical 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)

0sorts (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

---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------

1recursive 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

1sorts (memory)

0sorts (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

--------------------------------------------------------------------------------

--------------

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

13sorts (memory)

0sorts (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

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------

6recursive calls

1 dbblock gets

576 consistent gets

0physical reads

184 redo size

4822 bytes sent via sql*Net toclient

606 bytes received via sql*Net from client

7sql*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

89 rowsprocessed

6.2建立外键索引观察

sql> create index ind_t_c_fid ont_c(fid);

sql> select a.id,t_c b where a.id=b.fidand a.id=880;

89 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3784248896

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------

9recursive calls

0 dbblock gets

159 consistent gets

1physical reads

0 redosize

4822 bytes sent via sql*Net toclient

606 bytes received via sql*Net from client

7sql*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

89 rowsprocessed

发现键索引后，逻辑读从 576 降低到了 159. 这个和NESTED LOOPS链接方式有关。

外键索引是很有用的。

6.3外键索引

外键索引还能有效避免锁的竞争。

sql> select sid from v$mystat where rownum=1;

SID

----------

sql>delete t_c where id=2;

1 row deleted.

开启第二个回话，就是一个新的链接

sql> select sid from v$mystat where rownum=1;

SID

----------

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条记录依赖于主表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(*)

----------

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(*)

----------

这个技术比较危险，一定要慎用。

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);

原文链接：https://www.f2er.com/oracle/207065.html

22.读书笔记收获不止Oracle之 索引特性活用

1. 通过索引消除排序

2. 缩小返回列

3. DISTINCT排重优化

3.1 不使用distinct 查看

3.2 创建索引

4. 索引全扫描与快速全扫

5. UNION合并的优化

5.1使用索引

5.2强制都索引全扫描

6. 主外键设计

6.1外键上的索引与性能

6.2建立外键索引观察

6.3外键索引

6.4删除外键索引

6.4.1级联删除

6.4.2改造列为主键

猜你在找的Oracle相关文章

22.读书笔记收获不止Oracle之索引特性活用