Statistics vs Secondary Index on a Table

Database
Enthusiast

Statistics vs Secondary Index on a Table

Hi,

I would like to understand the performance improvement we might get when using secondary index vs collecting Stats. 

If Stats are collected on a singe/multiple columns used often in queries, isn't that sufficient? When should we go for secondary indices, apart from achieving uniqueness on a combination of columns.

Thanks

Manjeeth

2 REPLIES
Enthusiast

Re: Statistics vs Secondary Index on a Table

Any inputs on this.

Enthusiast

Re: Statistics vs Secondary Index on a Table

These are 2 differenct concepts. Coming to Secondary indexes it can be either unique or non unique, NUSI are used by the optimizer only if it finds better way compared to FTS, usually weekly selective NUSI are ignored by Optimizer. USI are always used by optimizer.

Either way stats are always recommeded on indexed columns or frequently joined columns, which will definetly help optimizer to prepare a good plan.

I would suggest initially to Collect stats on the columns, see the explain plan on your query, secondly create Secondary index then check the explain plan again. if you could see a greatly improved plan, please go ahead and use the secondary index. Else try to avoid Secondary index as it creates over head by having a subtable which needs to be maintained by the system every time the table is loaded with records.