All Forums Database
manharrishi 11 posts Joined 09/13
24 Aug 2015
Statistics vs Secondary Index on a Table

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.

manharrishi 11 posts Joined 09/13
25 Aug 2015

Any inputs on this.

kirthi 65 posts Joined 02/12
25 Aug 2015

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.

You must sign in to leave a comment.