更新Oracle表的統(tǒng)計信息 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');

表的統(tǒng)計信息不是實時取的。所以有時候剛剛delete掉表中數(shù)據，并不能實時從user_tables中的num_rows反應出來。這時候收集下表的統(tǒng)計信息即可。

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

這時候行數(shù)還是4。我們收集下統(tǒng)計信息。

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

==============================================================================================

還原剛才刪掉的數(shù)據……

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.

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com