Use Dbms_stats.gather_table_stats to adjust the statistics of a table

Create an experiment table and insert 100,000 rows of data

Sql> CREATE TABLE Test (ID number,name varchar2 (10));

Table created.

Sql> Declare
Begin
For I in 1..100000 loop
INSERT into test values (1, ' a ');
Commit
End Loop;
End
/

PL/SQL procedure successfully completed.

Sql> commit;

Commit complete.

View statistics for a table, empty statistics

Sql> Select table_name, Num_rows,blocks,avg_row_len from User_tables where table_name= ' TEST ';

TABLE_NAME Num_rows BLOCKS Avg_row_len

------------------------------ ---------- ---------- -----------
TEST

Collecting statistics for a table

sql> exec dbms_stats.gather_table_stats (' SCOTT ', ' TEST ');

PL/SQL procedure successfully completed.

Sql> Select Table_name,num_rows,blocks,avg_row_len from user_tables where table_name= ' TEST ';

TABLE_NAME Num_rows BLOCKS Avg_row_len
------------------------------ ---------- ---------- -----------
TEST 100000 244 5

View execution plan and cost for full table scan

Sql> Set Lines pages 300
Sql> explain plan for SELECT * from test;

explained.

Sql> select * FROM table (Dbms_xplan.display ());

Plan_table_output
----------------------------------------------------------------------------------
Plan Hash value:1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100k| 488k| 69 (2) | 00:00:01 |
| 1 | TABLE ACCESS full| TEST | 100k| 488k| 69 (2) | 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

Calculates the number of blocks used to increase the number of rows from 100,000 to 2 million

Sql> Select Round (244*2000000/100000) from dual;

ROUND (244*2000000/100000)
-------------------------
4880

Modifying statistics by using Set_table_stats

sql> exec dbms_stats.set_table_stats (ownname=> ' SCOTT ',tabname=> ' TEST ',numrows=>2000000,numblks=> 4880,AVGRLEN=>5);

PL/SQL procedure successfully completed.

sql> Select Table_name,num_rows,blocks,avg_row_len from user_tables where table_name= ' TEST ';

TABLE_NAME Num_rows BLOCKS Avg_row_len
------------------------------ ---------- ---------- -----------
TEST 2000000 4880 5

View execution plan and cost for full table scan

Sql> explain plan for SELECT * from test;

explained.

Sql> select * FROM table (Dbms_xplan.display ());

Plan_table_output
-------------------------------------------------------------------------------------
Plan Hash value:1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000k| 9765k| 1341 (2) | 00:00:17 |
| 1 | TABLE ACCESS full| TEST | 2000k| 9765k| 1341 (2) | 00:00:17 |
--------------------------------------------------------------------------

8 rows selected.

Use Dbms_stats.gather_table_stats to adjust the statistics of a table