Overview of advantages and disadvantages of five tables in ORACLE

1. Common table (heap table): It is suitable for most design scenarios and has advantages and disadvantages.

Advantages:
1. Simple grammar
2. Suitable for most scenarios

Disadvantages:
1. Updating logs is expensive
2. Delete cannot free space (HWM High Water Mark does not drop)
3. Table record is too large and retrieval is too slow
4. Index back table reading overhead is very high
5. Even if it is inserted orderly, it is difficult to guarantee orderly readout

2. Global Temporary Tables: Suitable for interface table design

Advantages:
Step 1 Delete efficiently
2. Generate very few logs
3. Different SESSION are independent and do not produce locks

Disadvantages:
1. Special grammar
2. Data cannot be effectively protected

Global temporary tables fall into two categories:

One is a session-based global temporary table (on commit preserve rows) and one is a transaction-based global temporary table (on commit delete rows). Here are the examples 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. Partitioned tables: Especially for log tables, very large tables

Advantages:
1. Effective partition elimination (partition clipping)
2. Efficient record cleaning (that is, truncate can be performed on a certain partition)
3. Efficient record transfer (partition switching)

Disadvantages:
1. Complicated syntax
2. Too many partitions have a definite impact on the system

4. Indexed organization tables: Suitable for tables that are rarely updated

Advantages:
1. A table is an index, so you can avoid returning to the table

Disadvantages:
1. Complicated syntax
2. Update overhead is high

5. Tables: Multiple tables that use frequent association queries

Advantages:
1. You can reduce or avoid sorting

Disadvantages:
1. Complicated syntax
2. Table updates are expensive