Oracle programming Art Study Notes (22)-table Type

 

There are nine types of tables in Oracle:

1) heap organization table)

This is the standard database table. Data is managed in heap mode. When adding data, the first free space found in the segment can be used to put the data down. After the data is deleted from the table, this part of space can be reused in INSERT and UPDATE operations. Heap is a set of spaces used in a random manner.

2) index organization table)

Tables are stored by index structure. This forces the row to have a physical order. In the heap organization table, data can be stored anywhere. In IOT, data must be stored in an orderly manner according to the primary key.

 

3) index Cluster table)

A cluster is a group composed of one or more tables. These tables are physically stored in the same database block, all rows with the same cluster key value are physically stored adjacent to each other.

First, multiple tables can be physically stored together. Generally, you can think that a database block stores the data of a table, but for clustering tables, the data of multiple tables may be stored on the same block.

Second, all data containing the same cluster key value (such as DEPTNO = 10) is physically stored together. The data is grouped together by the clustering key value "clustering. The cluster key is created using the B * tree index.

 

4) hash clustered table)

Similar to the index clustering table, instead of using the B * tree index clustering key to locate data, the data is hashed to the database block through the hash clustering key. In a hashed cluster, data is an index (this is a metaphor ). If you need to read data frequently through key equality comparison

Clustering tables are very suitable.

 

5) sorted hash clustered table)

New in Oracle 10 Gb, which has the characteristics of clustering tables and IOT.

Rows are hashed by a key value (such as CUSTOMER_ID), and a series of records related to the key are stored in the insert sequence. (Therefore, these records may be timestamp-based records ).

For example, in the order Input System, the order is obtained and processed in FIFO mode. In such a system, ordered clustering is the applicable data structure.

 

6) nested table)

Nested tables are part of Oracle object link extension. They are actually child tables in the parent/child relationship generated and maintained by the system.

 

7) temporary table (temporary table)

The temporary table should be allocated with temporary partitions from the temporary tablespace of the current user as needed. Each session can only see the partition allocated by this session; it will never see any data created in any other session.

 

8) object table)

An object table is created based on an object type. They have special properties not available in the object table. For example, the system generates a REF (object identifier) for each row of the object table ).

An object table is a special case of heap organization tables, index organization tables, and temporary tables. It can also contain nested tables as part of its structure.

 

9) external table)

These tables are not stored in the database, but stored outside the database, that is, in common operating system files.

You can use an external table to query a file other than the database, as if this file is also a common table in the database. External tables are most useful for loading data to databases (External tables are very powerful data loading tools ).

Oracle 10 Gb further introduces an External table detachment function, which provides a simple way to move data between Oracle databases without using database links.

 

Author: NowOrNever