Oracle High-level test example

First, related testing

(1) Create a test table

Sql> CREATE TABLE TT (ID number);

Table created.

At this point, the table is not parsed, it is the original data, that is, 8 blocks of data.

Sql>select segment_name,segment_type,blocks from dba_segments WHERE segment_name= ' TT ';

Segment_name Segment_type BLOCKS

--------------- --------------- ----------

TT TABLE 8

Sql> SELECT table_name,num_rows,blocks,empty_blocks from user_tables WHERE table_name= ' TT ';

TABLE_NAME Num_rows BLOCKS Empty_blocks

--------------- ---------- ---------- ------------

Tt

(2) Insert some test data into the table

Sql> Declare

2 I number;

3 begin

4 for I in 110000 loop

5 INSERT INTO TT values (i);

6 end Loop;

7 commit;

8 End;

9/

Pl/sql procedure successfully completed.

(3) At the time to view the table information

Sql> SELECT table_name,num_rows,blocks,empty_blocks from user_tables WHERE table_name= ' TT ';

TABLE_NAME Num_rows BLOCKS Empty_blocks

--------------- ---------- ---------- ------------

Tt

Sql> SELECT segment_name,segment_type,blocks from dba_segments WHERE segment_name= ' TT ';

Segment_name Segment_type BLOCKS

--------------- --------------- ----------

TT TABLE 24

At this point, the table TT occupies a database of 24. However, the information displayed by User_tables is still empty. Because no statistical analysis was done.

(4) Collecting statistical information

Sql> exec dbms_stats. Gather_table_stats (' SYS ', ' TT ');

Pl/sql procedure successfully completed.

Sql> SELECT segment_name,segment_type,blocks from dba_segments WHERE segment_name= ' TT ';

Segment_name Segment_type BLOCKS

--------------- --------------- ----------

TT TABLE 24

Sql> SELECT table_name,num_rows,blocks,empty_blocks from user_tables WHERE table_name= ' TT ';

TABLE_NAME Num_rows BLOCKS Empty_blocks

--------------- ---------- ---------- ------------

TT 10000 20 0

At this point, the user_tables already has data, and the display uses 20 blocks of data. But the empty_blocks is still empty. Here to pay attention to the place. This field does not have data until it collects statistics using analyze.