Relation between Statistics and Indexes on a table

Database
Enthusiast

Relation between Statistics and Indexes on a table

Hi,

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_id INTEGER,

      product_name VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      sale_date DATE FORMAT 'dd-mm-yyyy',

      daily_sales DECIMAL(18,6))

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.

2 REPLIES
Senior Apprentice

Re: Relation between Statistics and Indexes on a table

Hi Mahesh,

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.

Dieter

Enthusiast

Re: Relation between Statistics and Indexes on a table

Its clear now. Thank you Dieter..