索引访问与表大小的关系

                      索引访问与表大小的关系

      当我们通过索引来访问表的数据时候，当索引的扫描范围一定时，表的大小对访问的
性能并没有太大的影响.

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的角度来说，索引的层级对性能影响更加小，当扫描索引第一个值时，
由于索引是已经排好序，可以直接往后面扫描，而不需要通过从上到下再次查找的方式。