Archives of the TeradataForum
Message Posted: Thu, 03 Apr 2008 @ 10:58:04 GMT
we have recently upgraded one of our test machines to V2R6.2 from V2R5.1 and we have a performace issue afterwards. We are using huge transaction tables with PPI (hundreds of milions of rows) and simple view without from clause, that stands for a constant for effective date as follows:
CREATE SET TABLE dw.Transaction ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( Account_Nbr CHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, Effective_Date DATE FORMAT 'YYYY-MM-DD' NOT NULL, Post_Date DATE FORMAT 'YYYY-MM-DD' NOT NULL, Tran_Seq_Id DECIMAL(7,0) NOT NULL, â ¦. ) UNIQUE PRIMARY INDEX Transaction_UPPI ( Account_Nbr ,Effective_Date , Post_Date ,Tran_Seq_Id ) PARTITION BY RANGE_N(Post_Date BETWEEN DATE '1999-01-01' AND DATE '2030-12-31' EACH INTERVAL '1' MONTH ) INDEX Transaction_NUSI ( Account_Nbr ); REPLACE VIEW dw_meta.V_DW_Date AS SELECT cast(1080311 as date) DW_Date, 1 Date_Weight;
The problem is that new V2R6 is not using this view (and PPI) as effectivelly as V2R5 in conditional clauses. For example for this select:
SELECT * FROM dw.Transaction WHERE Post_Date = dw_meta.V_DW_Date.DW_Date
explains shows that on V2R5 only single one partition is searched on all amps,
3) We do an all-AMPs RETRIEVE step from a single partition of dw.Transaction with a condition of ( "dw.Transaction.Post_Date = DATE '2008-03-11'") with a residual condition of ("dw.Transaction.Post_Date = DATE '2008-03-11'") into Spool 1 (group_amps), which is built locally on the AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The size of Spool 1 is estimated with high confidence to be 2,375,660 rows. The estimated time for this step is 5.29 seconds.
but on V2R6 it is an all rows scan across all partitions. Furthermore, data from views with constants no longer seem to be interpreted as such, but are regulary joined as tables in V2R6.
6) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an all-rows scan, which is joined to dw.transaction by way of an all-rows scan with no residual conditions. Spool 5 and dw.transaction are joined using a product join, with a join condition of ("dw.transaction.Post_Date = DWE_DATE"). The input table dw.transaction will not be cached in memory, but it is eligible for synchronized scanning. The result goes into Spool 4 (group_amps), which is built locally on the AMPs. The size of Spool 4 is estimated with no confidence to be 2,375,660 rows. The estimated time for this step is 1 minute and 5 seconds.
Out of curriosity I have typed this on V2R6
explain SELECT * FROM dw.Transaction WHERE Post_Date = 1080311
and again only one partition is searched.
Does anybody know why the behaviour on V2R6 is not consistent? Is there any way to prevent this?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|