When a table is unusable, it prevents the importing of new data
Everyday a clean up task would messed up a partitioned table. Thus the table would have unusable indexes. This problem prevents the import of new data into the table.
On google we can quickly find interesting articles how to fix the problem :
ALTER INDEX index_name REBUILD
The solution is to rebuild indexes after the error. This temporary fix is not sustainable for this scenario. Because it means to launch the command of rebuilding indexes everyday on a table with potentially millions of data.
Description of the partitionned table
The table with unusable indexes is partitioned and has two indexes. One index is on the primary key and the other index on a date column MY_TABLE_DATE.
The table is partitioned with the method “Range Partitioning”
Range partitioning maps data to partitions based on ranges of partition key values that you establish for each partition. It is the most common type of partitioning and is often used with dates. For example, you might want to partition sales data into monthly partitions.
The table is partitionned with the date and has a primary key :
create table MY_TABLE
ID NUMBER GENERATED BY DEFAULT AS IDENTITY ,
MY_TABLE_DATE DATE NOT NULL,
NAME VARCHAR2(3) NOT NULL,
SURNAME VARCHAR2(20) NOT NULL,
PARTITION BY RANGE (MY_TABLE_DATE )
(PARTITION P_20100101 VALUES LESS THAN (TO_TIMESTAMP('2010/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS') )
Actually this primary key is a problem according to Oracle documentation. Indeed the partitionned column should be part of the primary key. Read on from « Partitioned Index-Organized Tables » :
For partitioning an index-organized table
Partition columns must be a subset of primary key columns
Therefore the solution is to make the partitionned column part of the primary key. the solution is something like this :
ALTER TABLE MY_TABLE DROP CONSTRAINT TABLE_PK;
ALTER TABLE MY_TABLE ADD CONSTRAINT TABLE_PK PRIMARY KEY (ID, MY_TABLE_DATE) USING INDEX LOCAL TABLESPACE tab_p_specified_space;