Archives of the TeradataForum

Message Posted: Thu, 17 Jan 2008 @ 11:33:46 GMT


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

Subj:		Join behaving of a PI join

From:		Mutsch, Joerg

Hi all,

I am working on a report where I have to join two fact tables with the same primary Index and two dimensional Tables. For my understanding the fact tables should be joined based on the PI but instead the Optimizer joins one of the fact Tables and with one of the dimension tables before joining with the other Fact tables. In this case the PI Join will get lost.

Even if I bring the fact tables into a dirived table there is no change on the behaving. I mean the answerset will be returned within seconds but I also had it that these kind of reports can take forever.

This behaving is usually recognized when 2 Fact Tables and multiple smaller dimensional or lookuptables will be joined.

How can I force the optimizer to pick the Join between the two fact tables first before joining the dimensional tables?

Cheers Joerg

These are the details:

I am working on TNT Demo 6.1. No sec. Index are set so far.

SQL:

     SELECT "Orders" ."OrderDate" "OrderDate" ,
              "CUSTOMERS"."CustomerName" "CustomerName" ,
              "PRODUCT_DIMENSION"."ProductName" "ProductName" ,
              "PRODUCT_DIMENSION"."ProductLine" "ProductLine" ,
              sum("Orders"."OrderQty") "OrderQty" ,
              sum("Orders"."UnitsReturned") "UnitsReturned"
     FROM
              (
                      (SELECT "ORDERHEADER"."OrderDate",
                              "ORDERHEADER"."CustomerId",
                              "ORDERDETAIL"."ProductNumber",
                              "ORDERDETAIL"."OrderQty",
                              "ORDERDETAIL"."UnitsReturned"
                      FROM "ORDERHEADER" "ORDERHEADER"
                      INNER JOIN "ORDERDETAIL" "ORDERDETAIL"
                      ON "ORDERHEADER"."OrderCode" = "ORDERDETAIL"."OrderCode"
                      )
                      "Orders"
              INNER JOIN  "CUSTOMERS"
              ON "CUSTOMERS" ."CustomerId" = "Orders"."CustomerId"
              )
     INNER JOIN "PRODUCT_DIMENSION" "PRODUCT_DIMENSION"
     ON "PRODUCT_DIMENSION" ."ProductNumber" = "Orders"."ProductNumber"
     GROUP BY "Orders"."OrderDate",
              "CUSTOMERS"."CustomerName",
              "PRODUCT_DIMENSION"."ProductName",
              "PRODUCT_DIMENSION"."ProductLine"

Explain:

Explanation --------------------------------------------------

1) First, we lock RETAIL.PRODUCT_DIMENSION for access, we lock RETAIL.ORDERDETAIL for access, we lock RETAIL.ORDERHEADER for access, and we lock RETAIL.CUSTOMERS for access.

2) Next, we execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from RETAIL.CUSTOMERS by way of an all-rows scan with no residual conditions into Spool 4 (all_amps), which is duplicated on all AMPs. The size of Spool 4 is estimated with high confidence to be 152 rows. The estimated time for this step is 0.03 seconds.

2) We do an all-AMPs partial SUM step to aggregate from RETAIL.ORDERDETAIL by way of an all-rows scan with a condition of ("NOT (RETAIL.ORDERDETAIL.ProductNumber IS NULL)"), and the grouping identifier in field 1. Aggregate Intermediate Results are computed locally, then placed in Spool 7. The input table will not be cached in memory, but it is eligible for synchronized scanning. The size of Spool 7 is estimated with low confidence to be 6,298 rows. The estimated time for this step is 0.41 seconds.

3) We do an all-AMPs RETRIEVE step from Spool 7 (Last Use) by way of an all-rows scan into Spool 6 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 6 by row hash. The size of Spool 6 is estimated with no confidence to be 6,298 rows.

4) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an all-rows scan, which is joined to RETAIL.ORDERHEADER by way of an all-rows scan. Spool 4 and RETAIL.ORDERHEADER are joined using a single partition hash_ join, with a join condition of ("CustomerId = RETAIL.ORDERHEADER.CustomerId"). The result goes into Spool 9 (all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 9 by row hash. The size of Spool 9 is estimated with no confidence to be 5,712 rows. The estimated time for this step is 0.25 seconds.

5) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from RETAIL.PRODUCT_DIMENSION by way of an all-rows scan with no residual conditions into Spool 10 (all_amps), which is duplicated on all AMPs. The size of Spool 10 is estimated with high confidence to be 230 rows. The estimated time for this step is 0.03 seconds.

2) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of a RowHash match scan, which is joined to Spool 9 (Last Use) by way of a RowHash match scan. Spool 6 and Spool 9 are joined using a merge join, with a join condition of ("OrderCode = OrderCode"). The result goes into Spool 11 (all_amps), which is built locally on the AMPs. The size of Spool 11 is estimated with no confidence to be 5,712 rows. The estimated time for this step is 0.21 seconds.

