Partition index (2)-the local partition index can use the same partition key and range limit as the table to partition the local index. The partition of each local index only contains the key and ROWID of the table partition associated with it. Local indexes can be B-tree or Bitmap indexes. If it is a B-tree index, it can be a unique or non-unique index. This type of index supports partition independence, which means that you can add, intercept, delete, split, and offline data for individual partitions without deleting or recreating indexes at the same time. Oracle automatically maintains these local indexes. Local index partitions can also be rebuilt separately, but other partitions will not be affected. (1) An index with a prefix and a prefix contains keys from the partition key and uses them as the leading index. For example, let's re-review the table 'delete. After creating the table, use the survey_id and survey_date columns to partition the range, and then create a prefix Local index on the survey_id column. All the partitions of this index are equally divided, that is, all the partitions of the index are created using the same range of the table. Tip: Local prefix indexes allow Oracle to quickly remove unnecessary partitions. That is to say, partitions that do not contain any value in the WHERE Condition Clause will not be accessed, which also improves the statement performance. (2) indexes without prefixes and without prefixes do not use the leading column of the partition key as the leading column of the index. If the same partition table with the same partition key (survey_id and survey_date) is used, the index created on the survey_date column is a local index without a prefix. You can create a local index without a prefix on any column of the table, but each partition of the index only contains the key value of the corresponding partition of the table. If you want to set an index without a prefix as a unique index, this index must contain a subset of the partition key. In this example, we must combine the columns containing survey and (or) survey_id (as long as survey_id is not the first column of the index, it is an index with a prefix ). Tip: For a unique index without a prefix, it must contain a subset of the partition key. (3) Note: 1) the local index must be a partition index, and the partition key is equivalent to the table's partition key. 2) Both prefix and non-Prefix indexes support the elimination of index partitions, provided that the query conditions contain the index partition key. 3) A local index only supports uniqueness in a partition and does not support table uniqueness. Therefore, if you want to use a local index to restrict the uniqueness of the table, the constraint must include the partition key column. 4) The local partition index is for a single partition, with each partition index pointing to only one table partition; 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. truncate, move, and shrink operations on a partition in a partition table may affect n global index partitions, local partition indexes have higher availability. 5) Bitmap indexes must be partial partition indexes. 6) local indexes are mostly used in the data warehouse environment. (4) Example:
sql> create index ix_custaddr_local_id on custaddr(id) local;
The index has been created. The effect is the same as that of the following SQL statement, because the local index is a partition index:
create index ix_custaddr_local_id_p on custaddr(id)local ( partition t_list556 tablespace icd_service, partition p_other tablespace icd_service) SQL> create index ix_custaddr_local_areacode on custaddr(areacode) local;
The index has been created. Verify the types of the two indexes:
SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT fromuser_part_indexes where table_name='CUSTADDR';index_name table_name partition locali alignment------------------------------ ---------- --------- ------ ------------ix_custaddr_local_areacode custaddr list local prefixedix_custaddr_local_id custaddr list local non_prefixed
Because our custaddr table is partitioned by areacode, the index ix_custaddr_local_areacode is prefixed index (prefixed ). Ix_custaddr_local_id is a non-Prefix index.