Archives of the TeradataForum

Message Posted: Thu, 03 Apr 2008 @ 10:58:04 GMT

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

Hello All,

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?

Best Regards

Lubomir

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