索引访问与表大小的关系
当我们通过索引来访问表的数据时候，当索引的扫描范围一定时，表的大小对访问的
性能并没有太大的影响.
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的角度来说，索引的层级对性能影响更加小，当扫描索引第一个值时，
由于索引是已经排好序，可以直接往后面扫描，而不需要通过从上到下再次查找的方式。