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

Attachments

Library

Quick Reference