oracle索引大小暴增_索引访问与表大小的关系

索引访问与表大小的关系

当我们通过索引来访问表的数据时候，当索引的扫描范围一定时，表的大小对访问的

性能并没有太大的影响.

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> select count(*) from t1;

COUNT(*)

----------

70768

SQL> create table t2 as select * from dba_objects where object_id<1000;

Table created.

SQL> select count(*) from t2;

COUNT(*)

----------

940

SQL> create index t1_id on t1(object_id);

Index created.

SQL> create index t2_id on t2(object_id);

Index created.

创建两个表，并分别在object_id栏位上增加过索引.

SQL> exec dbms_stats.gather_table_stats('SYS','T1',method_opt=>'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SYS','T2',method_opt=>'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> select owner,index_name,BLEVEL,LEAF_BLOCKS

2  from dba_ind_statistics

3  where table_name in ('T1','T2') and owner='SYS'

4  ;

OWNER      INDEX_NAME                         BLEVEL LEAF_BLOCKS

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

SYS        T1_ID                                   1         157

SYS        T2_ID                                   1           2

SQL> select owner,table_name,num_rows,blocks

2  from dba_tables

3  where table_name in ('T1','T2') and owner='SYS';

OWNER      TABLE_NAME                       NUM_ROWS     BLOCKS

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

SYS        T2                                    940         12

SYS        T1                                  70768       1041

查询比较大的表

SQL> select count(object_name) from t1 where  object_id<10;

COUNT(OBJECT_NAME)

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

8

Execution Plan

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

Plan hash value: 863914470

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

------

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

|

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

------

|   0 | SELECT STATEMENT             |       |     1 |    30 |     3   (0)| 00:0

0:01 |

|   1 |  SORT AGGREGATE              |       |     1 |    30 |            |

|

|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     4 |   120 |     3   (0)| 00:0

0:01 |

|*  3 |    INDEX RANGE SCAN          | T1_ID |     4 |       |     2   (0)| 00:0

0:01 |

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

------

Predicate Information (identified by operation id):

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

3 - access("OBJECT_ID"<10)

Statistics

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

164  recursive calls

0  db block gets

24  consistent gets

0  physical reads

0  redo size

532  bytes sent via SQL*Net to client

519  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

4  sorts (memory)

0  sorts (disk)

1  rows processed

查询小表

SQL> select count(object_name) from t2 where  object_id<10;

COUNT(OBJECT_NAME)

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

8

Execution Plan

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

Plan hash value: 729095221

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

------

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

|

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

------

|   0 | SELECT STATEMENT             |       |     1 |    20 |     3   (0)| 00:0

0:01 |

|   1 |  SORT AGGREGATE              |       |     1 |    20 |            |

|

|   2 |   TABLE ACCESS BY INDEX ROWID| T2    |     8 |   160 |     3   (0)| 00:0

0:01 |

|*  3 |    INDEX RANGE SCAN          | T2_ID |     8 |       |     2   (0)| 00:0

0:01 |

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

------

Predicate Information (identified by operation id):

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

3 - access("OBJECT_ID"<10)

Statistics

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

164  recursive calls

0  db block gets

23  consistent gets

0  physical reads

0  redo size

532  bytes sent via SQL*Net to client

519  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

4  sorts (memory)

0  sorts (disk)

1  rows processed

可以看出查询小表和查询大表，consistent gets没有太的区别，cost比较接近.经常有比如一个表中

存放180天和30天的数据，而通过索引只查询前一天的数据，这个表里是否存放30天数据就比较合理呢.

如果仅从索引访问的方式来看，其实并没有影响.

从oracle的index range scan的角度来说，索引的层级对性能影响更加小，当扫描索引第一个值时，

由于索引是已经排好序，可以直接往后面扫描，而不需要通过从上到下再次查找的方式。