Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2765975
  • 博文数量: 389
  • 博客积分: 4177
  • 博客等级: 上校
  • 技术积分: 4773
  • 用 户 组: 普通用户
  • 注册时间: 2008-11-16 23:29
文章分类

全部博文(389)

分类: Oracle

2015-08-22 15:25:14

                      索引访问与表大小的关系


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

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

 

 

 

 

阅读(2267) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~