 |
 |
Archives of the TeradataForum
Message Posted: Sat, 31 Oct 2015 @ 15:35:07 GMT
| Subj: | | Re: Multiple single-column SI vs Single Multi-column SI |
| |
| From: | | Myrna, James |
A query may still use a composite secondary index even if all of the columns are not specified in the WHERE clause. If the optimizer believes
it is better to scan the index and retrieve row id's rather than to scan the entire table (collecting stats are important for the columns), then
the index can still be used. Also, if the secondary index covers the query, then no access to the base table is required.
Explanation
-------------------------------------------------- | |
| | 3) | We do an all-AMPs RETRIEVE step from EMPLOYEE by way of a traversal of index # 4 extracting row ids only with a residual condition of
("EMPLOYEE.LST_NME = 'SMITH'") into Spool 2 (all_amps) (compressed columns allowed), which is built locally on the AMPs. Then we do a SORT to
order Spool 2 by row id eliminating duplicate rows. The size of Spool 2 is estimated with high confidence to be 125 rows. The estimated time for
this step is 0.53 seconds.
| |
| | 4) | We do an all-AMPs RETRIEVE step from EMPLOYEE by way of row ids from Spool 2 (Last Use) with no residual conditions into Spool 1
(group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 30,956 rows (24,888,624 bytes).
The estimated time for this step is 0.54 seconds.
| |
Regards,
Jim
| |
