Archives of the TeradataForum

Message Posted: Thu, 26 Jun 2003 @ 11:21:09 GMT

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

The key questions to ask are as follows :

- Will you always enter all three columns on every query?

- How do you join to the rest of your model?

Both these are key questions as you may be able to get the data out of the table quickly with one option but hamper joining to other tables in doing so. If you understand the usage clearly, you can weigh up the merits of each option and make an informed decision. As with all performance tuning it is a balancing act.

In my experiences secondary indices tend to be expensive (space and maintenance) given that their use by the optimizer is often inconsistent (heavily impacted by data.) I was interested that you said you did not have the space for a single table JI but think you have the space for several secondaries. I have found single table JIs to be very useful and, if you use the column grouping and ROWID options, they don't actually have to be that big. Check out the manuals for column grouping and rowid options and have a play. You could for example keep your current index and add a single table JI with the 3 piece index. You may find this uses less space and is more useful and consistent than secondaries.

Hope this helps, you've asked a quite detailed question that's hard to answer in a short mail (without charging!)

If you are in the UK, I will be presenting on this issues around this topic at the user group meeting in July so it may be worth trying to attend if you want some more ideas.

Andy

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