Two forms of ORA-01502 oracle database index, ora-01502oracle

Two forms of ORA-01502 oracle database index, ora-01502oracle

In the current project, when we run the batch, there is an SP always reports the error "ORA-01502: index 'wbill _ 102. PK_A_NOTWEB_ACT_PROVINCE_M 'or partition of such index is in unusable state ". The solution is to re-create the INDEX and ALTER index IN_AA REBUILD;

However, after the solution is completed, this error is still reported during batch execution. This requires me to consider what causes the problem!

My error scenarios are restored:

There is a table CUS_ASSET_MONTH. The primary key is PK_CUS_ASSET_MONTH, and the primary key field is CUS_CODE. This table is a partition table and the partition field is DATA_YM.

In order to re-run the batch, the first step of the batch is to delete the data for this month:

Alter table CUS_ASSET_MONTH truncate partition PA_CUS_ASSET_MONTH_201412;

Then insert into the table CUS_ASSET_MONTH, this will report an error ORA-01502

Scenario restored!

Problem Analysis:

The index PK_CUS_ASSET_MONTH in the CUS_ASSET_MONTH table is a global index,

Here is the statement for creating an index:

Alter table CUS_ASSET_MONTH add constraint PK_CUS_ASSET_MONTH primary key (cus_code) using index;

The default INDEX is global index.

After the TRUNCATE operation is performed on the table partition, The ORA-01502 is reported when the insert operation is performed.

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 when this statement is executed, the following error is reported: ORA-14039: partition columns must constitute a UNIQUE index of the keyword column child set

This is because the primary key index is created. The primary key index field must contain the partition field in the partition table. If this primary key index must exist, you can change this primary key index to a common index.

My final solution [primary keys and common indexes]:

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;

In this way, when I perform the TRUNCATE operation on the table partition and insert data into this table, no error will be reported!

Difference between local index and global index of ps local index:

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 a partition table may affect n global index partitions. Because of this, 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 may be different from the partition key and number of partitions of the table. The partition mechanism of the table and the 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 only the data in one partition is truncated, you must rebulid Several partitions.
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 use B for global partition index.


 
Partition index dictionary
 
SUMMARY statistics of the 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 statistics for each partition Index
Dba_indexesminusdba_part_indexes to obtain the non-partition indexes of each table.
 
Index Reconstruction
 
Alter index idx_name rebuild partition index_partition_name [online nologging]
You need to rebuild each partition index. You can select online (the table will not be locked) When rebuilding, or do not generate logs when nologging creates an index to speed up the process.
Alter index rebuild idx_name [online nologging]
For non-partitioned indexes, only the entire index can be rebuilt