Archives of the TeradataForum
Message Posted: Wed, 13 Jun 2002 @ 00:59:37 GMT
| Subj: | | Re: Full-Table Scans and Secondary Indexes |
| |
| From: | | Todd A. Walter |
David's note is right about spooling rowids from a NUSI in order to more efficiently access base table rows when a lot of rows are
expected to be qualified via the index.
Mike's note is right for older releases but this area has changed a lot in R4.0 and 4.1.
Single table join indexes can be defined with an index portion(including hashing differently than the underlying table) and a data
portion creating a "covered index". This form of index can fully satisfy a query if the query requires only columns that are
included in the index. It can fully satisfy a single table query or it can satisfy the retrieval for joins,... in which case it can
be used directly if the hashed fields match the join fields or it can be spooled, redistributed,... . Basically it is just another
table the optimizer considers in planning. As we implemented that, we extended the number of cases in which a NUSI can be used
directly for satisfying queries or joins. Of course, NUSIs typically have fewer fields and are therefore less likely to be able to
satisfy a large set of queries but it can and will be used as a table also. USI will also be used the same way.
| 
