Archives of the TeradataForum
Message Posted: Sun, 17 Jun 2001 @ 22:16:20 GMT
We all know that we should collect statistics on NUPI's. But do you collect statistics on UPI's as well? It's always been a contentious point but I have always don't them as a rule of thumb. Here's JK's general Collect Statistics 'guidelines' for what they are worth:
1. Collect on all UPI & NUPI columns (indexes)
2. Collect on all NUSI & USI columns (indexes)
3. Collect on all columns used in joins (PI's or not)
4. Collect on all columns used in qualifications/restrictions (i.e. used in the WHERE clause)
If you do this as you are initially designing (specially when you are doing a first out with canned reports (parameterized or not) and setting up your database - you will have a great majority covered. Also, when you are in initial testing/staging the systems for production. Access Logging on your development userid's is a very useful tool to capture SQL and see what the 'apps folks' are doing and gives you the chance to add collect stats on other columns.
Oh - we all know UPI's are unique, but does the optimizer know how many there are?
Just my thoughts for a sunny Sunday afternoon in 111F heat! (But it's a dry heat;-)
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|