Oracle global index and Local index

Oracle global indexes and Local index tables can be partitioned by range, hash, list, and table partitions. After partitioning, the indexes on these indexes are different from those on normal tables, oracle has two types of indexes for Partitioned Tables: local indexes and global indexes. The following describes the features and limitations of these two indexes. Local index 1. the local index must be a partition index. The partition key is equivalent to the table's partition key, and the number of partitions is equivalent to the table's partition. In a word, the partition mechanism of the Local index is the same as that of the table. 2. If the index column of a local index starts with a partition key, it is called a prefix Local index. 3. If the column of a local index does not start 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 support the elimination of index partitions, provided that the query conditions contain the index partition key. 5. Local indexes only support uniqueness in partitions and cannot support table uniqueness. Therefore, if you want to use a local index to restrict the uniqueness of a table, the constraint must include the partition key column. 6. the partial partition index is for a single partition. Each partition index points to only one table partition, while the global index is not. One partition index can point to n table partitions. At the same time, one table partition, it may also point to n INDEX partitions. truncate, move, and shrink operations on a partition in the Partition Table may affect n global index partitions, local partition indexes have higher availability. 7. Bitmap indexes can only be partial partition indexes. 8. Local indexes are mostly used in the data warehouse environment. Global index 1. The partition key and number of partitions of the global index are different from those of the table. The partition mechanism of the table and global index is different. 2. The global index can be partitioned or non-partitioned. The global index must be a prefix index, that is, the index column of the global index must use the index partition key as the first few. 3. the index entries of the global partition index may point to several partitions. Therefore, for a global partition index, even if it only moves, data in one partition is truncated, you need to rebulid Several partitions even the entire index. 4. Global indexes are mostly used in oltp systems. 5. The global partition index is only supported by the range or hash partition. The hash partition is 10 Gb later. 6. When moving or truncate the partition table after oracle9i, you can use the update global indexes statement to synchronously update the global partition index, which consumes a certain amount of resources in exchange for high availability. 7. the table uses column a as a partition, and the index uses Column B as a partial partition index. If Column B is used for query in the where condition, oracle scans all the tables and index partitions, resulting in a higher cost score, in this case, you can consider using B for the summary statistics of the global partition index dictionary DBA_PART_INDEXES partition index. You can find out which partition indexes are available on each table and the new partition Index Class (local/global ,) dba_ind_partitions partition-level statistical information for each partition index. You can obtain which non-partition index indexes are available on each table. Rebuilding Alter index idx_name rebuild partition index_partition_name [online nologging] requires rebuild for each partition index, during Reconstruction, you can select online (the table will not be locked), or do not generate logs when nologging creates an index to speed up the process. Alter index rebuild idx_name [online nologging] for non-partition indexes, only the entire index can be rebuilt