[20210316]为什么刷新缓存后输出记录顺序发生变化.txt

1.环境：
TTT@aaa.bbb.ccc.ddd:1521/orcl> @ ver1
TTT@aaa.bbb.ccc.ddd:1521/orcl> @ prxx
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 18.0.0.0.0
BANNER                        : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
BANNER_LEGACY                 : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.建立测试例子：
TTT@aaa.bbb.ccc.ddd:1521/orcl> create table t  pctfree 99 as select level id, rpad('abc', 3500, 'x') vc from dual connect by level <= 500 order by dbms_random.random;
Table created.

TTT@aaa.bbb.ccc.ddd:1521/orcl> create index i_t_id on t(id) ;
Index created.

TTT@aaa.bbb.ccc.ddd:1521/orcl> exec dbms_stats.gather_table_stats(null, 'T', cascade=>true);
PL/SQL procedure successfully completed.

3.测试：
TTT@aaa.bbb.ccc.ddd:1521/orcl> select /*+ index(t) */ id,substr(vc,1,2),rowid from  t where id between 1 and 10;
        ID SUBS ROWID
---------- ---- ------------------
         1 ab   AAA78QAAMAAC7jpAAA
         2 ab   AAA78QAAMAAC7kHAAA
         3 ab   AAA78QAAMAAC7jJAAA
         4 ab   AAA78QAAMAAC7Z1AAA
         5 ab   AAA78QAAMAAC7hhAAA
         6 ab   AAA78QAAMAAC7iSAAA
         7 ab   AAA78QAAMAAC7iDAAA
         8 ab   AAA78QAAMAAC7i4AAA
         9 ab   AAA78QAAMAAC7l9AAA
        10 ab   AAA78QAAMAAC7gVAAA
10 rows selected.

TTT@aaa.bbb.ccc.ddd:1521/orcl> alter system flush buffer_cache;
System altered.

TTT@aaa.bbb.ccc.ddd:1521/orcl> select /*+ index(t) */ id,substr(vc,1,2),rowid from  t where id between 1 and 10;
        ID SUBS ROWID
---------- ---- ------------------
         1 ab   AAA78QAAMAAC7jpAAA
         2 ab   AAA78QAAMAAC7kHAAA
         3 ab   AAA78QAAMAAC7jJAAA
        10 ab   AAA78QAAMAAC7gVAAA
         4 ab   AAA78QAAMAAC7Z1AAA
         5 ab   AAA78QAAMAAC7hhAAA
         6 ab   AAA78QAAMAAC7iSAAA
         7 ab   AAA78QAAMAAC7iDAAA
         8 ab   AAA78QAAMAAC7i4AAA
         9 ab   AAA78QAAMAAC7l9AAA
10 rows selected.

4.还可以人为改变输出顺序:
TTT@aaa.bbb.ccc.ddd:1521/orcl> alter system flush buffer_cache;
System altered.

TTT@aaa.bbb.ccc.ddd:1521/orcl> select id,substr(vc,1,2),rowid from  t where rowid='AAA78QAAMAAC7iSAAA';
        ID SUBS ROWID
---------- ---- ------------------
         6 ab   AAA78QAAMAAC7iSAAA

TTT@aaa.bbb.ccc.ddd:1521/orcl> select /*+ index(t) */ id,substr(vc,1,2),rowid from  t where id between 1 and 10;
        ID SUBS ROWID
---------- ---- ------------------
         1 ab   AAA78QAAMAAC7jpAAA
         2 ab   AAA78QAAMAAC7kHAAA
         3 ab   AAA78QAAMAAC7jJAAA
         6 ab   AAA78QAAMAAC7iSAAA
        10 ab   AAA78QAAMAAC7gVAAA
         4 ab   AAA78QAAMAAC7Z1AAA
         5 ab   AAA78QAAMAAC7hhAAA
         7 ab   AAA78QAAMAAC7iDAAA
         8 ab   AAA78QAAMAAC7i4AAA
         9 ab   AAA78QAAMAAC7l9AAA
10 rows selected.
--//id=6的记录先输出了。为什么我不讲解了，留下大家分析。