[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的記錄先輸出了。為什麼我不講解了，留下大家分析。