Optimization of large database tables: Use the tered tables and the tered Index)

Optimization of large database tables: Use the tered tables and the tered Index)

Two-dimensional tables and two-dimensional indexes are a technology provided by oracle. The basic idea is to share several tables with the same data items that are frequently used together through data blocks). The common fields of each table are used as the cluster key. when accessing data, the database first finds the primary key value to obtain relevant data for several tables at the same time. The benefit of using a two-dimensional table is that it can reduce I/O and storage space. I pay more attention to the former. Partition)

Table Partitioning technology divides large tables and their indexes into small and manageable small blocks in the form of partition (patition) in a super large database (VLDB, in addition, each partition can be further divided into smaller sub-partitions ). Such partitions are transparent to applications. Partition a table to obtain the following benefits:

1) reduce the possibility of data corruption.

2) Each partition can be backed up and restored independently, enhancing the manageability of the database.

3) The distribution of partitions on hard disks can be controlled to balance IO and improve database performance.

The two table partitions have different focuses. The former focuses on improving the query efficiency between joined tables, while the former focuses on the manageability of large tables and the performance of local queries. These two items are extremely important to my system. Due to my technical restrictions, I am not sure whether the two can be implemented at the same time. I am very grateful for the experience in providing guidance.

If the two cannot be implemented at the same time, you should choose the desired functions. Combining the advantages and disadvantages of the two modes, I think using Table Partitioning technology is more suitable for our applications.

Oracle table partitions are of the following types:

1) range partition: partitions a table by the value range of a field or several fields.

2) hash partition: evenly distributes a table to several specified partitions based on the value of a field.

3) composite partition: combined with the advantages of the preceding two partition types, the table is partitioned by value range, and data is further evenly distributed to the physical storage location in hash mode.

Taking into account various factors, the third type is the most advantageous. (I actually use only 1st range partitions, because it is relatively simple and easy to manage)

Optimization steps:

1. Determine the table to optimize the partition:

After analyzing the system database table structure, fields, and applications, we can determine which tables need to be partitioned:

For example, the account transaction list acct_detail.

2. Determine the table partition method and partition key:

Partition type: use range partition.

Partition key:

Partitions by trans_date (transaction time.

3. Determine the partition range of the partition key and the partitions to be divided:

For example, the account transaction list acct_detail.

Partition by field (trans_date:

1). Partition/01/2003

2). Partition/01/2003

3). Partition/01/2003

4). Partition 4: 12/01/2003

5). Partition 5: 01/01/2004

6). Shard/01/2004

The table obviously needs to add partitions later.

4. Create Partition Table space and partition index space

1). Create the tablespace of each partition of the table:

1. Partition 1: crm_detail_200309

Create tablespace crm_detail_200309 DATAFILE

'/U1/oradata/orcl/crm_detail_20030901.dbf'

SIZE 2000 m extent management local uniform size 16 M;

Other months and later are the same (I am using the oracle tablespace local management method ).

2) create a partition index tablespace

1. Partition 1: index_detail_200309

Create tablespace index_detail_200309 DATAFILE

'/U3/oradata/orcl/index_detail_20030901.dbf'

SIZE 2000 m extent management local uniform size 16 M;

5. Create a partition-based table:

Create table name

(

........

Enable row movment -- this statement can modify the row partition key value. If this statement is not added, the Record Partition key value cannot be modified, and the record partition cannot be migrated.

Partition by range (TRANS_DATE)

(

PARTITION crm_detail_200309 VALUES LESS

(TO_DATE ('2014/1/123', 'Mm/dd/yyyy'

TABLESPACE crm_detail_200309,

Other partitions .....

;

6. Create a partition-based index:

Create index index_name on table_name (partition key + ...)

Global -- this is a global partition index. You can also create a local index.

Partition by range (TRANS_DATE)

(

PARTITION index_detail_200309 VALUES LESS

(TO_DATE ('2014/1/123', 'Mm/dd/yyyy ')

TABLESPACE index_detail_200309,

Other index partitions...

;

This completes the partition of the table. It is the most important to determine the partition policy of the table partition for the first time, but I think it is difficult to manage the table partition in the future, as the data volume increases, table partitions must be deleted, resized, and increased. Global indexes are also involved in these processes. Because the ddl operation on the partition table destroys the global index, the global index must be re-built after the ddl operation.

Copyright: transfer to http://www.javaeye.com/topic/174264