Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sat, 22 Oct 2005 @ 09:12:33 GMT


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


Subj:   Help on Join Index with Outer Joins, please.
 
From:   David Clough

I've got a query that chooses a Join Index when the number of joins in the query is the same as those in the Join index, but does not pick it when I include a join to another table which is not in the Join Index.

Here's the query:

     SELECT  'INVOICE' AS SUMMARY_TYPE
     /*COALESCE(DED1.SALES_TERRITORY_NUM, PC1.SALES_TERRITORY_NUM, ' ') AS TERRITORY_NUM*/
     ,PC1.SALES_TERRITORY_NUM AS TERRITORY_NUM
     ,PC1.SALES_TERRITORY_NUM
     ,DA1.ACCOUNT_NUMBER
     ,DA1.DIVISION_NUMBER
     ,DA1.RATING_DEPOT
     ,CAL1.DTE_YRWK_NR_KPN
     ,SUM(INL1.INV_CONS_ITEMS) AS NUM_ITEMS
     ,SUM(INL1.INV_CONS_WEIGHT) AS NUM_KILOS
     ,SUM(INL1.INVOICE_LINE_AMOUNT) AS NET_REVENUE
     ,SUM(CASE WHEN COALESCE(INL1.CONSIGNMENT_NUMBER,0) > 0 THEN 1 ELSE 0 END)
     AS NUM_CONS
     FROM DEVUK_T.DIVISIONALACCOUNT DA1
     /*
     LEFT JOIN
      DEVUK_T.UK_DEDSLSTER DED1
     ON DA1.ACCOUNT_NUMBER=DED1.ACCOUNT_NUMBER
     AND DA1.DIVISION_NUMBER=DED1.DIVISION_NUMBER
     */
     LEFT JOIN
      DEVUK_T.UK_PCODESALESTER PC1
     ON PC1.ROUTING_TABLE_REF=DA1.ROUTING_TABLE_REF
     LEFT JOIN
      DEVUK_T.UK_INVOICE_LINE INL1
     ON DA1.ACCOUNT_NUMBER=INL1.ACCOUNT_NUMBER
     LEFT JOIN
      DEVCE_T.CALENDAR CAL1
     ON INL1.CONS_COLLECTED_DT=CAL1.DTE_DT
     GROUP BY 1,2,3,4,5,6,7

As you can see, WITHOUT the join to UK_DEDSLSTER I get the following Explain :

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct DEVUK_T."pseudo table" for read on a RowHash to prevent global deadlock for DEVUK_T.DIVISIONALACCOUNT_JI1.  
  2)Next, we lock DEVUK_T.DIVISIONALACCOUNT_JI1 for read.  
  3)We do an all-AMPs SUM step to aggregate from DEVUK_T.DIVISIONALACCOUNT_JI1 by way of an all-rows scan with no residual conditions, and the grouping identifier in field 1. Aggregate Intermediate Results are computed locally, then placed in Spool 4. The size of Spool 4 is estimated with no confidence to be 322,455 rows. The estimated time for this step is 5.84 seconds.  
  4)We do an all-AMPs RETRIEVE step from Spool 4 (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 322,455 rows. The estimated time for this step is 1.57 seconds.  
  5)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.  


Which is good news: it picks join index DIVISIONALACCOUNT_JI1 (although worryingly it has 'no confidence', even though I've collected stats on ALL the joining columns and on the Join Index).

But, when I un-comment the join to UK_DEDSLSTER it doesn't want to go anywhere near the Join Index.

The Join Index looks like this :

     CREATE JOIN INDEX DEVUK_T.DIVISIONALACCOUNT_JI1 AS
     SELECT
     (DA1.ACCOUNT_NUMBER AS ACC_NR
     ,DA1.DIVISION_NUMBER
     ,DA1.RATING_DEPOT
     ,DA1.ROUTING_TABLE_REF AS RTR_DA
     ,DA1.ROWID)
     ,
     (PC1.SALES_TERRITORY_NUM
     ,PC1.ROUTING_TABLE_REF AS RTR_PC
     ,INL1.INVOICE_NUMBER
     ,INL1.INVOICE_LINE_NUM
     ,INL1.INV_CONS_ITEMS AS NUM_ITEMS
     ,INL1.INV_CONS_WEIGHT AS NUM_KILOS
     ,INL1.CONSIGNMENT_NUMBER AS CON_NUMBER
     ,INL1.INVOICE_LINE_AMOUNT AS NET_REVENUE
     ,INL1.CONS_COLLECTED_DT
     ,CAL1.DTE_YRWK_NR_KPN
     ,CAL1.DTE_DT)
     FROM
      DEVUK_T.DIVISIONALACCOUNT DA1
     LEFT JOIN
      DEVUK_T.UK_PCODESALESTER PC1
     ON PC1.ROUTING_TABLE_REF=DA1.ROUTING_TABLE_REF
     LEFT JOIN
      DEVUK_T.UK_INVOICE_LINE INL1
     ON DA1.ACCOUNT_NUMBER=INL1.ACCOUNT_NUMBER
     LEFT JOIN
      DEVCE_T.CALENDAR CAL1
     ON INL1.CONS_COLLECTED_DT=CAL1.DTE_DT
      PRIMARY INDEX (ACC_NR);

It has the same Primary Index as DEVUK_T.DIVISIONALACCOUNT and this has the same Primary Index as DEDSLSTER (all a NUPI on Acount_Number).

So, the result of the Join Index is distributed on the same Amp as DEDSLSTER - so why doesn't it still use the Join Index ?!!

I thought Left Joins were fine with Join Indexes.

Any ideas ?


_______________________
Dave Clough
Database Designer
Express ICS



     
  <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