更新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.