Update the statistical information of the Oracle table GATHER_TABLE_STATS

The GATHER_TABLE_STATS process of the dbms_stats package is required. The owner and table names must be filled in.

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 ');

 

The table statistics are not obtained in real time. So sometimes the data in the table is deleted and cannot be reflected from num_rows in user_tables in real time. At this time, collect the statistical information of the following table.

 

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

At this time, the number of rows is still 4. We collect statistics.

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

 

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

Restore the deleted data ......

 

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';

Session altered.

SQL>
SQL> select sysdate from dual;

SYSDATE
-------------------
2012-02-28 05:01:49

SQL> select * from d as of timestamp to_timestamp ('2017-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 ('2017-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.