Archives of the TeradataForum

Message Posted: Mon, 12 Mar 2001 @ 17:57:11 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Howard,

(Oh boy ! This one's going to run ...)

I'll make an assumption that you expect these new indexes to benefit your query processing. I know this is 'stating the obvious' but if they don't benefit query processing then DON'T add them.

In your case have you got queries that need to select on week number ONLY instead of date (as they presumably do now)? If you have, then maybe you could remove one/more of the existing indexes on date columns to be replaced by new ones on week-number. If you simply want to allow for selection based on date AND week-number, then it's quite probable that you won't find any benefit for the queries from these new indexes - the optimiser would just follow it's existing plan but treat the selection on the week number as an extra residual condition.

Right, having got that lot out of the way, most of the systems that I've worked on seem to stop at about 3 or 4 SI's on a single table. Adding a NUSI is always a trade-off between benefits to query performance and slowing down the maintenance time. These new ones that you're looking at will be 7 times more non-unique than your existing ones which will further increase your maint time. How many data rows do you expect to have per week-number? Divide that by the number of AMPs on your system and then divide by 8000. This gives you the average number of NUSI data blocks per amp for one of these indexes. As that number starts to grow, you get significantly increased maintenance times.

BTW - I think the limit for the number of SI's on a single table is 32 (not 64 as in Julie's reply).

Hopefully this hasn't been too rambly and is of some use. It'll be interesting to see what numbers other people come back with.

Cheers,

Dave

	<Prev	Next>		<<First	<Prev	Next>	Last>>