Archives of the TeradataForum
Message Posted: Tue, 29 Sep 2009 @ 22:49:14 GMT
| Subj: | | Re: Join Index Versus secondary index |
| |
| From: | | Victor Sokovin |
| | I am looking for a little guidance from the forum on which solution would yield better, more consistent performance | |
| | Option 1 - Secondary index on field xxx | |
| | Option 2 - Join index selecting field xxx and the rowid with a primary index of xxx | |
This is one of these seemingly simple questions for which "little guidance" is not really possible, so I have to disappoint you straight
away.
I'd say that in production systems Option 1 would be considered first because simple (non-aggregated) single-table JIs are the least efficient
in terms of their cost/benefit ratio, and the DBA aspects of maintaining them are not easy. So, this would be the end of the analysis in most
practical situations.
If maintenance and costs are not an issue, then Option 2 can have its advantages but not really in the simplest case of one column xxx. JI is a
potentially rich structure with its own PI and SI possibilities, separate stats collection strategy, etc. When you only take one column you don't
really use these possibilities. You do still benefit from separate storage of JI and its compression algorithm so there should be examples when
the JI will shine. The easiest of them is when column xxx has only one value for all rows. NUSI may not "know" this but JI will always do.
JI do not have the notion of uniqueness so if Option 1 allows a USI then it would give you a potential two-AMP access path whereas JI may lead
to all-AMP access.
Victor
| 
