create table t0
(
sid int not null ,
sname varchar2(20)
)
tablespace test;
--循环导入数据
declare
maxrecords constant int:=100000;
i int :=1;
begin
for i in 1..maxrecords loop
insert into t0 values(i,'ocpyang'||i);
end loop;
dbms_output.put_line(' 成功录入数据! ');
commit;
end;
/
create table t1
as
select sid,sname from t0 order by sname desc;
create index index_t1 on t1(sid);
create table t2
as
select sid,sname from t0 order by sid asc;
create index index_t2 on t2(sid asc);
--分析两张表及其索引
EXEC DBMS_STATS.gather_table_stats(USER, 'T1');
EXEC DBMS_STATS.gather_table_stats(USER, 'T2');
EXEC DBMS_STATS.gather_index_stats(USER, 'INDEX_T1');
EXEC DBMS_STATS.gather_index_stats(USER, 'INDEX_T2');
---比较同一个查询
set autot traceonly stat;
SELECT * FROM t1 WHERE sid BETWEEN 100 AND 120;
统计信息
----------------------------------------------------------
5 recursive calls
4 db block gets
15 consistent gets 一致读
0 physical reads
540 redo size
1240 bytes sent via SQL*Net to client
530 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21 rows processed
SELECT * FROM t2 WHERE sid BETWEEN 100 AND 120;
统计信息
----------------------------------------------------------
6 recursive calls
4 db block gets
9 consistent gets 一致读
0 physical reads
540 redo size
1240 bytes sent via SQL*Net to client
530 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21 rows processed
set autotrace off;
由上得知,通过执行统计信息观察,t1表的查询一致读是15,而t2表的一致读只有9,尽然t1的一致读尽然是t2的1倍还多,
很奇怪,同样的表结构,同样的数据.
----分析原因:
select
b.table_name,
a.index_name,
b.num_rows,
b.blocks,
a.clustering_factor from
user_indexes a,user_tables b
where b.table_name in ('T1','T2')
and a.table_name=b.table_name;
TABLE_NAME INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
---------- ---------- ---------- ---------- -----------------
T1 INDEX_T1 100000 330 1048
T2 INDEX_T2 100000 330 316
通过查询聚簇因子发现,两个表的聚簇因子差别很大,基于sid的索引在sid是顺序排列的表中,clustering_factor的值相差很大。
T1表中数据属于无序状态,这个时候的CLUSTERING_FACTOR比较接近NUM_ROWS,说明如 果扫描整个表,
每次都要根据Index来读取相应行的RowID,这个时候的IO操作很多,自然检索时间会比较长。
T2表数据有序,CLUSTERING_FACTOR比较接近BLOCKS,说明相邻的数据在一个块中,减少了IO操作数量,
自然检索时间会大大降低.