Archives of the TeradataForum
Message Posted: Sat, 22 Oct 2005 @ 09:12:33 GMT
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 :
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 ?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|