Overview of the advantages and disadvantages of Oracle's five kinds of tables _oracle

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