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

Attachments

Library

Quick Reference