SQL Tuning Basic Overview 04-oracle table types and introduction

Tables

A table describes an entity such as employees. You define a table with a table name, such as employees, and set of columns. In general, you give each column a name, a data type, and a width when you create the table.

1. Ordinary heap table (heap-organized Tables)

For all scenarios, applications with specific scenarios have a performance bottleneck, and alternative types of tables need to be replaced depending on the scenario.

2. Global temp Table (temporary Tables)

Oracle Database Temporary tables hold data is exists only for the duration of a transaction or session.

Advantages:

①insert, UPDATE, delete operation produces less redo than the ordinary table;

② automatically delete data without generating redo;

③ is based on session isolation.

disadvantage: Since the deletion of data does not produce redo advantages, the relative derivation of the data can not effectively protect the shortcomings.

3. Partition table (partitioned Tables)

Common types of partitions: Range,list,range-list,range-hash

Advantages:

① Partition elimination: The precondition is that the partition condition can be used rationally;

② Historical Data Quick cleanup: ALTER TABLE t truncate partition P1;.

Disadvantages:

① too many partitions also increase overhead, and in general, tables above the million data level are considered for partitioning.

② too many partitions also result in a workload on partition maintenance.

4. Index Organization table (index-organized Tables)

The table itself is the index, and the index itself is the table. For configuration tables that are rarely updated.

Pros: eliminate back tables and apply very few updates to the configuration table.

Cons: because of the structure particularity, the update overhead is very big.

5. Cluster table (table Cluster)

Advantage: ordered cluster tables can eliminate order by ordering.

Cons: because of the structure particularity, the update overhead is very big. At the same time, because the row sequence to establish the index can also eliminate the exclusion, so the cluster table is rarely used.

6. External table (External Tables)

In the database, only the definition of the table structure, the real data is not stored in the database, generally applied in the scene of loading data.

reference book: receiving, more than Oracle, "Smudge Oracle" "Concepts"

SQL Tuning Basic Overview 04-oracle table types and introduction