Solution to index errors after modifying the field type in Oracle

Cause:

The union all operation is required in the query, and the operation cannot be completed because the LONG type field exists in the table. Modify the LONG type field to the CLOB type according to the specific business scenario. After the modification, the union all operation is feasible, but there is a problem with the addition, deletion, and modification of the sub-table, prompting that the INDEX of the parent table is unavailable.

By the way, if you want to change from the VARCHAR2 type to the special type CLOB, you cannot directly convert from VARCHAR2 to the clob type. You need to use the LONG type, to make a transition (you can first change it to the LONG type and then change it from LONG to CLOB ).

Solution:

1. My colleague also encountered this problem recently. His solution is to back up the old table, delete the table, then import data, and re-create the primary key. It may be difficult for an ORCALE server that does not have direct operation permissions. So here, the final result is drop and re-create.

2. re-create the index. Oracle indexes can be re-built in multiple ways, such as drop and re-create, rebuild, and rebuild online. The first method is used to delete the creation. Some scripts are as follows (TB_SCENERY is the parent table [attraction table], and TB_SCENERY_TICKETS is the sub table [attraction ticket table ]):

1. -- Query the primary and Foreign keys. The table name must be in uppercase.
2. SelectConstraint_nameFromUser_constraintsWhereTable_name ='Tb _ SCENERY';
3. SelectConstraint_nameFromUser_constraintsWhereTable_name ='Tb _ SCENERY_TICKETS';
5. -- Delete the primary and Foreign keys. Note the case sensitivity.
6. Alter TableTB_SCENERYDrop ConstraintSCENERY_PK_ID;
7. Alter TableTB_SCENERY_TICKETSDrop ConstraintSCENERY_ID;
9. -- Add a primary key (the primary key index will be automatically created when the primary key is added)
10. Alter TableTB_SCENERYAdd ConstraintSCENERY_PK_IDPrimary Key(ID );
12. -- Add a foreign key
13. Alter TableTB_SCENERY_TICKETSAdd ConstraintSCENERY_PK_IDForeign Key(SCENERY_ID)ReferencesTB_SCENERY (ID );
15. -- Query related indexes of a table
16. SelectIndex_name, index_type, table_nameFromUser_indexesWhereTable_name ='Tb _ SCENERY';
18. -- Delete an index [forcibly]
19. DROP INDEXSCENERY_PK_ID [FORCE];
21. -- Query which tables do not have an index
22. SELECTTable_nameFROMUser_tables tWHERE NOTEXISTS (SELECTTable_nameFROMUser_constraints cWHEREConstraint_type ='P' ANDT. table_name = c. table_name)

The steps here are:

1. query the foreign key name of the sub-table, delete the corresponding foreign key, and the index corresponding to the foreign key

2. query the primary key name of the primary table, delete the corresponding primary key, and the index corresponding to the primary key.

3. Create a primary key for the primary table (primary key index will be automatically created), create a foreign key for the child table, and create a foreign key index

3. the drop and re-create method is a bit faster, but the disadvantage is that it will affect the original SQL query. If you consider this effect, you can use the rebuild method (IDX_TEST_C1 is the index name ):

Alter index IDX_TEST_C1 REBUILD;

In fact, we tried the rebuild method at the beginning, but failed. Instead, we can only use the drop and re-create method.