Oracle 表上的高水印

SQL> create table t as select * from dba_objects;

Table created.

SQL> insert into t select * from t;

78473 rows created.

SQL> insert into t select * from t;

156946 rows created.

SQL> insert into t select * from t;

313892 rows created.

SQL> commit;

Commit complete.

SQL> execute dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks from user_tables where table_name = 'T';

    BLOCKS
----------
     12171

1 row selected.

SQL> set autotrace on stat
SQL> select count(*) from t;

  COUNT(*)
----------
    627784

1 row selected.


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      12148  consistent gets
          0  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace off
SQL>
SQL> delete from t where rownum <= 200000;

200000 rows deleted.

SQL> commit;

Commit complete.

SQL>
SQL> execute dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks from user_tables where table_name = 'T';

    BLOCKS
----------
     12171

1 row selected.

SQL> set autotrace on stat
SQL> select count(*) from t;

  COUNT(*)
----------
    427784

1 row selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      12148  consistent gets
          0  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL>
复制