Tuesday, March 27, 2012

A error in partition table ,could you tell me ?

1 HIS_HTTP_LOG a partition table
2 REL_HTTP_LOG not a partition table,the same structure of HIS_HTTP_LOG;
3 When HIS_HTTP_LOG doesn't exist any index
the following executed succeed

ALTER PARTITION SCHEME PS_HIS_HTTP_LOG NEXT USED [FG_03]
ALTER PARTITION FUNCTION PF_HIS_HTTP_LOG() SPLIT RANGE ('20070331 23:59:59.997')
ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3

4 However when I added the index in HIS_HTTP_LOG and execute the step 3,It made error:
a) CREATE INDEX IDX_HIS_HTTP_LOG_001 ON HIS_HTTP_LOG(USERID)ON PS_HIS_HTTP_LOG (STARTIME)
b) ALTER PARTITION SCHEME PS_HIS_HTTP_LOG NEXT USED [FG_03]
ALTER PARTITION FUNCTION PF_HIS_HTTP_LOG() SPLIT RANGE ('20070331 23:59:59.997')
ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3


========================= Error messages================================================
"ALTER TABLE SWITCH statement failed. There is no identical index in source table 'TMP_HTTP_LOG SWITCH ' for the index 'IDX_HIS_HTTP_LOG_001' in target table 'HIS_HTTP_LOG' ."

When I added index in REL_HTTP_LOG ,it gave me the same error message

Could you tell me how can I solve the problem !

The error says you need to create anidentical index on?TMP_HTTP_LOG SWITCH,?as?'IDX_HIS_HTTP_LOG_001' in target table 'HIS_HTTP_LOG' .?So?create?such?an?index?and?try?again.

When?use?ALTER?TABLE?SWITCH?to?transfer?schema, there is no physical data movement, only metadata change, the partitions and tables involved in the switching are required to be homogeneous. They must have the same columns of the same data type, name, order, and collation on the same filegroup.

No comments:

Post a Comment