Archives of the TeradataForum

Message Posted: Thu, 24 Apr 2003 @ 08:33:33 GMT

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

Our main use of join indices is as single table JIs to offer a quick alternate route into the table and this has proved very successful. Single table JIs are useful when you have two suitable and highly used candidates for the PI of a table. They can also be good for lookup link tables (many to many) as you can effectively maintain two versions of the table without any admin overhead. That said, maintenance overhead (loading) is high if you update rows in your tables, we are lucky as we generally tend to append rather than update.

Other things to consider are as follows, we found these useful

- Can any queries be covered by the join index, adding a handful of extra columns can mean it is used more and for queries that require FTS it can be much quicker.

- The inclusion of ROWID is possibly the most useful option as it allows quick access to columns not covered by the join index. So the join index identifies the rows then goes back into the target table on rowid - much quicker than FTS.

- Use the grouping option (play around with it) as this can save a lot of space. Group the common fields together, they will only be held once and the more unique fields will be held as many times as possible. I'm not sure if this speeds things up as well (anyone know?)

Whilst we do not do any aggregation in out single table JIs, I'm fairly sure the options above fit nicely with aggregates also.

The final thing is that the cost based optimizer will still not always use the join index. Views can be set up to force it to be used but tread carefully. There were some examples where we tried to force the use of the JI as we thought we new best. Testing proved the optimizer to be correct so don't get too hung up if it doesn't always choose to use the JI.

Hope this helps

Cheers

Andy

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