测试表，全表扫描54592一致性读，返回786432行

SQL> select * from p; 786432 rows selected. 

Statistics ----------------------------------------------------------          32  recursive calls           2  db block gets        54592  consistent gets           0  physical reads         528  redo size    21312856  bytes sent via SQL*Net to client      577259  bytes received via SQL*Net from client       52430  SQL*Net roundtrips to/from client           3  sorts (memory)           0  sorts (disk)       786432  rows processed

在表上启用in-database archive

alter table p  row archival; 启用后，表上会多出一个ORA_ARCHIVE_STATE隐藏列 SQL> select substr(column_name,1,22) name, substr(data_type,1,20) data_type, column_id as col_id,   2    segment_column_id as seg_col_id, internal_column_id as int_col_id, hidden_column, char_length   3    from user_tab_cols where table_name='P'; NAME                 DATA_TYPE      COL_ID SEG_COL_ID INT_COL_ID HID CHAR_LENGTH -------------------- ------------- ------- ---------- ---------- --- ----------- ID                   NUMBER              1          1          1 NO            0 NAME                 VARCHAR2            2          2          2 NO          150 SYS_NC00003$         RAW                            3          3 YES           0 ORA_ARCHIVE_STATE    VARCHAR2                       4          4 YES        4000 默认值都是0 SQL> select ORA_ARCHIVE_STATE from p where rownum < 10; ORA_ARCHIVE_STATE ----------------------- 0 0 0 0 0 0 0 0 0 9 rows selected. 归档方式就是更新ORA_ARCHIVE_STATE的值 SQL> update p set  ORA_ARCHIVE_STATE = 20 where id >=100; 393216 rows updated. SQL> commit; Commit complete. 更新后，被归档的行就不可见了，相关数据块不需要被扫描。 SQL>  select * from p; 393216 rows selected. Statistics ----------------------------------------------------------         209  recursive calls           0  db block gets        29413  consistent gets        2907  physical reads           0  redo size    10460172  bytes sent via SQL*Net to client      288905  bytes received via SQL*Net from client       26216  SQL*Net roundtrips to/from client          22  sorts (memory)           0  sorts (disk)       393216  rows processed 对于需要历史数据的统计类SQL，可以在session级别设置归档数据可见 SQL> alter session  set row archival visibility = all; Session altered. SQL> select * from p; 786432 rows selected. Statistics ----------------------------------------------------------           5  recursive calls           0  db block gets        55322  consistent gets           0  physical reads           0  redo size    21312856  bytes sent via SQL*Net to client      577259  bytes received via SQL*Net from client       52430  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)       786432  rows processed