Summary of advantages and disadvantages of five ORACLE tables

Summary of the advantages and disadvantages of the five ORACLE tables:
1. Normal table: Suitable for most design scenarios, with advantages and disadvantages.
Advantages:
A. The syntax is simple and convenient.
B. Suitable for most scenarios
Disadvantages:
A. the overhead of updating logs is high.
B. Delete cannot release space (HWM High Water Mark does not drop)
C. The table record is too large. The retrieval is too slow.
D. Large overhead for reading index-return tables
E. Even ordered inserts make it difficult to ensure orderly reading.

2. Global temporary table: Suitable for interface Table Design
Advantages:
A. Efficient Deletion
B. Few logs are generated.
C. Different sessions are independent and no locks are generated.
Disadvantages:
A. Special syntax
B. 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 and very large tables
Advantages:
A. Effective partition elimination (partition pruning)
B. Efficient record cleanup (that is, You Can 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: Suitable for tables with very few updates
Advantages:
A. The table is an index, which can avoid returning to the table.
Disadvantages:
A. complex syntax
B. Large update overhead

5. Orders table: Multiple tables with frequent association queries
Advantages:
A, which can reduce or avoid sorting
Disadvantages:
A. complex syntax
B. Large table update overhead