I. Ordinary table (Heap table): For most design scenarios, there are advantages and disadvantages.
Advantages:
1. Simple and convenient grammar
2. Suitable for most scenes
Disadvantages:
1. Update log cost is large
2.Delete Unable to free space (HWM high Water mark does not fall)
3. Table record too large retrieval too slow
4. Index back to table read overhead very much
5. Even if the orderly insertion, it is difficult to ensure orderly read out
second, global temporary table : Suitable for interface table design
Advantages:
1. Efficient deletion
2. produce very little log
3. Different session independent, does not produce the lock
Disadvantages:
1. Special grammar
2. Data cannot be effectively protected
Global temporary tables are divided into two categories:
One is a session-based global temporary table (on commit preserve rows), a transactional-based global temporary table (on commit delete rows), and the following is the example 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 deletes rows as select * from Dba_objects;
third, the partition table : Especially suitable for the log table, very large table
Advantages:
1. Effective partition elimination (partition cropping)
2. Efficient record cleaning (that is, a partition can be truncate)
3. Efficient record transfer (partition Exchange)
Disadvantages:
1. Grammatical complexity
2. Too much zoning has a certain impact on the system
Iv. Index Organization table : A table suitable for very few updates
Advantages:
1. Table is the index, you can avoid back to the table
Disadvantages:
1. Grammatical complexity
2. Higher cost of update
The nest table : Multiple tables with frequent associated queries
Advantages:
1. Can reduce or avoid sorting
Disadvantages:
1. Grammatical complexity
2. Table update Cost is large