dbms_stats.set_table_stats( user, 'myTable1', numrows => 10000000, numblks => 1000000 ) : DBMS_STATS « System Packages « Oracle PL/SQL Tutorial

SQL>
SQL>
SQL> create table myTable1
  2  as
  3  select *
  4    from all_users
  5   where 1=0;



SQL>
SQL> create index myTable1_username_idx on myTable1(username);



SQL>
SQL> create table myTable2
  2  as
  3  select *
  4    from all_objects
  5   created;



SQL>
SQL> exec dbms_stats.set_table_stats( user, 'myTable1', numrows => 10000000, numblks => 1000000 );

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.set_table_stats( user, 'myTable2', numrows => 10000, numblks => 1000 );

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly explain
SQL> select myTable1.username, sum(myTable2.object_id)
  2    from myTable1, myTable2
  3   where myTable1.username = myTable2.owner (+)
  4   group by myTable1.username
  5  /
SQL>
SQL> set autotrace off
SQL>
SQL> drop table myTable1;
SQL> drop table myTable2;

Table dropped.

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 )