Secondary Index

Database
Enthusiast

Secondary Index

IS it necessary to always collect statistics on NUSI? At my end the table is frequently accessed and due to some reason we can't collect statitics so does this means that the NUSI on the table will not be accessed always.

Thanks,
9 REPLIES
Enthusiast

Re: Secondary Index

Hi,

It is of the utmost importance to collect statistics on all NUSIs. Statistics might impact whether or not the optimizer chooses to use a secondary index, which could result in substantial differences in query run times. If a sort key is specified in a NUSI definition with the ORDER BY option, it is important to collect statistics on that sort key column in order for the optimizer to be able to more accurately compare the cost of using that index versus using a different access path.

Hope this makes sense!!

Enthusiast

Re: Secondary Index

if the query which we fire reminas the same always with only where clause equlaity changes so still we need to collect the statistics.

Regards,
Vinod

Re: Secondary Index

They say the only thing worse than no statistics is stale statistics. The technical consensus is that if you can't collect stats then there's no point in having a nusi. If you're not able to collect stats you really need to resolve the problem. Collect stats uses an access lock which should only be blocked by an exclusive lock. It uses a write lock only at the very end of the process when the dbc tables are updated.

It's possible that you can get away without recollecting stats every load cycle if the data isn't changing dramatically. Internally the stats are stored in histogram intervals, and if the overall data distribution doesn't change then the histograms don't change. For example, the relative frequency of customer or product codes typically don't change quickly. Eventually the row counts grow and new codes are added so stats need to be refreshed.

However, a column like transaction date is always adding to the end of the distribution. If you don't recollect stats the optimizer will always think there are zero rows for the new dates, and that can cause extremely bad execution plans.

Enthusiast

Re: Secondary Index

I would not collect stats on the column every time you load unless the column is a date column that is always getting incremented with each load. You can also have an issue if you're doing range-based queries against a column that is getting incremented with each load, so you'll want to collect on these each time you load as well.

For tables where this isn't the case, set up a periodic recollection of stats. We're currently using 3 months for most of our tables. We cycle through the tables at different times so that each week, we're re-collecting stats on approximately 1/12 of the tables. If the demographics of your tables change drastically during updates, then you'll want to collect more often. If they are fairly static, you may want to push it out to 6 months or 1 year.

You can burn up a lot of CPU time recollecting stats unnecessarily.

Thanks,
Barry
Enthusiast

Re: Secondary Index

Hi,

Say, a table is having one column NUSI and stats are collected on that index. What will happen to stats when the NUSI is dropped?

Enthusiast

Re: Secondary Index

If the stats are collected on the index (NUSI) and NOT on the column then stats would be dropped if you drop the index.

Senior Apprentice

Re: Secondary Index

If stats are collected on a single column NUSI and that index is dropped, the stats are *not* dropped.

This only happens for multi-column NUSIs, but not in TD14 anymore.

Dieter

Enthusiast

Re: Secondary Index

Hi Dieter,

Thanks for your response. I tried this one created a table with single column NUSI and collected stats on the index. Then i dropped the index, still i able to see the stats using help stats and in dbc.tvfields. I am using Teradata 12.

Now after dropping the index, whether the stats can be used by the parser when we query the table using that column? In explain i am not able to see any difference in the output after dropping the index.

Whether those collected stats are used even after dropping the index?

Where can i see the collect stats definition?

Senior Apprentice

Re: Secondary Index

Whether those collected stats are used even after dropping the index?

Of course are those stats used, both column and index stats on a single column are stored in tvfields, there's absolutely no difference.

Where can i see the collect stats definition?

Do you mean the actual statement to collect stats?

It's not stored in older releases (TD14: SHOW STATS ON tab;)

Or the actual data stored in dbc.TVFields?

HELP STATS tab COLUMN xxx; (TD14: SHOW STATS VALUES COLUMN xxx ON tab;)

Dieter