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

[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的记录先输出了。为什么我不讲解了，留下大家分析。