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

In this issue, the editor will bring you about the difference between global index and local index in Oracle. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Record the difference between global index and local index

The table can be partitioned by range,hash,list. After partitioning, the index on the table is different from that on the ordinary table. Oracle divides the indexes on the partitioned table into two categories, namely, local index and global index. The following summarizes the characteristics and limitations of these two indexes. 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 to truncate the data in one partition, you need to rebulid several partitions or even the entire 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 references 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 DBA_PART_INDEXES partition index summary statistics, you can know which partition indexes are on each table. The new class of partitioned indexes (local/global,) Dba_ind_partitions the partition-level statistics of each partitioned index Dba_indexesminusdba_part_indexes, you can find out which non-partitioned indexes are rebuilt on each table Alter index idx_name rebuild partition index_partition_name [online nologging] you need to rebuild each partitioned index, you can choose online when rebuilding (do not lock the table), or nologging does not generate logs when building the index, so speed up. Alter index rebuild idx_name [online nologging] for non-partitioned indexes, only the entire index can be rebuilt. This is the difference between global index and local index in Oracle shared by the editor. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.