Shulou(Shulou.com)06/01 Report--

1. Establish initialization data to demonstrate the change of high water level in the data table.

(1), create users and tables

SYS@odb > create user test8 identified by test8

SYS@odb > grant connect,resource to test8

SYS@odb > conn test8/test8

TEST8@odb > create table gaoshuiwei as select * from all_objects

TEST8@odb > select TABLE_NAME,NUM_ROWS,BLOCKS from user_tables where table_name='GAOSHUIWEI'

TABLE_NAME NUM_ROWS BLOCKS

GAOSHUIWEI 68309 500

TEST8@odb > delete from gaoshuiwei

TEST8@odb > commit

TEST8@odb > select count (*) from gaoshuiwei

COUNT (*)

-

0

TEST8@odb > select TABLE_NAME,NUM_ROWS,BLOCKS from user_tables wheretable_name='GAOSHUIWEI'

TABLE_NAME NUM_ROWS BLOCKS

GAOSHUIWEI 68309 500

You can see that after deleting the data in the table, the table still has 68309 rows and about 8m faster in user_tables.

(2) insert data at the second time

TEST8@ odb > insert intogaoshuiwei select * from all_objects

TEST8@ odb > commit

TEST8@ odb > select count (*) fromgaoshuiwei

COUNT (*)

-

68309

TEST8@ odb > selectTABLE_NAME,NUM_ROWS,BLOCKS from user_tables where table_name='GAOSHUIWEI'

TABLE_NAME NUM_ROWS BLOCKS

GAOSHUIWEI 68309 500

TEST8@ odb > delete fromgaoshuiwei

68309 rows deleted.

TEST8@ odb > commit

Commit complete.

TEST8@ odb > selectTABLE_NAME,NUM_ROWS,BLOCKS from user_tables where table_name='GAOSHUIWEI'

TABLE_NAME NUM_ROWS BLOCKS

GAOSHUIWEI 68309 500

Seeing that the information has not changed, we need to collect statistical information.

TEST8@odb >

Execdbms_stats.gather_table_stats ('TEST8','GAOSHUIWEI',cascade= > true)

PL/SQL procedure successfullycompleted.

TEST8@ odb > selectTABLE_NAME,NUM_ROWS,BLOCKS from user_tables where table_name='GAOSHUIWEI'

TABLE_NAME NUM_ROWS BLOCKS

GAOSHUIWEI 0 500

TEST8@ odb > selectsegment_name,segment_type,bytes from user_segments wheresegment_name='GAOSHUIWEI'; / / View segment information

SEGMENT_NAME SEGMENT_TYPE BYTES

GAOSHUIWEI TABLE 8388608

Block 500 is about 8m to generate an execution plan:

TEST8@odb > explain plan for select count (*) from gaoshuiwei

Explained.

TEST8@odb > select * from table (dbms_xplan.display)

The table is 0 rows of data, full table scan, cost 189. the reason is that because the table inserts and deletes back and forth, the high water level line does not fall, and 500blocks will be scanned, all of which will return results. If you find that the data in the table is small, but the query is slow, it may be related to the high water level:

Can be used

Method (1) use truncate method

Method (2), the method of using mobile tablespace

Method (3), the method of using shrinking table space

TEST8@ odb > alter table gaoshuiwei enablerow movement; / / enable Line Mobility

TEST8@ odb > alter table gaoshuiwei shrinkspace compact; / / compress the space of the table, which hasn't changed yet.

When TEST8@ odb > alter table gaoshuiwei shrinkspace; / / lowers the high water mark of the meter, the change is more obvious.

The same operation can be set for the index.

Alter indexPUB_LOG_PK shrink space compact

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.