ORA-01502 two forms of the Oracle Database index index

In the current project, when we run the batch, there is an SP always error "Ora-01502:index ' wbill_102.pk_a_notweb_act_province_m ' or partition of such index is in U NusableState ", the solution is to rebuild the index, ALTER index IN_AA REBUILD;

But after the settlement, in running the batch or reported this error, which let me need to consider, the cause of this problem is what!

My error scenario is restored:

There is a table cus_asset_month, the primary key is Pk_cus_asset_month, the primary key field is cus_code, this table is the partition table, the partition field is Data_ym

In order for our batch to have a re-run function, the first step of the batch is to delete this month's data:

ALTER TABLE cus_asset_month TRUNCATE PARTITION pa_cus_asset_month_201412;

Then insert in the table Cus_asset_month, this time will be an error ORA-01502

Scene Restore Complete!

Problem Analysis:

The index pk_cus_asset_month of the Cus_asset_month table is the global index,

This is the statement that created the index:

ALTER TABLE Cus_asset_month ADD constraint Pk_cus_asset_month primary key (Cus_code) using Index;

The index created so that the default is GLOBAL index

After the TRUNCATE operation on the table partition, the insert will error ORA-01502.

So we need to change the index to LOCAL index

ALTER TABLE Cus_asset_month ADD constraint Pk_cus_asset_month primary key (Cus_code) using index LOCAL;

But in the execution of this sentence, and then the error: ORA-14039: The partition column must form a unique index of the keyword column subset

Because this is the primary key index created, the primary key index field must contain the partition field in the partition table, and if the primary key index must be there, then the primary key index can be changed to a normal index.

My final Solution [primary key also has, normal index also has]:

ALTER TABLE Cus_asset_month ADD constraint Pk_cus_asset_month primary key (CUS_CODE,DATA_YM) using index LOCAL;

Create INDEX In_cus_asset_month on Cus_asset_month (cus_code) local;

This way, after I have TRUNCATE the table partition, I will not be able to make any error when inserting data into this table!

The difference between the local index and the global index of PS:

Local index

1. The local index must be a partition index, the partition key is equivalent to the table partition key, partition number is equal to the table Partition said, in a word, the local index partition mechanism and the table partition mechanism.
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-prefixed index.
4. Both prefix and non-prefix indexes can support index partition elimination, provided that the criteria for the query contains the index partition key.
5. Local indexes only support uniqueness within a partition and cannot support uniqueness on the table, so if you want to use a local index to make a unique constraint on the table, you must include the partition key column in the constraint.
6. The local partition index is for a single partition, each partition index points to only one table partition, the global index is not, a partition index can point to N table partition, at the same time, a table partition, may also point to n index partition,
Doing truncate or move,shrink on a partition in a partitioned table can affect n global index partitions, which is why local partitioned indexes have higher availability.
7. The bitmap index can only be a local partition index.
8. Local indexes are most applied to a data warehouse environment.

Globally indexed Global Index

1. The partitioning key and number of partitions and the number of partition keys and partitions for a global index may be different, and the partitioning mechanism for tables and global indexes is different.
2. The global index can be partitioned, or it can be a non-partitioned index, and the global index must be a prefix index, that is, the index column of the global index must be the index partition key as its first few columns.
3. The index entry for a global partitioned index may point to several partitions, so for a global partitioned index, even if you move, truncating data in one partition requires rebulid several partitions
To the entire index.
4. Global indexes are more widely used in OLTP systems.
5. Global Partition index only by range or hash hash partition, hash partition is only supported after 10g.
6. Oracle9i When you make a move or truncate to a partitioned table, you can update the global Partition index synchronously with the update Global indexes statement, which consumes a certain amount of resources in exchange for high availability.
7. The table uses a column as the partition, the index uses B to do the local partition index, if the Where condition uses B to query, then Oracle scans all table and the index partition, the cost will be higher than the partition, at this time may consider uses B to do the Global Partition index



Partitioned Index Dictionaries

Dba_part_indexes Partition Index Summary statistics, you can know which partition index on each table, partition index class new (Local/global,)
Dba_ind_partitions partition-level statistics for each partition index
Dba_indexesminusdba_part_indexes, you can get what non-partitioned indexes are on each table

Index rebuild

Alter index Idx_name rebuild partition index_partition_name [online nologging]
You need to do rebuild for each partition index, you can choose online (without locking the table) when rebuilding, or nologging build the index without generating logs to speed up.
Alter index rebuild Idx_name [online nologging]
For non-partitioned indexes, only the entire index is rebuilt

ORA-01502 two forms of the Oracle Database index index