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

According to the type, the partitioned table can be divided into range partition (Range), list partition (List) and hash partition (Hash). After the table is partitioned, the corresponding index will be different from that of the ordinary table.

Basic concepts can be divided into two categories for indexes on partitioned tables: local indexes and global indexes. The global index can be divided into partitioned index and unpartitioned index, and the local index must be partitioned index. Therefore, if an index is not partitioned, it can be directly determined to be a global index; if it is partitioned, it can be judged according to the specific situation. The official screenshot is as follows:

This article focuses on the basic concepts and principles of Local Partitioned Index (which is written here to match the above figure, which can be abbreviated to Local Index, because Local Index must be partitioned) and the corresponding scenarios in which they are used.

The partitioning mechanism of the local index is the same as that of the table: the partitioning key of the local index is the same as that of the partitioned table, and the number of partitions is the same as that of the base table, so the partitioning of the local index is exactly the same as that of the base table.

The global partition index partition key and the number of partitions may be different from the base table, and the global partition index must be a prefix index, that is, its index partition key must be the first few columns of the index.

The textual description is boring and difficult to understand. The following picture vividly illustrates the two and the difference between the two.

Local Index global Partition Index

Application scenarios so what scenarios are local indexes and global partitioned indexes or global indexes suitable for? Take local index and global index (non-partitioned) as examples:

1. Create a new test range partition table T_PARTITION with ID as the partition key. The specific table structure is as follows:

Desc t_partition

Name Null? Type

-

ID NUMBER

DATA VARCHAR2 (100)

T_PARTITION creates 10 partitions each with column ID intervals of 100, as follows

Click (here) to collapse or open

SQL > SELECT count (*) FROM user_tab_partitions WHERE table_name='T_PARTITION'

COUNT (*)

-

The data volume of 1000T_PARTITION is as follows:

Click (here) to collapse or open

SQL > SELECT count (*) FROM t_partition

COUNT (*)

-

110300392. Create a local index and a global index in the DATA column.

Local Index:

Click (here) to collapse or open

Create index ind_l_data on t_partition (data) local;Index created. After the creation is complete, check the details of the index with the following statement, and you can see from the output that the local index is the same as the base table partition as mentioned earlier.

Click (here) to collapse or open

SELECT table_name,locality,alignment FROM user_part_indexes WHERE index_name='IND_L_DATA'

TABLE_NAME LOCALITY ALIGNMENT

T_PARTITION LOCAL NON_PREFIXED

SELECT count (*) FROM user_ind_partitions t WHERE index_name='IND_L_DATA'

COUNT (*)

-

1000 Global Partition Index:

Click (here) to collapse or open

Drop index ind_l_data;create index ind_g_data on test_partition (data); Index created.3. Compare local and global indexes

There are two situations to compare. In one case, the query predicate has the partition key of the base table, and the other does not have the partition key of the base table. Let's first look at the condition without the partition key:

Local Index:

Global Index:

From the statistics output from the above two figures, we can see that the logical read (consistent gets) global index is significantly less logical read than the local index, and the global index is more efficient.

Let's take another look at the predicate with a partitioning key:

Local Index:

Global Index:

There is little difference, relatively speaking, the performance of local indexes is better than that of global indexes.

The global index and the local index have their own characteristics, and the usage scenarios are different. DBA should create the corresponding index according to the specific business situation. In addition, you can find some suggestions on the local index and the global index from the Oracle official:

It is inferred that the global index is mostly used in the OLTP system, while the local index is used in the OLAP system.

Maintenance precautions 1. The local index can be a prefix index and a non-prefix index, and the global index must be a prefix index. As follows, our global index is created in the data column, partitioned through the id column, and the system reports an error GLOBAL partitioned index must be prefixed.

two。 As long as the query condition contains the partition key of the base table, both non-prefix and prefix indexes support partition elimination.

3. The local index is automatically managed by Oracle. When there are operations such as adding, deleting, merging partitions and truncating partition data, the local index will be updated automatically, but the global index will not be updated automatically. Once you have the above operation, please remember to add the update global indexes statement, if you do not add the index, it will become unavailable.

Local Index:

Global Index:

You can see that if you do not add update global indexes, the global index state becomes unavailable, so pay attention to this issue during the daily maintenance of the global index.

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.