ALTER TABLE “I274785”.“O_RPS_RCP_BNFT2” DROP CONSTRAINT “P_RPS_RCP_BNFT2”;
DROP INDEX “I274785”.Test_PK;
ON “I274785”.“O_RPS_RCP_BNFT2”( RPS_CNTR_NMBR
CLUSTER ALLOW REVERSE SCANS COMPRESS NO;
ADD CONSTRAINT “P_RPS_RCP_BNFT2” PRIMARY KEY
(“RPS_CNTR_NMBR”, “EFCTV_DT”, “RPS_PRFX_CD”, “RPS_UNT_NMBR”, “RPS_RCP_NMBR”, “RPS_RCP_BNFT_NMBR”, “RCP_BNFT_SEQ_NMBR”);
It doesn’t need dropping and recreating the table. The identical table exists in Pilot environment. I used the object compare feature (right click on the table) and compare the Test & Pilot structure. The DDL generated by TOAD to sync up Pilot requires dropping & recreating the table. I don’t believe this is the expected behavior. I was able to recreate this issue for a different table.
Is there an option I need to select to avoid the drop & recreate? Or is this a bug?
I made the following changes to a table in Test environment, which is to move the column EFCTV_DT from the last column of the index to the 2nd column. The index is used as the primary constraint and also the clustering index.
CREATE UNIQUE INDEX “I274785”.Test_PKASC, EFCTV_DT ASC, RPS_PRFX_CD ASC, RPS_UNT_NMBR ASC, RPS_RCP_NMBR ASC, RPS_RCP_BNFT_NMBR ASC, RCP_BNFT_SEQ_NMBR ASC)ALTER TABLE “I274785”.“O_RPS_RCP_BNFT2”