Collect stats on column vs on index

Database

Collect stats on column vs on index

I have a table with primary and secondary indexes defined on it. Something like:

CREATE TABLE DB1.TBL1

(COL1 INTEGER,

COL2 CHAR(10),

COL3.....

COL10)

UNIQUE PRIMARY INDEX PMY_idx ( COL1 )

INDEX SEC_idx ( COL2 );

Stats can be defined on the table in two ways:

COLLECT STATISTICS ON DB1.TBL1 INDEX (COL1);

COLLECT STATISTICS ON DB1.TBL1 INDEX (COL2);

OR

COLLECT STATISTICS ON DB1.TBL1 COLUMN (COL1);

COLLECT STATISTICS ON DB1.TBL1 COLUMN (COL2);

However, in both cases I get the same result on doing HELP STATS on the table. Is there any difference between the statements or we can use interchangably?

Tags (1)
2 REPLIES

Re: Collect stats on column vs on index

Hi.

There is no difference, but you could use the INDEX clause with your named indexes instead of their columns:

COLLECT STATISTICS ON DB1.TBL1 INDEX PMY_idx;

COLLECT STATISTICS ON DB1.TBL1 INDEX SEC_idx;

Cheers.

Carlos.

N/A

Re: Collect stats on column vs on index

Carlos,

Using INDEX clause will access the index path to collect the stats resulting faster stats collection?

-Sen