Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

Message Posted: Thu, 02 Oct 2008 @ 10:16:49 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


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  



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020