When a User Specifies Only an Index Name to Modify the Index, A Message Indicating That the Index Does Not Exist Is Displayed

Symptom

Create a partitioned table index HR_staffS_p1_index1, without specifying index partitions.

1

CREATE INDEX HR_staffS_p1_index1 ON HR.staffS_p1 (staff_ID) LOCAL;

Create a partitioned table index HR_staffS_p1_index2, with index partitions specified.

1
2
3
4
5
6

CREATE INDEX HR_staffS_p1_index2 ON HR.staffS_p1 (staff_ID) LOCAL
(
    PARTITION staff_ID1_index,
    PARTITION staff_ID2_index TABLESPACE example3,
    PARTITION staff_ID3_index TABLESPACE example4
) TABLESPACE example;

The user changes the tablespace of an index partition staff_ID1_index to example1:

When the user executes ALTER INDEX HR_staffS_p1_index2 MOVE PARTITION staff_ID2_index TABLESPACE example1;, a message is displayed, indicating that the index does not exist.

Cause Analysis

Run the CREATE INDEX HR_staffS_p1_index2 MOVE PARTITION staff_ID2_index TABLESPACE example1 command to recreate the index. An error message indicating that the index already exists is displayed. Then, run the following SQL statement or the \d+ HR.staffS_p1 meta command using gsql to query the index, a message is displayed, indicating that the index already exists.

1

SELECT * FROM DBA_INDEXES WHERE index_name = HR.staffS_p1 ;

The possible reason why the user fails to find the index is that the user is in the public schema instead of the hr schema.

To verify this guess, execute ALTER INDEX hr.HR_staffS_p1_index2 MOVE PARTITION staff_ID2_index TABLESPACE example1;. The execution succeeds, proving the guess to be correct.

Execute ALTER SESSION SET CURRENT_SCHEMA TO hr; and then ALTER INDEX HR_staffS_p1_index2 MOVE PARTITION staff_ID2_index TABLESPACE example1;. The setting succeeds.

Solution

Use schema.table to determine a table, index, or view for query.