Verify heap table Storage

Heap table Storage:

• In the Oracle database system, the most common is heap tables.
• The data storage mode of the heap table is unordered, that is, any DML operation may make the current data block available free space.
• In consideration of space saving, the available free space on the block will be filled by the newly inserted rows instead of the last block to be used.
• The above operations lead to the generation of data with no sequence.
• When an index is created, the index block is filled according to the specified columns in order. By default, the index is in ascending order.
• When creating or recreating an index, the order of the index column is ordered, and the order of the table is unordered, that is, there is a difference, that is, the result is the clustering factor.

Verification:

1. Create a table

SQL> conn scott/tiger

Connected.

SQL> create table t1

2 (a int,

3 B varchar2 (4000) default rpad ('*', 4000 ,'*'),

4 c varchar2 (3000) default rpad ('*', 3000 ,'*')

5)

6/

Table created.

 

SQL> desc t1

Name Null? Type

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

A number (38)

B VARCHAR2 (4000)

C VARCHAR2 (3000)

2. Insert data

SQL> insert into t1 (a) values (1 );

1 rows created;

SQL> insert into t1 (a) values (2 );

1 rows created;

SQL> insert into t1 (a) values (3 );

1 rows created;

SQL> select a from t1;

A

----------

1

2

3

3. delete a row of data

SQL> delete from t1 where a = 2;

1 row deleted.

SQL> select a from t1;

A

----------

1

3

4. Insert a record again.

SQL> insert into t1 (a) values (4 );

1 row created.

SQL> select a from t1;

A

----------

1

4

3

As can be seen from the above, data insertion is not in order, but the space for the original deleted record is used!

Oracle online redefinition (converting normal heap tables into partitioned tables)