Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 12 Feb 2003 @ 23:50:18 GMT


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


Subj:   Question on why Aggregate Join Index Not Being Used
 
From:   Carl Martin

I have created a Multitable Aggregate Join Index, but can't seem to get the SQL to use it. Below you will find all related information related to this task. I would greatly appreciate any suggestions.

Create for Aggregate Join Index:

CREATE JOIN INDEX JoinIndex1 ,NO FALLBACK

AS
SELECT COUNT(*)(FLOAT, NAMED CountStar ),
DatabaseName.Table2.Column23 ,
DatabaseName.Table1.Column12,
DatabaseName Table1.Column13,
SUM(DatabaseName.Table1.Column15 )(FLOAT, NAMED Column15 ),
SUM(DatabaseName.Table1.Column16 )(FLOAT, NAMED Column16 ),
SUM(DatabaseName.Table1.Column17 )(FLOAT, NAMED Column17 ),
SUM(DatabaseName.Table1.Column18 )(FLOAT, NAMED Column18 )

FROM (DatabaseName.Table1  Table1
INNER JOIN DatabaseName.Table2  Table2
ON DatabaseName.Table1.Column11 =  DatabaseName.Table2.Column21 )

GROUP BY DatabaseName.Table2.Column23
        ,DatabaseName.Table1.Column12
        ,DatabaseName.Table1.Column13

PRIMARY INDEX ( Column23 ,Column12 ,Column13 );

Statistics for Aggregate join Index:

Date      Time      Unique Values   Column Names
03/02/12  14:19:47  1,551,653       Column23,Column12,Column13

Create DDL for DatabaseName.Table1:

CREATE SET TABLE DatabaseName.Table1 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL

     (
      Column11  INTEGER NOT NULL,
      Column12  SMALLINT NOT NULL,
      Column13  DATE FORMAT 'YYYY-MM-DD' NOT NULL,
      Column14  CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT 'L',
      Column14  DECIMAL(9,2) NOT NULL DEFAULT 0.00 ,
      Column16  DECIMAL(9,2) NOT NULL DEFAULT 0.00 ,
      Column17  INTEGER NOT NULL DEFAULT 0 ,
      Column18  DECIMAL(9,2) NOT NULL DEFAULT 0.00 )

PRIMARY INDEX  ( Column11,Column12,Column13 );

Statistics for DatabaseName.Table1:

Date      Time      Unique Values   Column Names
03/02/12  15:05:50            528   Column11
03/02/12  15:06:00            994   Column12
03/02/12  15:06:05            111   Column13
03/02/12  15:06:10              1   Column14
03/02/12  15:06:14     21,411,105   Column11,Column12,Column13

Create DDL for DatabaseName.Table2:

CREATE SET TABLE DatabaseName.Table2 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL

     (
      Column21 INTEGER NOT NULL,
      Column22 INTEGER NOT NULL,
      Column23 INTEGER NOT NULL,
      Column24 INTEGER NOT NULL,
      Column25 CHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT '                                        ')

UNIQUE PRIMARY INDEX  ( Column21 );

Statistics for DatabaseName.Table2:

Date      Time      Unique Values   Column Names
03/02/12  10:40:00            834   Column21
03/02/12  10:40:12             63   Column22
03/02/12  10:40:14             22   Column23
03/02/12  10:40:16              4   Column24

SQL:

select   Table2.Column23,
            Table1.Column12,
            Table1.Column13,
            sum(Table1.Column15) As Column15
 from     DatabaseName.Table1  Table1
 join      DatabaseName.Table2  Table2
 on        Table2.Column21 = Table1.Column11 and
            Table2.Column23 = 23 and
            Table1.Column12 = 999 and
            Table1.Column13 = '2003-01-31'
 group by           1, 2, 3;

Explain:

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct DatabaseName."pseudo table" for read on a RowHash to prevent global deadlock for DatabaseName.Table2.  
  2)Next, we lock a distinct DatabaseName."pseudo table" for read on a RowHash to prevent global deadlock for DatabaseName.Table1.  
  3)We lock DatabaseName.Table2 for read, and we lock DatabaseName.Table1 for read.  
  4)We do an all-AMPs RETRIEVE step from DatabaseName.Table2 by way of an all-rows scan with a condition of ("DatabaseName.Table2.Column23 = 23") into Spool 4, which is redistributed by hash code to all AMPs. The size of Spool 4 is estimated with high confidence to be 37 rows. The estimated time for this step is 0.03 seconds.  
  5)We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an all-rows scan, which is joined to DatabaseName.Table1 with a condition of ("(DatabaseName.Table1.Column12 = 701) AND (DatabaseName.Table1.Column13 = DATE '2003-01-31')"). Spool 4 and DatabaseName.Table1 are joined using a single partition hash join, with a join condition of ("Column21 = DatabaseName.Table1.Column11"). The result goes into Spool 3, which is built locally on the AMPs. The size of Spool 3 is estimated with low confidence to be 10 rows. The estimated time for this step is 0.20 seconds.  
  6)We do a SUM step to aggregate from Spool 3 (Last Use) by way of an all-rows scan, and the grouping identifier in field 1. Aggregate Intermediate Results are computed globally, then placed in Spool 5. The size of Spool 5 is estimated with low confidence to be 10 rows.  
  7)We do an all-AMPs RETRIEVE step from Spool 5 (Last Use) by way of an all-rows scan into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 10 rows. The estimated time for this step is 0.17 seconds.  
  8)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->The contents of Spool 1 are sent back to the user as the result of statement 1.  



     
  <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: 15 Jun 2023