Oracle Database Table fragmentation required

Oracle Database Table fragmentation required

Recently, a mysql user asked me if the table in the Oracle database needs to be split regularly because of the storage of the Oracle data table. Unlike the clustering organization table (IOT) used in mysql (innodb) to store data, it uses a method called HEAP to store data. Data rows are stored in random blocks. the data rows read during full table scan are not sorted in a certain way. therefore, Oracle can insert any data block with free space.

Suppose we create a table now. The process is as follows.

SQL> create table t2 as select * from dba_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats ('sys ', 't2 ');

PL/SQL procedure successfully completed.

Obtain the data block and row information of the current table.

SQL> select table_name, num_rows, blocks, num_rows/blocks from dba_tables where table_name = 't2' and owner = 'sys ';

TABLE_NAME NUM_ROWS BLOCKS NUM_ROWS/BLOCKS

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

T2 90426 1535 59.9094463

Currently, the table has 90426 rows and 1535 data blocks. On average, each block stores about 59 rows of data.

We use the following PLSQL code to randomly Delete 10000 pieces of data;

SQL> declare

I number;

Begin

For I in 1 .. 10000 loop

Delete from t2 where object_id = round (dbms_random.value ));

End loop;

Commit;

End;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats ('sys ', 't2 ');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, blocks, num_rows/blocks from dba_tables where table_name = 't2' and owner = 'sys ';

TABLE_NAME NUM_ROWS BLOCKS NUM_ROWS/BLOCKS

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

T2 81413 1535 53.037785

After 10000 rows are deleted, the table's data blocks remain unchanged, but each block stores 53 rows of data on average.

Now we want to insert data randomly.

SQL> declare

I number;

Begin

For I in 1 .. 10000 loop

Insert into t2 (OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,

GENERATED, SECONDARY, NAMESPACE, EDITION_NAME, SHARING, EDITIONABLE, ORACLE_MAINTAINED)

Values ('sys ',' I _ COBJ # ', null, 30, 30, 'index', to_date ('07-07-2014 05:39:01 ', 'dd-mm-yyyy hh24: mi: ss'), to_date ('07-07-2014 05:39:01 ', 'dd-mm-yyyy

Hh24: mi: ss'), '2017-07-07: 05: 39: 01', 'valid', 'n', 4, null, 'none', null, 'y ');

End loop;

Commit;

End;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats ('sys ', 't2 ');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, blocks, num_rows/blocks from dba_tables where table_name = 't2' and owner = 'sys ';

TABLE_NAME NUM_ROWS BLOCKS NUM_ROWS/BLOCKS

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

T2 91413 1535 59.552443

We can see that Oracle does not allocate a new data block for this table, but it still uses the free space of the original data block. when some original data rows in some blocks are deleted, this part of space can be reused, Which is why oracle tables do not need to be fragmented.