更新Oracle表的统计信息 GATHER_TABLE_STATS
需要用到dbms_stats这个包的GATHER_TABLE_STATS过程，其中拥有者和表名必须填。
PROCEDURE GATHER_TABLE_STATS
Argument Name
Type
In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME
VARCHAR2
IN
TABNAME
VARCHAR2
IN
PARTNAME
VARCHAR2
IN
DEFAULT
ESTIMATE_PERCENT
NUMBER
IN
DEFAULT
BLOCK_SAMPLE
BOOLEAN
IN
DEFAULT
METHOD_OPT
VARCHAR2
IN
DEFAULT
DEGREE
NUMBER
IN
DEFAULT
GRANULARITY
VARCHAR2
IN
DEFAULT
CASCADE
BOOLEAN
IN
DEFAULT
STATTAB
VARCHAR2
IN
DEFAULT
STATID
VARCHAR2
IN
DEFAULT
STATOWN
VARCHAR2
IN
DEFAULT
NO_INVALIDATE
BOOLEAN
IN
DEFAULT
STATTYPE
VARCHAR2
IN
DEFAULT
FORCE
BOOLEAN
IN
DEFAULT
exec dbms_stats.gather_table_stats('SCOTT','D');
表的统计信息不是实时取的。所以有时候刚刚delete掉表中数据，并不能实时从user_tables中的num_rows反应出来。这时候收集下表的统计信息即可。
SQL> select table_name ,num_rows from user_tables where table_name='D';
TABLE_NAME NUM_ROWS
--------------- ----------
D
4
SQL> select * from d;
DEPTNO DNAME
---------- ----------------------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
SQL> delete from d where deptno=30;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from d;
DEPTNO DNAME
---------- ----------------------------
10 ACCOUNTING
20 RESEARCH
40 OPERATIONS
SQL> select table_name ,num_rows from user_tables where table_name='D';
TABLE_NAME NUM_ROWS
--------------- ----------
D
4
这时候行数还是4。我们收集下统计信息。
SQL> exec dbms_stats.gather_table_stats('SCOTT','D');
PL/SQL procedure successfully completed.
SQL> select table_name ,num_rows from user_tables where table_name='D';
TABLE_NAME NUM_ROWS
--------------- ----------
D
3
==============================================================================================
还原刚才删掉的数据……
SQL> alter session set nls_date_format ='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> SQL>
SQL> select sysdate from dual;
SYSDATE
-------------------
2012-02-28 05:01:49
SQL> select * from d as of timestamp to_timestamp('2012-02-28 04:50:00','yyyy-mm-dd hh24:mi:ss') where deptno=30;
DEPTNO DNAME
---------- ----------------------------
30 SALES
SQL> insert into d select * from d as of timestamp to_timestamp('2012-02-28 04:50:00','yyyy-mm-dd hh24:mi:ss') where deptno=30;
1 row created.
SQL> select * from d;
DEPTNO DNAME
---------- ----------------------------
10 ACCOUNTING
20 RESEARCH
40 OPERATIONS
30 SALES
SQL> commit;
Commit complete.
