Oracle table fragmentation causes and solutions

Today we found that indexes are not used in an SQL query, and classid is used to create indexes, as shown in the following figure: select * frominfobasewhereclassidin (10001,10002, 10003,10

Today we found that indexes are not used in an SQL query. classid is used to create an index, as shown in the following figure: select * from infobase where classid in (10001,10002, 10003,10

Today we found that indexes are not used in an SQL query, and classid is used to create indexes, as shown below:
Select * from infobase where classid in (10005 );
It is strange to find that no index is used for the classid in (,) value or more, and the two or the following values can be used. It is suspected that the index is faulty, as a result, the index on the next classid cannot be rebuilt. Find an article on the Internet to know that the table may contain fragments.
Use the following steps to solve the problem:
1. Recreate the table:
Create table infobase2 select * from infobase;
2. Change the previous table name:
Alter table infobase rename to infobase3;
3. Change the name of the new table to the previous one:
Alter table infobase2 rename to infobase;
4. Create an index:
Create index classid_ind on infobase (classid );

After a day, the problem occurs again, and the index cannot be used again. Then, execute the following statement to solve the problem:
Analyze table infobase compute Statistics;
Or
Analyze table infobase estimate statistics sample 50 PERCENT;
-- Note: if the value of 50 PERCENT is too small, the index may still not work. When I started to use 20 PERCENT, the index was still unavailable.

Basic knowledge related to table fragments:
What is a High Water Mark )?
----------------------------
All the Oracle segments (segments here, for ease of understanding, we recommend that you use segment as a synonym for the table) have an upper limit to accommodate data within the segments, we call this upper limit "high water mark" or HWM. This HWM is a tag used to indicate how many unused data blocks have been allocated to this segment. HWM usually increases by five data blocks at a time. In principle, HWM only increases and does not shrink. Even if all the data in the table is deleted, HWM is the original value, this makes HWM very similar to the historical highest water level of a reservoir, which is the original meaning of HWM. Of course, it cannot be said that a reservoir has no water, but the historical highest water level of the reservoir is 0. However, if we use the truncate command on the table, the HWM of the table will be reset to 0.

HWM database operations have the following effects:
A) Full table scan usually reads all the database blocks in the table until the HWM mark, even if the table does not have any data.
B) even if there are idle database blocks below HWM AND THE append keyword is used for data insertion, the data blocks above HWM are used for data insertion, And the HWM will automatically increase.

How do I know the HWM of a table?
A) analyze the table first:
ANALYZE TABLE ESTIMATE/compute statistics;
B) SELECT blocks, empty_blocks, num_rows
FROM user_tables
WHERE table_name = ;

The BLOCKS column indicates the number of database BLOCKS used in the table, that is, the waterline.
EMPTY_BLOCKS indicates the database block that is allocated to the table but above the waterline, that is, the data block that has never been used.

Let's take a BIG_EMP1 table with 28672 rows as an example:
1) SQL> SELECT segment_name, segment_type, blocks
FROM dba_segments
WHERE segment_name = 'Big _ emp1 ';
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
---------------------------------------------------------------
BIG_EMP1 TABLE 1024 2
1 row selected.

2) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

3) SQL> SELECT table_name, num_rows, blocks, empty_blocks
FROM user_tables
WHERE table_name = 'Big _ emp1 ';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
--------------------------------------------------------------
BIG_EMP1 28672 700 323
1 row selected.

Note:
BLOCKS + EMPTY_BLOCKS (700 + 323 = 1023) has fewer database BLOCKS than DBA_SEGMENTS.BLOCKS because a database block is reserved as the segment header. DBA_SEGMENTS.BLOCKS indicates the number of all database blocks allocated to this table. USER_TABLES.BLOCKS indicates the number of used database blocks.

4) SQL> SELECT COUNT (DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) |
DBMS_ROWID.ROWID_RELATIVE_FNO (rowid) "Used"
FROM big_emp1;
Used
----------
700
1 row selected.

5) SQL> DELETE from big_emp1;
28672 rows processed.

6) SQL> commit;
Statement processed.

7) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

8) SQL> SELECT table_name, num_rows, blocks, empty_blocks
FROM user_tables
WHERE table_name = 'Big _ emp1 ';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
--------------------------------------------------------------
BIG_EMP1 0 700 323
1 row selected.

9) SQL> SELECT COUNT (DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) |
DBMS_ROWID.ROWID_RELATIVE_FNO (rowid) "Used"
FROM big_emp1;
Used
----------
0 -- the table name does not contain any database block to accommodate data, that is, the table does not have data.
1 row selected.

10) SQL> TRUNCATE TABLE big_emp1;
Statement processed.

11) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.

12) SQL> SELECT table_name, num_rows, blocks, empty_blocks
FROM user_tables
WHERE table_name = 'Big _ emp1 ';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
--------------------------------------------------------------
BIG_EMP1 0 0 511
1 row selected.

13) SQL> SELECT segment_name, segment_type, blocks
FROM dba_segments
WHERE segment_name = 'Big _ emp1 ';
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
---------------------------------------------------------------
BIG_EMP1 TABLE 512 1
1 row selected.

Note:
The TRUNCATE command recycles the free space generated by the delete command. Note that the space allocated to the table is reduced from the original 1024 blocks to 512 blocks.
To retain the free space generated by the delete command, you can use
Truncate table big_emp1 REUSE STORAGE
After using this command, the table will still be the first 1024 blocks.