 |
 |
Archives of the TeradataForum
Message Posted: Thu, 02 Oct 2008 @ 10:16:49 GMT
| Subj: | | Re: Secondeary index not used |
| |
| From: | | McCall, Glenn David |
What version of TD are you using? What type of index USI? NUSI?
On my TD (TD 12), in both cases the index is used. Note that the index isn't used until step 6 - this is because the sub query has to be "done"
first.
The first explain is from the NUSI (duplicated on all amps - step 5). The second is the USI (redistribute by row hash - step 5). In both cases
the index (index #4) is referenced in step 6.
Glenn Mc
P.S. Here are the details
explain
select *
from scores
where period_dt = (
select max(cast (ts as date))
from t1
)
Table:
create table scores (
party_id varchar(240)not null,
party_type varchar(240) not null,
name varchar(240) not null,
text_value varchar(240) casespecific,
period_dt date format 'yy/mm/dd')
primary index xie1scores ( party_id ,party_type )
unique index scores_t ( period_dt );
And obviously the index was non unique for one test :-)
Row count in "scores" table: 327,680 rows
Explains:
NUSI:
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct GM310509."pseudo table" for read on a RowHash to prevent global deadlock for GM310509.t1.
| |
| | 2) | Next, we lock a distinct GM310509."pseudo table" for read on a RowHash to prevent global deadlock for GM310509.scores.
| |
| | 3) | We lock GM310509.t1 for read, and we lock GM310509.scores for read.
| |
| | 4) | We do an all-AMPs SUM step to aggregate from GM310509.t1 by way of an all-rows scan with no residual conditions, grouping by field1 (
15386). Aggregate Intermediate Results are computed globally, then placed in Spool 4. The size of Spool 4 is estimated with high confidence to
be 1 row (19 bytes). The estimated time for this step is 0.05 seconds.
| |
| | 5) | We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of an all-rows scan into Spool 6 (all_amps), which is duplicated on all
AMPs. Then we do a SORT to order Spool 6 by the hash code of (GM310509.t1.Field_2). The size of Spool 6 is estimated with high confidence to be
2 rows (34 bytes). The estimated time for this step is 0.01 seconds.
| |
| | 6) | We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an all-rows scan, which is joined to GM310509.scores by way of a traversal of
index # 4 without accessing the base table extracting row ids only. Spool 6 and GM310509.scores are joined using a nested join, with a join
condition of ("GM310509.scores.period_dt = Field_2"). The input table GM310509.scores will not be cached in memory. The result goes into Spool 7
(all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 7 by field Id 1. The size of Spool 7 is estimated with low
confidence to be 2 rows (54 bytes). The estimated time for this step is 0.02 seconds.
| |
| | 7) | We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of an all-rows scan, which is joined to GM310509.scores by way of an all-rows
scan with no residual conditions. Spool 7 and GM310509.scores are joined using a row id join, with a join condition of
("GM310509.scores.period_dt = Field_2"). The input table GM310509.scores will not be cached in memory. The result goes into Spool 1
(group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 2 rows
(706 bytes). The estimated time for this step is 0.05 seconds.
| |
| | 8) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
USI:
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct GM310509."pseudo table" for read on a RowHash to prevent global deadlock for GM310509.t1.
| |
| | 2) | Next, we lock a distinct GM310509."pseudo table" for read on a RowHash to prevent global deadlock for GM310509.scores.
| |
| | 3) | We lock GM310509.t1 for read, and we lock GM310509.scores for read.
| |
| | 4) | We do an all-AMPs SUM step to aggregate from GM310509.t1 by way of an all-rows scan with no residual conditions, grouping by field1 (
15422). Aggregate Intermediate Results are computed globally, then placed in Spool 4. The size of Spool 4 is estimated with high confidence to
be 1 row (19 bytes). The estimated time for this step is 0.05 seconds.
| |
| | 5) | We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of an all-rows scan into Spool 6 (all_amps), which is redistributed by the
hash code of (GM310509.t1.Field_2) to all AMPs. Then we do a SORT to order Spool 6 by row hash. The size of Spool 6 is estimated with high
confidence to be 1 row (17 bytes).
| |
| | 6) | We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an all-rows scan, which is joined to GM310509.scores by way of a traversal of
index # 4 without accessing the base table extracting row ids only. Spool 6 and GM310509.scores are joined using a nested join, with a join
condition of ("GM310509.scores.period_dt = Field_2"). The input table GM310509.scores will not be cached in memory. The result goes into Spool 7
(group_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 7 by the sort key in spool field1
('00000000000000000000'XB). The size of Spool 7 is estimated with high confidence to be 1 row (27 bytes).
| |
| | 7) | We do a group-AMPs JOIN step from Spool 7 (Last Use) by way of an all-rows scan, which is joined to GM310509.scores by way of an all-rows
scan with no residual conditions. Spool 7 and GM310509.scores are joined using a row id join, with a join condition of
("GM310509.scores.period_dt = Field_2"). The input table GM310509.scores will not be cached in memory. The result goes into Spool 1
(group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 1 row (353 bytes). The estimated
time for this step is 0.04 seconds.
| |
| | 8) | Finally, we send out an END TRANSACTION step to all AMPs involved
| |
| |
