7.索引的性能分析-白红宇

7.索引的性能分析

阅读量：7234 次

发布时间：2019-06-29

本文共 27557 字，大约阅读时间需要 91 分钟。

2013-08-07 星期三天气：晴

-------------------------------索引的性能分析－－－－－－－－－－－－－－－－－－－－－－－

场景：假设数据块的大小8000字节，查询返回的记录行数是整表记录的20％

1、表有100000行数据，返回20000行数据，假如每行80字节，1块＝100行，全表有1000块，

查询到的对应的叶子行有20000个，扫索引扫20000块次，全表扫，扫1000块次。

2、表有100000行数据，返回20000行数据，假如每行800字节，1块＝10行，全表有10000块，

查询到的对应的叶子行有20000个，扫索引扫20000块次，全表扫，扫10000块次。

3、表有100000行数据，返回20000行数据，假如每行1600字节，1块＝5行，全表有20000块，

查询到的对应的叶子行有20000个，扫索引扫20000块次，全表扫，扫20000块次。

4、表有100000行数据，返回20000行数据，假如每行8000字节，1块＝1行，全表有100000块，

查询到的对应的叶子行有20000个，扫索引扫20000块次，全表扫，扫100000块次。

归纳：

A 不一定使用索引查询效率就会提升，场景1个场景2反而会下降。

B 索引的使用和块、行的比例有关系，块中含有的行越多扫索引的效率就会越低，

块中含有的行越少，扫索引的效率就会越高。和行的密度的有关系。

案例：研究RBO优化法则和CBO性能分析的差别

1、创建一个案例表

SQL> create table tx as select 1 id,object_name from all_objects;

Table created.

SQL> update tx set id=99 where rownum=1;

1 row updated.

SQL> commit;

Commit complete.

2、用RBO优化器生成执行计划

SQL> set autotrace trace exp

SQL> select /*+ rule */ * from tx where id=1;

Execution Plan

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

Plan hash value: 40191160

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

|* 1 | TABLE ACCESS FULL| TX | －－全表扫描

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

Predicate Information (identified by operation id):

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

1 - filter("ID"=1)

Note

-----

- rule based optimizer used (consider using cbo)

