12c in-database archive

測試表，全表掃描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