I have created a table "product" with defination as below,
CREATE SET TABLE product ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
product_name VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
sale_date DATE FORMAT 'dd-mm-yyyy',
PRIMARY INDEX ( product_id );
presently there are 10 rows in it.
Now i collected stats on column (product_name, sale_date) as below,
--- collect statistics product column (product_name,sale_date) ;
then i try to drop column sale_date from "product" table as below,
alter table product
drop sale_date ;
But it gaves me an error "3557 : column sale_date is an Index column and cannot be dropped".
I think statistics which i collected got inserted into DBC.Indexes because of which it is telling me this error (even though there are no index defined on sale_date column).
But please put some light on this issue as I am still not pretty sure about it.
this is just a misleading error message, it should be rephrased to match the error for single column statistics:
"6956 Column xxx has statistics and cannot be dropped or modified."
Before TD14 a multi-column statistic is stored as a pseudo-index in dbc.indexes, which might be the cause.
So simply drop the stats before you drop the column.