Oracle's partitioned tables and clustered tables

Under what circumstances is a clustered table used? Do you want to compare the performance of the overly-area and clustered tables?

1, the so-called clustering refers to a column as a benchmark, the same cluster key values of all the rows are stored in the same location of the physical storage method. The practice of storing rows with the same clustered values in multiple tables in a contiguous position means that the rows that need to perform table joins are stored together in advance, thereby significantly reducing the price of the data transported when the table is connected.

2, the cluster is divided into two kinds:

(1) cluster types using clustered index to find data are called index clusters

(2) The cluster type that uses hash function to look up data is called hash cluster, also known as hash cluster (note only suitable for "=" Equality comparison operator to read data)

The number of tables can also be divided into:

(1) Tanku cluster: A clustered structure that creates only one table in a specified cluster

(2) Composite table Clustering: A clustered structure that stores two or more tables in a single unit cluster

3. Under what circumstances is a clustered table used?

Single-table clustering solves large scale data processing in massive database.

The composite clustering greatly improves the efficiency of the specific table connection.

Clustering plays a great role in improving the efficiency of data reading, but the cost of inserting, modifying and deleting data is relatively large.

The most important thing for us to use clustering is how to improve the efficiency of data reading by combining the features of each table that will be clustered.

4. Do you compare the performance of the excessive area table and the clustered table?

The performance of these two kinds of tables can not be directly compared to the specific situation of specific analysis:

Features of partitioned tables:

(1) The partition and the partition index can be operated separately

(2) Improve the efficiency of search. Partition cropping function

(3) Partitioning is more conducive to data maintenance. Can be partitioned separately for backup, recovery, and other operations

(4) Partitioning facilitates the expiration of database data.

If it is OLTP, to design a log table, for the log table we have to do a lot of inserts, the query can be a time range, which is suitable for the partition table

If it is OLAP, there are several tables are often connected to check the data, and these data are rarely modified, which is suitable for clustered table

See more highlights of this column: http://www.bianceng.cn/database/Oracle/