Overview of advantages and disadvantages of five tables in ORACLE

  • 2021-11-14 07:24:47
  • OfStack

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

1. Simple grammar
2. Suitable for most scenarios

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

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

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

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)

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

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

1. Complicated syntax
2. Update overhead is high

5. Tables: Multiple tables that use frequent association queries

1. You can reduce or avoid sorting

1. Complicated syntax
2. Table updates are expensive

Related articles: