Summary of the advantages and disadvantages of ORACLE five types of tables

Summary of advantages and disadvantages of ORACLE five tables:
1. Normal table (heap table): Suitable for most design scenarios, with advantages and disadvantages.

Strengths:
A, the syntax is simple and convenient
b, suitable for most scenes
Disadvantages:
A, the update log cost is large
B,delete Cannot free space (HWM high water mark does not drop)
C, table record too large retrieval too slow
D, index back table read overhead is very large
E, even if inserted in order, it is very difficult to ensure an orderly readout

2, global temporary table: Suitable for interface table design
Strengths:
A, efficient deletion
b, producing very few logs
C, different session independent, no lock generated
Disadvantages:
A, special syntax
b, data cannot be effectively protected
Global temporal tables are divided into two categories:
One is a session-based global temporary table (on commit preserve rows), one is a transaction-based global temporary table (on commit delete rows), the following is a sample created:
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 for log tables, very large tables
Strengths:
A, effective partition elimination (partition clipping)
b, efficient record cleanup (i.e. the ability to truncate a partition)
C, efficient record transfer (partition switching)
Disadvantages:
A, complex syntax
b, too many partitions have a certain impact on the system

4, Index Organization table: A table for very few updates
Strengths:
A, the table is the index, can avoid the back table
Disadvantages:
A, complex syntax
b, the update cost is large

5, Nest table: Multiple tables with frequently associated queries
Strengths:
A, can reduce or avoid sorting
Disadvantages:
A, complex syntax
b, table update cost is large

Summary of the advantages and disadvantages of ORACLE five types of tables