oracle 索引回表,文章详情页_Oracle 索引回表_OPCoder

索引存储索引列的值和 ROWID，当执行数据查询，根据索引找到了指定的记录所在行后，还需要根据 ROWID 再查询表中数据，就是回表。

在数据库中，数据的存储都是以块为单位的，称为数据块。表中每一行数据都有唯一的地址标志 ROWID。每次使用 SQL 进行查询的时候，都要扫描数据块，找到行所在的 ROWID，再扫描该表的数据块。回表将会导致扫描更多的数据块。

回表

"回表" 一般就是指执行计划里显示的 TABLE ACCESS BY INDEX ROWID

查询的字段里有索引不包含的列

SQL> create table t1 as select * from dba_objects;

SQL> create index idx_t1 on t1(object_id);

SQL> select object_name, object_id from t1 where t1.object_id = 13;

执行计划

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

Plan hash value: 50753647

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

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

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

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

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

只查询索引列，但是需要回表过滤掉其他行

SQL> select object_id from t1 where object_id = 13 and owner = 'SCOTT';

执行计划

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

Plan hash value: 50753647

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 7 | 210 | 2 (0)| 00:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 7 | 210 | 2 (0)| 00:00:01 |

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

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

回表开销

索引查询要尽可能避免回表，如果不可避免，则需要关注聚簇因子是否过大。

创建表 T1，插入有序的数据

SQL> create table t1 as select * from dba_objects order by object_id;

SQL> create index idx_t1 on t1(object_id);

--查看聚簇因子

SQL> select

s.index_name,

s.table_name,

s.clustering_factor,

s.num_rows,

s.blevel,

s.last_analyzed

from user_indexes s where s.table_name = 'T1';

INDEX_NAME TABLE_NAME CLUSTERING_FACTOR NUM_ROWS BLEVEL LAST_ANALYZED

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

IDX_T1 T1 1085 76090 1 2020/4/25 19:07:50

--回表开销

SQL> alter session set statistics_level = all;

SQL> select /*+ index(t1 idx_t1) */ * from t1 where object_id between 10000 and 30000;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT

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

SQL_ID 4xz6z1b4rmg3v, child number 0

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

select * from t1 where object_id between 10000 and 30000

Plan hash value: 50753647

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

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |

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

| 0 | SELECT STATEMENT | | 1 | | 19849 |00:00:00.15 | 2956 | 258 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 15783 | 19849 |00:00:00.15 | 2956 | 258 |

|* 2 | INDEX RANGE SCAN | IDX_T1 | 1 | 15783 | 19849 |00:00:00.08 | 1367 | 38 |

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

表 T1 的聚簇因子比较小，回表的代价较低，产生了 2956 个 BUFFER。

创建表 T2，插入无序的数据

SQL> create table t2 as select * from dba_objects order by dbms_random.value;

SQL> create index idx_t2 on t2(object_id);

--查看聚簇因子

SQL> select

s.index_name,

s.table_name,

s.clustering_factor,

s.num_rows,

s.blevel,

s.last_analyzed

from user_indexes s where s.table_name = 'T2';

INDEX_NAME TABLE_NAME CLUSTERING_FACTOR NUM_ROWS BLEVEL LAST_ANALYZED

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

IDX_T2 T2 76035 76091 1 2020/4/25 19:07:50

--回表开销

SQL> alter session set statistics_level = all;

SQL> select /*+ index(t2 idx_t2) */ * from t2 where object_id between 10000 and 30000;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT

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

SQL_ID 7bugcp2muw142, child number 0

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

select /*+ index(t2 idx_t2) */ * from t2 where object_id between 10000

and 30000

Plan hash value: 2008370210

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

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |

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

| 0 | SELECT STATEMENT | | 1 | | 19849 |00:00:00.59 | 21202 | 790 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 17962 | 19849 |00:00:00.59 | 21202 | 790 |

|* 2 | INDEX RANGE SCAN | IDX_T2 | 1 | 17962 | 19849 |00:00:00.08 | 1367 | 0 |

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

表 T2 的聚簇因子比较大，回表的代价很高，产生了 21202 个 BUFFER。

回表优化

通过子查询解决(但这种方法并不一定效率高)

SQL> select object_id, object_name from t1 where object_id in (select /*+ cardinality(1 10) */ object_id from t1);

执行计划

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

Plan hash value: 3529199878

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 68847 | 6185K| 577 (2)| 00:00:07 |

|* 1 | HASH JOIN | | 68847 | 6185K| 577 (2)| 00:00:07 |

| 2 | SORT UNIQUE | | 68847 | 874K| 123 (1)| 00:00:02 |

| 3 | INDEX FAST FULL SCAN| IDX_T1 | 68847 | 874K| 123 (1)| 00:00:02 |

| 4 | INDEX FAST FULL SCAN | IDX_T1 | 68847 | 5311K| 123 (1)| 00:00:02 |

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

通过复合索引解决

SQL> drop index idx_t1;

SQL> create index idx_t1 on t1(object_id, object_name);

SQL> select object_id, object_name from t1 where object_id between 10000 and 30000;

已选择19849行。

执行计划

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

Plan hash value: 1369807930

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 15783 | 1217K| 104 (0)| 00:00:02 |

|* 1 | INDEX RANGE SCAN| IDX_T1 | 15783 | 1217K| 104 (0)| 00:00:02 |

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

参考资料

原创文章，转载请注明出处：http://www.opcoder.cn/article/35/