Shulou(Shulou.com)06/01 Report--

Optimizing it through case study-- the basic concept of Partition Table

Introduction to Partitioning

Partitioning addresses key issues in supporting very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions. SQL queries and DML statements do not need to be modified in order to access partitioned tables. However, after partitions are defined, DDL statements can access and manipulate individuals partitions rather than entire tables or indexes. This is how partitioning can simplify the manageability of large database objects. Also, partitioning is entirely transparent to applications.

Each partition of a table or index must have the same logical attributes, such as column names, datatypes, and constraints, but each partition can have separate physical attributes such as pctfree, pctused, and tablespaces.

Partitioning is useful for many different types of applications, particularly applications that manage large volumes of data. OLTP systems often benefit from improvements in manageability and availability, while data warehousing systems benefit from performance and manageability.

Design principles of partition table

Table size: when the size of the table exceeds 1.5GB-2GB, or for OLTP systems, the table has more than 10 million records, you should consider partitioning the table.

Data access features: most table-based query applications access only a small amount of data in the table. For partitioning such tables, the feature of partitioning excluding extraneous data queries can be fully utilized.

Data maintenance: delete batches of data by time period, such as monthly deletion of historical data. For such tables, you need to consider partitioning to meet the needs of maintenance.

Data backup and recovery: when backing up the tablespace according to the time period, the partition is corresponding to the tablespace.

Read-only data: if most of the data in a table is read-only data, by partitioning the table, the read-only data can be stored in a read-only tablespace, which is very useful for database backup.

Parallel data operations: tables that often perform parallel operations (such as Parallel Insert,Parallel Update, etc.) should be considered for partitioning.

Table availability: when there is a high requirement for partial data availability of the table, table partitioning should be considered.

Type of partition table

Oracle 10g:

Range Partitioning

List Partitioning

Hash Partitioning

Composite Partitioning

RANG-HASH

RANG-LIST

Oracle 11g:

Common views of partition tables

1. Query which partition tables are under the current user:

SELECT * FROM USER_PART_TABLES

2. Query which partition indexes are available under the current user:

SELECT * FROM USER_PART_INDEXES

3. Query the partition information of the partition index under the current user:

SELECT * FROM USER_IND_PARTITIONS T

WHERE T.INDEX_NAME=xxx

4. Query the partition information of the partition table under the current user:

SELECT * FROM USER_TAB_PARTITIONS T

WHERE T.TABLE_NAME=xxx

5. Query the amount of data under a partition:

SELECT COUNT (*) FROM TABLE_PARTITION PARTITION (TAB_PARTOTION_01)

6. Query the index and create partitions on those columns on the table:

SELECT * FROM USER_PART_KEY_COLUMNS

7. Query the information of a user's secondary partition (data is available only if the secondary partition is created):

SELECT * FROM USER_TAB_SUBPARTITIONS

Partition table index

Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes). In general, you should use global indexes for OLTP applications and local indexes for data warehousing or DSS applications. Also, whenever possible, you should try to use local indexes because they are easier to manage. When deciding what kind of partitioned index to use, you should consider the following guidelines in order:

If the table partitioning column is a subset of the index keys, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 2.

If the index is unique, use a global index. If this is the case, you are finished. If this is not the case, continue to guideline 3.

If your priority is manageability, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 4.

If the application is an OLTP one and users need quick response times, use a global index. If the application is a DSS one and users are more interested in throughput, use a local index.

Local index local index

1. The local index must be a partitioned index, the partitioning key is equal to the partitioning key of the table, and the number of partitions is equal to the partitioning of the table. In a word, the partitioning mechanism of the local index is the same as that of the table.

two。 If the index column of a local index begins with a partitioning key, it is called a prefix local index.

3. If the column of a local index does not begin with a partition key, or does not contain a partition key column, it is called a non-prefix index.

4. Both prefix and non-prefix indexes can support index partition elimination as long as the condition of the query contains the index partition key.

5. The local index only supports the uniqueness within the partition, but cannot support the uniqueness on the table, so if you want to use the local index to constrain the uniqueness of the table, the partition key columns must be included in the constraint.

6. The local partition index is for a single partition, each partition index only points to one table partition, while the global index is not, a partition index can point to n table partitions, at the same time, a table partition may also point to n index partitions, doing truncate or move,shrink to a partition in the partition table, etc., may affect n global index partitions, because of this, the local partition index has higher availability.

7. Bitmap indexes can only be locally partitioned.

8. Local index is mostly used in data warehouse environment.

Global index global index

1. The partition key and partition number of the global index and the partition key and partition number of the table may be different, and the partitioning mechanism of the table and the global index is different.

two。 The global index can be partitioned or unpartitioned, and the global index must be a prefix index, that is, the index column of the global index must have the index partitioning key as its first few columns.

3. The index entry of a global partitioned index may point to several partitions, so for a global partitioned index, even if you only move and truncate the data in one partition, you need to rebulid several partitions or even the whole index.

4. Global index is mostly used in oltp system.

5. Global partitioning indexes are only partitioned by range or hash hash, and hash partitions are only supported after 10g.

6. When oracle9i move or truncate the partition table in the future, you can use the update global indexes statement to update the global partition index synchronously, consuming certain resources in exchange for a high degree of availability.

7. The table uses column an as the partition and refers to b as the local partition index. if b is used in the where condition, then oracle will scan the partitions of all tables and indexes, and the cost will be higher than the partition. At this time, you can consider using b as the global partition index.

Partition index dictionary

Summary statistics of DBA_PART_INDEXES partitioned indexes, you can know which partitioned indexes are on each table, and the new class of partitioned indexes (local/global,)

Partition-level statistics for each partition index of Dba_ind_partitions

Dba_indexesminusdba_part_indexes can find out which non-partitioned indexes are on each table.

Index reconstruction

Alter index idx_name rebuild partition index_partition_name [online nologging]

If you need to rebuild each partition index, you can choose online (do not lock the table) when rebuilding, or nologging can speed up by not generating logs when building the index.

Alter index rebuild idx_name [online nologging]

For non-partitioned indexes, only the entire index can be rebuilt

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.