Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Statistics 1
- Thread starter Lysen
- Start date
I don't think the space is as much of an issue as time. I think statitics are just as important, if not more important to collect on commonly joined columns as on indexes.
I think it is more important to review the need for secondary indexes. These are the real space eaters, they are also troublemakers during transaction processing and rollbacks.
At our site I think we have many more secondary index then necessary, particularly when you consider that they don't apply to spool files that are prevolent to a DSS system.
I think it is more important to review the need for secondary indexes. These are the real space eaters, they are also troublemakers during transaction processing and rollbacks.
At our site I think we have many more secondary index then necessary, particularly when you consider that they don't apply to spool files that are prevolent to a DSS system.
-
1
- #3
Stats should be collected on
- any NUSI, without statistics the optimizer is usually not using it
- NUPIs
- USIs/UPIs with a small number of rows, it it's large it depends...
And remember that Teradata is not using Secondary Indexes for Joins (unless it's a Nested Join), so BillDHS suggestion is usefull.
But don't forget non-indexed columns used for joins and Where-constraints, this will help the optimzer to choose the best plan.
There's no overhead regarding disk space, it's the resources/time needed to collect. You sometimes can't afford to repeatedly collect all necessary stats, but in V2R5 there's a COLLECT STATS USING SAMPLE which might save a lot of processing time (but check the manuals before you try it)
Dieter
- any NUSI, without statistics the optimizer is usually not using it
- NUPIs
- USIs/UPIs with a small number of rows, it it's large it depends...
And remember that Teradata is not using Secondary Indexes for Joins (unless it's a Nested Join), so BillDHS suggestion is usefull.
But don't forget non-indexed columns used for joins and Where-constraints, this will help the optimzer to choose the best plan.
There's no overhead regarding disk space, it's the resources/time needed to collect. You sometimes can't afford to repeatedly collect all necessary stats, but in V2R5 there's a COLLECT STATS USING SAMPLE which might save a lot of processing time (but check the manuals before you try it)
Dieter
- Status