iot表输出按主键列排序,heap表不是

<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行。