Archives of the TeradataForum
Message Posted: Mon, 28 Apr 2003 @ 19:42:47 GMT
I have a question regarding collect statistics. We have very small window to run statistics jobs to collect statistics at table level (5:00PM till midnight). We have defined stats on primary and secondary indexes. Some of the users are requesting column level statistics on very large tables, as these are the columns most frequently used in the Join conditions in many queries. The problem is when we define statistics at column level, (for many columns on the table) our jobs are taking long time and not finishing in stipulated time as they perform table level scan. One of my friends suggested that if we define Secondary index (NUSI) on those columns, and define stats on the Secondary indexes, it might fasten the process. Some how I am not convinced with his idea as it has to scan subtable instead of base table and if the Number of non unique values are less, it would be equivalent to the same as the base table scan. I also found that optimizer is not using the NUSI defined on the table during query execution for some of the queries I checked. I feel waste of the space also if NUSI's are not used. We are unable to decide which would be better.....We have no constraint on space as of now.
Give the scenario mentioned which would you think is better option or please let me know other options available.
Appreciate your help on this....
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|