Check table status with SYS.DBMS_STATS.gather_table_stats after adding index : DBMS_STATS « System Packages « Oracle PL/SQL Tutorial






SQL>
SQL> CREATE TABLE employee_evaluation (
  2  id            number,
  3  title         varchar2(100),
  4  written_date  date );

Table created.

SQL>
SQL>
SQL>
SQL> INSERT INTO employee_evaluation(id,title,written_date)VALUES(100,'SQL',to_date('19700101', 'YYYYMMDD') );

1 row created.

SQL> INSERT INTO employee_evaluation(id,title,written_date)VALUES(100,'Java',to_date('19900101', 'YYYYMMDD') );

1 row created.

SQL> INSERT INTO employee_evaluation(id,title,written_date)VALUES(200,'C++',to_date('20030101', 'YYYYMMDD') );

1 row created.

SQL> INSERT INTO employee_evaluation(id,title,written_date)VALUES(200,'C',to_date('20000101', 'YYYYMMDD') );

1 row created.

SQL> INSERT INTO employee_evaluation(id,title,written_date)VALUES(200,'Oracle',to_date('20020101', 'YYYYMMDD') );

1 row created.

SQL> INSERT INTO employee_evaluation(id,title,written_date)VALUES(200,'Theory',to_date('20050101', 'YYYYMMDD') );

1 row created.

SQL> INSERT INTO employee_evaluation(id,title,written_date)values(300,'Third',to_date('20000101', 'YYYYMMDD')
  2  INSERT INTO employee_evaluation(id,title,written_date)values(300,'Data',to_date('20020101', 'YYYYMMDD')
  3
SQL>
SQL> CREATE INDEX employee_evaluation_k1
  2  on           employee_evaluation (
  3  title );

Index created.

SQL>
SQL>EXEC SYS.DBMS_STATS.gather_table_stats(USER, UPPER('employee_evaluation'));
SQL>
SQL>
SQL> drop table employee_evaluation;

Table dropped.

SQL>
SQL>








31.27.DBMS_STATS
31.27.1.execute SYS.DBMS_STATS.gather_table_stats(USER, 'employee_job');
31.27.2.Check table status with SYS.DBMS_STATS.gather_table_stats after adding index
31.27.3.Create a procedure to collect statistics on all my objects
31.27.4.dbms_stats.drop_stat_table, dbms_stats.create_stat_table,dbms_stats.delete_system_stats
31.27.5.dbms_stats.gather_schema_stats
31.27.6.dbms_stats.gather_table_stats
31.27.7.dbms_stats.import_system_stats
31.27.8.dbms_stats.objectTab
31.27.9.dbms_stats.set_table_stats( user, 'EMP', numrows => 100000 )
31.27.10.dbms_stats.set_table_stats( user, 'myTable1', numrows => 10000000, numblks => 1000000 )