Archives of the TeradataForum

Message Posted: Fri, 05 Sep 2008 @ 17:02:47 GMT

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

No, actually, 1.5 million records is REALLY small, depending on the system size. DO NOT put secondary indices on randomly. Statistics on columns that you're joining or selecting on are essential. But we don't even bother with things compression on tables of less than 10 gigabytes. As tables get larger, we do put PPI on them, if it makes sense. We use secondary indices sparingly. In the 2000 or so production tables that we have, we have secondary in on probably less than 5% of them (ok, maybe we have less than 10 that have secondary indices). We consider a table big enough to care about when it's bigger than a two or three hundred gigabytes, maybe 50 million rows, something like that. I don't think we have a single production table with more than one secondary index. The ones we have been tested with the standard method of:

1) Using a production (or production-like) query that NEEDS to be optimized and hasn't been fixed in other ways,

2) Run the EXPLAIN, store it

3) Run the query or better yet, gather history from past production runs

4) Put on the index

5) Run the EXPLAIN and see if it makes any visible difference in the query, and IF IT DOES:

6) Run the query and record several runs to see if it ACTUALLY makes a difference.

7) Then put it into production (after due diligence for the load processes) and monitor that it doesn't affect loads, etc.

To quote Zed from Men in Black, "We're not having an intergalactic kegger here.", and we're not running an Oracle system either. So we do things differently.

iv

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