<pre name="code" class="html"> create table t1 (id char(10) primary key,a1 char(10),a2 char(10));
begin
for i in 1 .. 1000
loop
insert into t1 values(i,i,'a'||i);
end loop
;
commit;
end;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',
tabname => 'T1',
estimate_percent => 100,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
/
create table t2 (id int primary key,a1 char(10),a2 char(10))organization index;
begin
for i in 1 .. 1000
loop
insert into t2 values(i,i,'a'||i);
end loop
;
commit;
end;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',
tabname => 'T2',
estimate_percent => 100,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
/
SQL> update t1 set id=1999 where id=999;
已更新 1 行。
SQL> commit;
提交完成。
SQL> update t2 set id=1999 where id=999;
已更新 1 行。
SQL> commit;
提交完成。
SQL> select * from t1 where id>990;
ID A1 A2
---------- ---------- ----------
991 991 a991
992 992 a992
993 993 a993
994 994 a994
995 995 a995
996 996 a996
997 997 a997
998 998 a998
1999 999 a999
1000 1000 a1000
1001 10001 a1001
已选择11行。
SQL> select * from t2 where id>990;
ID A1 A2
---------- ---------- ----------
991 991 a991
992 992 a992
993 993 a993
994 994 a994
995 995 a995
996 996 a996
997 997 a997
998 998 a998
1000 1000 a1000
1001 10001 a1001
1999 999 a999
已选择11行。
可以看到t1表是heap表输出不按主键排序
t2表时iot表输出按主键列排序
SQL> select * from t1 where id>990 order by id ;
ID A1 A2
---------- ---------- ----------
1000 1000 a1000
1001 10001 a1001
1999 999 a999
991 991 a991
992 992 a992
993 993 a993
994 994 a994
995 995 a995
996 996 a996
997 997 a997
998 998 a998
已选择11行。
SQL> select * from t2 where id>990 order by id;
ID A1 A2
---------- ---------- ----------
991 991 a991
992 992 a992
993 993 a993
994 994 a994
995 995 a995
996 996 a996
997 997 a997
998 998 a998
1000 1000 a1000
1001 10001 a1001
1999 999 a999
已选择11行。