6) We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of an all-rows scan, which is joined to Spool 11 (Last Use) by way of an all- rows scan. Spool 10 and Spool 11 are joined using a single partition hash join, with a join condition of ("ProductNumber = ProductNumber"). The result goes into Spool 3 (all_amps), which is built locally on the AMPs. The size of Spool 3 is estimated with no confidence to be 5,712 rows. The estimated time for this step is 0.20 seconds.

7) We do an all-AMPs 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 12. The aggregate spool file will not be cached in memory. The size of Spool 12 is estimated with no confidence to be 3,156 rows. The estimated time for this step is 2.54 seconds.

8) We do an all-AMPs RETRIEVE step from Spool 12 (Last Use) by way of an all-rows scan into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 3,156 rows. The estimated time for this step is 0.15 seconds.

9) 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.

Table Definition and statistics:

     CREATE SET TABLE RETAIL.ORDERHEADER ,NO FALLBACK ,
           NO BEFORE JOURNAL,
           NO AFTER JOURNAL,
           CHECKSUM = DEFAULT
           (
            OrderNumber DECIMAL(18,0),
            OrderCode INTEGER,
            OrderDate DATE,
            ClosedDate DATE,
            EmployeeId INTEGER,
            CustomerId DECIMAL(18,0),
            BranchCode INTEGER,
            OrderMethodCode INTEGER,
            OrderYear INTEGER,
            OrderMonth INTEGER)
     UNIQUE PRIMARY INDEX ( OrderCode );

Statistics on:

     OrderCode

     ---------------------------------------------------------------

     CREATE SET TABLE RETAIL.ORDERDETAIL ,NO FALLBACK ,
           NO BEFORE JOURNAL,
           NO AFTER JOURNAL,
           CHECKSUM = DEFAULT
           (
            Surr_Key INTEGER,
            Customer_Id INTEGER,
            OrderCode INTEGER,
            OrderDetailCode DECIMAL(18,0),
            ProductNumber DECIMAL(18,0),
            OrderQty FLOAT,
            Discount FLOAT,
            ProductCost FLOAT,
            ProductPrice FLOAT,
            ProfitMargin FLOAT,
            UnitsReturned BYTEINT)
     PRIMARY INDEX ( OrderCode );

Statistics on:

     OrderCode
     ProductNumber
     OrderCode,ProductNumber

     ---------------------------------------------------------------

     CREATE SET TABLE RETAIL.CUSTOMERS ,NO FALLBACK ,
           NO BEFORE JOURNAL,
           NO AFTER JOURNAL,
           CHECKSUM = DEFAULT
           (
            CustomerId DECIMAL(18,0),
            CustomerName VARCHAR(255) CHARACTER SET UNICODE NOT CASESPECIFIC,
            CustomerAlias VARCHAR(320) CHARACTER SET UNICODE NOT CASESPECIFIC,
            StreetAddress VARCHAR(970) CHARACTER SET UNICODE NOT CASESPECIFIC,
            CityName VARCHAR(60) CHARACTER SET UNICODE NOT CASESPECIFIC,
            RegionName VARCHAR(60) CHARACTER SET UNICODE NOT CASESPECIFIC,
            CountryName VARCHAR(80) CHARACTER SET UNICODE NOT CASESPECIFIC,
            CustomerType VARCHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC,
            Industry VARCHAR(60) CHARACTER SET UNICODE NOT CASESPECIFIC,
            CustomerSinceDate DATE)
     UNIQUE PRIMARY INDEX ( CustomerId );

Statistics on:

     CustomerId
     CustomerName
     ---------------------------------------------------------------

     CREATE SET TABLE RETAIL.PRODUCT_DIMENSION ,NO FALLBACK ,
           NO BEFORE JOURNAL,
           NO AFTER JOURNAL,
           CHECKSUM = DEFAULT
           (
            ProductNumber DECIMAL(18,0),
            ProductName VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC,
            Description VARCHAR(255) CHARACTER SET UNICODE NOT CASESPECIFIC,
            LanguageCode VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC,
            UnitPrice FLOAT,
            ProductionCost FLOAT,
            MARGIN FLOAT,
            IntroductionDate DATE FORMAT 'YY/MM/DD',
            PICTURE VARCHAR(32) CHARACTER SET UNICODE NOT CASESPECIFIC,
            CurrentInventory INTEGER,
            ProductTypeCode INTEGER,
            ProductType VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC,
            ProductLineCode INTEGER,
            ProductLine VARCHAR(40) CHARACTER SET UNICODE NOT CASESPECIFIC)
     UNIQUE PRIMARY INDEX ( ProductNumber );

Statistics on:

     ProductNumber
     ProductLine
     ProductName,ProductLine


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

Attachments

Library

Quick Reference