SQL> create index ind_tx on tx(id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'tx',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select /*+ rule */ * from tx where id=1;

Execution Plan

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

Plan hash value: 2614573569

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS BY INDEX ROWID| TX |

|* 2 | INDEX RANGE SCAN | IND_TX | －－RBO下只要有索引一定会强制走索引的。

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

Predicate Information (identified by operation id):

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

2 - access("ID"=1)

Note

-----

- rule based optimizer used (consider using cbo)

SQL> select /*+ rule */ * from tx where id=99;

Execution Plan

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

Plan hash value: 2614573569

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS BY INDEX ROWID| TX |

|* 2 | INDEX RANGE SCAN | IND_TX |

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

Predicate Information (identified by operation id):

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

2 - access("ID"=99)

Note

-----

- rule based optimizer used (consider using cbo）

================================================

3、用CBO生成执行计划

SQL> select * from tx where id=1;

Execution Plan

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

Plan hash value: 40191160

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

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

| 0 | SELECT STATEMENT | | 40919 | 1118K| 47 (3)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TX | 40919 | 1118K| 47 (3)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("ID"=1)

SQL> select /*+ index(tx ind_tx) */ * from tx where id=1; －－CBO下，强制走索引代价很大。

Execution Plan

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

Plan hash value: 2614573569

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

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

| 0 | SELECT STATEMENT | | 40919 | 1118K| 273 (1)| 00:00:04 |

| 1 | TABLE ACCESS BY INDEX ROWID| TX | 40919 | 1118K| 273 (1)| 00:00:04 |

|* 2 | INDEX RANGE SCAN | IND_TX | 40919 | | 81 (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("ID"=1)

SQL> set linesize 1000

SQL> select * from tx where id=99;

Execution Plan

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

Plan hash value: 2614573569

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

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

| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TX | 1 | 28 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IND_TX | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("ID"=99)

查询列值分布数量很高的值，会全表扫，反之，才可能会用索引。

影响索引使用效率的三个影响因素：

1、块中行的密度

2、查询语句中where条件中的索引列引用值在列值中的分布情况。

3、索引和表数据放在同一个磁盘上，会发生IO的争用。

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

案例：分析B树索引的性能

SQL> drop table t1;

drop table t1

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> create table t1(id int primary key,name varchar2(10));

Table created.

SQL> begin

2 for i in 1..10000 loop

3 insert into t1 values(i,'T1');

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> create table t2(id int primary key,name varchar2(10));

Table created.

SQL> begin

2 for i in 1..1000000 loop

3 insert into t2 values(i,'T2');

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autotrace trace exp －－显示执行计划

SQL> select * from t1 where id=100;

Execution Plan

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

Plan hash value: 1846342996

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

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

| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 6 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | SYS_C006531 | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("ID"=100)

SQL> select * from t2 where id=100;

Execution Plan

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

Plan hash value: 3288443420

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

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

| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 7 | 3 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | SYS_C006532 | 1 | | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("ID"=100)

T1表的索引数只有两层，但是T2表的平衡树有三层，T3表在读取索引的时候会多读取一个根块。

查询层数：

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

select * from user_indexes; --首先查看对应表的索引名

analyze index 索引名 validate structure; --分析

select * from index_stats --查看索引的统计信息

如下：

SQL> analyze index ind_obj_id validate structure;

Index analyzed

SQL> select * from index_stats;

HEIGHT BLOCKS NAME PARTITION_NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

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

2 384 IND_OBJ_ID 115766 273 1843607 7996 272 1 3750 8028 52956 839277 38598 3 2190936 1847357 85 2.×××745738 3.99963728690606 0 0 1 8

两次查询的代价是一样的，有了B树索引之后，查询的损耗不会因为表数据量的增加而增加，

即使数据量相差很大，当在唯一性索引或者主键索引查询的时候，消耗的资源几乎是没有区别的，

这就是索引发挥的巨大作用。

如果没有索引：

SQL> select /*+ full(t1) */ * from t1 where id=100;

Execution Plan

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

Plan hash value: 3617692013

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

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

| 0 | SELECT STATEMENT | | 1 | 6 | 6 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T1 | 1 | 6 | 6 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("ID"=100)

SQL> select /*+ full(t2) */ * from t2 where id=100;

Execution Plan

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

Plan hash value: 1513984157

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

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

| 0 | SELECT STATEMENT | | 1 | 7 | 444 (7)| 00:00:06 |

|* 1 | TABLE ACCESS FULL| T2 | 1 | 7 | 444 (7)| 00:00:06 |

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

Predicate Information (identified by operation id):

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

1 - filter("ID"=100)

update操作中索引的影响

SQL> update t1 set id=10001 where id=100;

1 row updated.

Execution Plan

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

Plan hash value: 2799287261

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

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

| 0 | UPDATE STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |

| 1 | UPDATE | T1 | | | | |

|* 2 | INDEX UNIQUE SCAN| SYS_C006531 | 1 | 4 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("ID"=100)

SQL> update /*+ full(t1) */ t1 set id=10001 where id=100;

0 rows updated.

Execution Plan

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

Plan hash value: 2927627013

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

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

| 0 | UPDATE STATEMENT | | 1 | 4 | 6 (0)| 00:00:01 |

| 1 | UPDATE | T1 | | | | |

|* 2 | TABLE ACCESS FULL| T1 | 1 | 4 | 6 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("ID"=100)

SQL> rollback;

Rollback complete.

SQL> update t2 set id=1000001 where id=100;

1 row updated.

Execution Plan

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

Plan hash value: 3398718552

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

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

| 0 | UPDATE STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |

| 1 | UPDATE | T2 | | | | |

|* 2 | INDEX UNIQUE SCAN| SYS_C006532 | 1 | 5 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("ID"=100)

SQL> update /*+ full(t2) */ t2 set id=1000001 where id=100;

0 rows updated.

Execution Plan

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

Plan hash value: 2722410703

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

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

| 0 | UPDATE STATEMENT | | 1 | 5 | 444 (7)| 00:00:06 |

| 1 | UPDATE | T2 | | | | |

|* 2 | TABLE ACCESS FULL| T2 | 1 | 5 | 444 (7)| 00:00:06 |

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

Predicate Information (identified by operation id):

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

2 - filter("ID"=100)

SQL> rollback;

Rollback complete.

delete操作：

SQL> delete from t2 where id=100;

1 row deleted.

Execution Plan

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

Plan hash value: 1328729220

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

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

| 0 | DELETE STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |

| 1 | DELETE | T2 | | | | |

|* 2 | INDEX UNIQUE SCAN| SYS_C006532 | 1 | 5 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("ID"=100)

SQL> delete /*+ full(t2) */ from t2 where id=100;

0 rows deleted.

Execution Plan

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

Plan hash value: 1451993194

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

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

| 0 | DELETE STATEMENT | | 1 | 5 | 444 (7)| 00:00:06 |

| 1 | DELETE | T2 | | | | |

|* 2 | TABLE ACCESS FULL| T2 | 1 | 5 | 444 (7)| 00:00:06 |

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

Predicate Information (identified by operation id):

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

2 - filter("ID"=100)

SQL> rollback;

Rollback complete.

注意：一定要where中用到索引列才能走索引：

SQL> update t2 set name='T11' where name='T2'; －－没有用到索引列是一定不会走索引的。

1000000 rows updated.

Execution Plan

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

Plan hash value: 2722410703

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

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

| 0 | UPDATE STATEMENT | | 1013K| 2970K| 447 (8)| 00:00:06 |

| 1 | UPDATE | T2 | | | | |

|* 2 | TABLE ACCESS FULL| T2 | 1013K| 2970K| 447 (8)| 00:00:06 |

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

Predicate Information (identified by operation id):

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

2 - filter("NAME"='T2')

SQL> update /*+ index(t2 SYS_C006532) */ t2 set name='T11' where name='T2'; －－强制走索引代价巨大

1000000 rows updated.

Execution Plan

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

Plan hash value: 1100077776

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

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

| 0 | UPDATE STATEMENT | | 1013K| 2970K| 3728 (2)| 00:00:45 |

| 1 | UPDATE | T2 | | | | |

|* 2 | TABLE ACCESS BY INDEX ROWID| T2 | 1013K| 2970K| 3728 (2)| 00:00:45 |

| 3 | INDEX FULL SCAN | SYS_C006532 | 1013K| | 1907 (2)| 00:00:23 |

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

Predicate Information (identified by operation id):

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

2 - filter("NAME"='T2')

插入数据的PLAN

SQL> insert into t1 values(10001,'T11');

1 row created.

Execution Plan

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

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

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

| 0 | INSERT STATEMENT | | 1 | 6 | 1 (0)| 00:00:01 |

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

A 在HWM以下找连续的空间将数据插入

B 没有找到，在HWM之上插入数据，后HWM上移。

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

块中的行的分布密度对使用索引的影响。

SQL> conn hr/hr

Connected.

SQL> create table t3(id int primary key,name1 varchar2(2000),name2 varchar2(2000)); －－行密度大的表

Table created.

SQL> create table t4(id int primary key,name1 char(2000),name2 char(2000)); －－行密度小的表

Table created.

块大小是8K，预留10％的空间，实际每个块能存7373个字节，T4表的块只能放一行数据。

SQL> begin

2 for i in 1..20000 loop

3 insert into t3 values(i,'T3','T31');

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> begin

2 for i in 1..20000 loop

3 insert into t4 values(i,'T4','T41');

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t3',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t4',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

SQL> select * from t3 where id>17000;

Execution Plan

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

Plan hash value: 4161002650

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

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

| 0 | SELECT STATEMENT | | 3000 | 33000 | 14 (8)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T3 | 3000 | 33000 | 14 (8)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("ID">17000)

SQL> select * from t4 where id>17000; －－行密度小的越容易用索引

Execution Plan

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

Plan hash value: 1161789439

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

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

| 0 | SELECT STATEMENT | | 3000 | 11M| 3011 (1)| 00:00:37 |

| 1 | TABLE ACCESS BY INDEX ROWID| T4 | 3000 | 11M| 3011 (1)| 00:00:37 |

|* 2 | INDEX RANGE SCAN | SYS_C006534 | 3000 | | 7 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("ID">17000)

SQL> set autotrace trace exp stat

SQL> select /*+ index(t3 SYS_C006533) */ * from t3 where id>17000;

3000 rows selected.

Execution Plan

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

Plan hash value: 2289016736

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

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

| 0 | SELECT STATEMENT | | 3000 | 33000 | 14 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 3000 | 33000 | 14 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | SYS_C006533 | 3000 | | 7 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("ID">17000)

Statistics

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

0 recursive calls

0 db block gets

415 consistent gets －－走索引一致性读还是有差别的

0 physical reads

0 redo size

55787 bytes sent via SQL*Net to client

2589 bytes received via SQL*Net from client

201 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3000 rows processed

SQL> select * from t3 where id>17000;

3000 rows selected.

Execution Plan

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

Plan hash value: 4161002650

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

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

| 0 | SELECT STATEMENT | | 3000 | 33000 | 14 (8)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T3 | 3000 | 33000 | 14 (8)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("ID">17000)

Statistics

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

0 recursive calls

0 db block gets

253 consistent gets

0 physical reads

0 redo size

55787 bytes sent via SQL*Net to client

2589 bytes received via SQL*Net from client

201 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3000 rows processed

T3表块上行分布密度比较大，用索引的概率小。T4表块上行分布密度小，用索引的概率大。

注意：上面这个实验要找到临界值才能成功的，临界值就是返回就录行数的值，

在这个值上走索引和全表扫的性能是一样的。

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

案例：B树索引使用的场合——键值重复率低的字段适合创建B树索引

SQL> create table t5 as select object_id,object_name from all_objects;

Table created.

SQL> create table t6 as select mod(object_id,2) object_id,object_name from all_objects; －－ID字段重复率很高

Table created.

SQL> create index t3_ind on t5(object_id);

Index created.

SQL> create index t6_ind on t6(object_id);

Index created.

SQL> select count(distinct object_id) from t5;

COUNT(DISTINCTOBJECT_ID)

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

40929

SQL> select count(distinct object_id) from t6;

COUNT(DISTINCTOBJECT_ID)

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

SQL> exec dbms_stats.gather_table_stats(user,'t5',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t6',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

SQL> select * from t5 where object_id=1;

no rows selected

Execution Plan

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

Plan hash value: 3812361271

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

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

| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T5 | 1 | 30 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T3_IND | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"=1)

Statistics

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

1 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

339 bytes sent via SQL*Net to client

389 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

SQL> select * from t6 where object_id=1;

20511 rows selected.

Execution Plan

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

Plan hash value: 1930642322

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

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

| 0 | SELECT STATEMENT | | 20465 | 539K| 46 (3)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T6 | 20465 | 539K| 46 (3)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_ID"=1)

Statistics

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

1 recursive calls

0 db block gets

1556 consistent gets

0 physical reads

0 redo size

626252 bytes sent via SQL*Net to client

15437 bytes received via SQL*Net from client

1369 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

20511 rows processed

强制T6走索引的话

SQL> select /*+ index(t6 T6_IND) */ * from t6 where object_id=1;

20511 rows selected.

Execution Plan

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

Plan hash value: 1497073299

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

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

| 0 | SELECT STATEMENT | | 20465 | 539K| 228 (1)| 00:00:03 |

| 1 | TABLE ACCESS BY INDEX ROWID| T6 | 20465 | 539K| 228 (1)| 00:00:03 |

|* 2 | INDEX RANGE SCAN | T6_IND | 20465 | | 40 (3)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"=1)

Statistics

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

1 recursive calls

0 db block gets

2954 consistent gets

0 physical reads

0 redo size

626252 bytes sent via SQL*Net to client

15437 bytes received via SQL*Net from client

1369 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

20511 rows processed

oracle先要扫索引的叶子行，找到符合条件的键值，根据键值中的rowid到表中找到对应的行。

如果满足的条件很多，oracle不停的访问索引再访问表，如此循环。

SQL> select object_id from t5 where object_id=1; --查询索引列，不需要用rowid再去访问表了，

因为查询索引已经满足了要求。

no rows selected

Execution Plan

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

Plan hash value: 2377336880

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

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

| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| T3_IND | 1 | 5 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("OBJECT_ID"=1)

SQL> select object_id from t6 where object_id=1;

20511 rows selected.

Execution Plan

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

Plan hash value: 3707116216

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

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

| 0 | SELECT STATEMENT | | 20465 | 61395 | 20 (5)| 00:00:01 |

|* 1 | INDEX FAST FULL SCAN| T6_IND | 20465 | 61395 | 20 (5)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_ID"=1)

结论：创建索引的时候，要选择那些键值重复率低的字段。否则是很难用上的。

转载于:https://blog.51cto.com/chenhuican/1279850

你可能感兴趣的文章

Python之父重回决策层，社区未来如何发展？

查看>>

J2EE开发笔记（一）—— J2EE开发环境配置

查看>>

算法与数据结构大系列 - NO.1 - 插入排序

查看>>

快速体验 Sentinel 集群限流功能，只需简单几步

查看>>

SWF是什么文件，SWF文件用什么软件可以打开

查看>>

回溯算法讲解--适用于leetcode绝大多数回溯题目

查看>>

vue--为什么data属性必须是一个函数

查看>>

240. Search a 2D Matrix II

查看>>

力扣(LeetCode)155

查看>>

Object.keys()是按什么顺序返回值的？

查看>>

在 JavaScript 中优雅的提取循环内的数据

查看>>

基于Nodejs的前端灰度发布方案_20190228

import提升导致Fundebug报错：“请配置apikey”

为什么 kubernetes 天然适合微服务（2）