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;