一、创建测试表
SQL> create table t_obj as select * from dba_objects;
Table created
二、创建索引
SQL> create index t_obj_ind on t_obj(object_id);
Index created
三、收集统计信息
SQL> exec dbms_stats.gather_table_stats(user,'t_obj',cascade=>true);
PL/SQL procedure successfully completed
四、查看块数，每块记录数
SQL> select 'T_OBJ' tbl_name, rows_per_block,count(*) number_of_such_blocks from
2 (
3 select dbms_rowid.rowid_block_number(rowid),count(*) rows_per_block from t_obj group by dbms_rowid.rowid_block_number(rowid)
4 ) group by 'T_OBJ' ,rows_per_block;
TBL_NAME ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS
-------- -------------- ---------------------
T_OBJ 80 18
T_OBJ 67 156
T_OBJ 89 2
T_OBJ 84 1
T_OBJ 85 1
T_OBJ 55 1
T_OBJ 74 27
T_OBJ 77 28
T_OBJ 69 152
T_OBJ 72 27
T_OBJ 75 28
T_OBJ 66 71
T_OBJ 83 2
T_OBJ 73 37
T_OBJ 70 74
T_OBJ 65 19
T_OBJ 64 2
T_OBJ 76 35
T_OBJ 90 2
T_OBJ 71 56
TBL_NAME ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS
-------- -------------- ---------------------
T_OBJ 81 12
T_OBJ 82 2
T_OBJ 79 16
T_OBJ 68 225
T_OBJ 63 4
T_OBJ 24 1
T_OBJ 78 33
T_OBJ 88 1
28 rows selected
五、创建另一测试表t_obj_1
SQL> create table t_obj_1 as select * from dba_objects where rownum < 5;
Table created
六查看t_obj_1的块数，每块记录数
SQL> select 'T_OBJ_1' tbl_name, rows_per_block,count(*) number_of_such_blocks from
2 (
3 select dbms_rowid.rowid_block_number(rowid),count(*) rows_per_block from t_obj_1 group by dbms_rowid.rowid_block_number(rowid)
4 ) group by 'T_OBJ_1' ,rows_per_block;
TBL_NAME ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS
-------- -------------- ---------------------
T_OBJ_1 4 1
七、使用records_per_block，固定每块记录数。
SQL> alter table t_obj_1 minimize records_per_block;
Table altered
八、插入数据
SQL> truncate table t_obj_1;
Table truncated
SQL>
SQL> select 'T_OBJ_1' tbl_name, rows_per_block,count(*) number_of_such_blocks from
2 (
3 select dbms_rowid.rowid_block_number(rowid),count(*) rows_per_block from t_obj_1 group by dbms_rowid.rowid_block_number(rowid)
4 ) group by 'T_OBJ_1' ,rows_per_block;
TBL_NAME ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS
-------- -------------- ---------------------
SQL> insert into t_obj_1 select * from dba_objects;
72532 rows inserted
SQL> commit;
Commit complete
九创建索引
SQL> create index t_obj_ind_1 on t_obj_1(object_id);
Index created
十、查看块数，每块记录数
SQL> select 'T_OBJ_1' tbl_name, rows_per_block,count(*) number_of_such_blocks from
2 (
3 select dbms_rowid.rowid_block_number(rowid),count(*) rows_per_block from t_obj_1 group by dbms_rowid.rowid_block_number(rowid)
4 ) group by 'T_OBJ_1' ,rows_per_block;
TBL_NAME ROWS_PER_BLOCK NUMBER_OF_SUCH_BLOCKS
-------- -------------- ---------------------
T_OBJ_1 4 18133
T_OBJ_1每个块存放的记录数为4，因此T_OBJ_1比T_OBJ表使用的数据块要多的多。
十一、查看t_obj执行计划
SQL> set autotrace traceonly;
SQL> set linesize 800
SQL> select * from t_obj where object_id < 1000;
已选择942行。
执行计划
----------------------------------------------------------
Plan hash value: 1458571288
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 964 | 93508 | 19 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_OBJ | 964 | 93508 | 19 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_OBJ_IND | 964 | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<1000)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
141 consistent gets
0 physical reads
0 redo size
94571 bytes sent via SQL*Net to client
1098 bytes received via SQL*Net from client
64 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
942 rows processed
十一、查看t_obj_1执行计划
SQL> select * from t_obj_1 where object_id < 1000;
已选择942行。
执行计划
----------------------------------------------------------
Plan hash value: 3240268517
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 942 | 190K| 278 (0)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_OBJ_1 | 942 | 190K| 278 (0)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | T_OBJ_IND_1 | 942 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<1000)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
474 consistent gets
4 physical reads
0 redo size
94571 bytes sent via SQL*Net to client
1098 bytes received via SQL*Net from client
64 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
当分别选择1000数据时，执行计划相同，返回的结果集相同，但T_OBJ_1一致性读的次数为474，远多于T_OBJ的141次，原因为相同的记录，T_OBJ_1分布在更大的数据块上。降低了一个块被重复读取的概率。缺点是性能下降。当然，如果多个用户并发修改不同的记录，那么这些用户读取同一个块的概率就会降低，也就是热块出现的概率降低。
