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.
| 
