Advantages and disadvantages of the five ORACLE tables

I. Normal table(Heap table): Suitable for most design scenarios with advantages and disadvantages.

Advantages:
1. Easy syntax
2. Suitable for most scenarios

Disadvantages:
1. Large overhead for updating logs
2. Delete cannot release space (HWM High Water Mark does not drop)
3. The table record is too large. The retrieval is too slow.
4. Large overhead for reading index-return tables
5. Even ordered inserts make it difficult to ensure orderly reading.

Ii. Global temporary table: Suitable for interface Table Design

Advantages:
1. Efficient Deletion
2. generate few logs
3. Different sessions are independent and no locks are generated.

Disadvantages:
1. Special syntax
2. data cannot be effectively protected

Global temporary tables are classified into two types:

One is a session-based global temporary table (on commit preserve rows) and the other is a transaction-based global temporary table (on commit delete rows). The following is an example:

create global temporary table T_TMP_SESSION on commit preserve rows as select * from dba_objects;create global temporary table T_TMP_TRANSACTION on commit delete rows as select * from dba_objects;

3. Partition Table: Especially suitable for log tables, very large tables

Advantages:
1. Effective partition elimination (partition pruning)
2. Efficient record cleanup (that is, You Can truncate a partition)
3. Efficient record transfer (partition switching)

Disadvantages:
1. complex syntax
2. Too many partitions have a certain impact on the system.

Iv. index the Organizational table: Suitable for tables with few updates

Advantages:
1. The table is an index, which can avoid returning to the table.

Disadvantages:
1. complex syntax
2. High update overhead

V. Orders table: Use multiple tables with frequent association queries

Advantages:
1. Sorting can be reduced or avoided

Disadvantages:
1. complex syntax
2. Large table update overhead