Archives of the TeradataForum

Message Posted: Mon, 19 Nov 2012 @ 20:23:31 GMT

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

Guyz,

I tried following options.

Option 1

     SELECT
        RANK(subline_id ASC)+ MAX_EDW_ID,  --AUTO GENERATED EDW_ID
        instance_id,
        test
     FROM
         ttmp.test_dipti_csum  AS STAGE
            , (
                SELECT COALESCE(MAX(subline_id), 0)
                FROM ttmp.test_dipti_csum
              ) AS TARGET ( MAX_EDW_ID );

Option 2

     SELECT CSUM(1,1)+b.Max_Id,
            'test'
     FROM ttmp.test_dipti_csum a
     CROSS JOIN
       ( SELECT ZEROIFNULL(MAX(subline_id)) as Max_Id
         FROM ttmp.test_dipti_csum
       ) b

They are working fine as expected but there is also a problem associated with both the queries to get the SK. Lets say if we have huge million data in source table then the performance is quite slow. If we look at the explain plan then it says "Spool 7 and ttmp.a are joined using a product join, with a join condition of ("(1=1)") " which is may causing performance issue.

     6) We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of an
        all-rows scan, which is joined to ttmp.a by way of an all-rows
        scan with no residual conditions.  Spool 7 and ttmp.a are joined
        using a product join, with a join condition of ("(1=1)").  The
        result goes into Spool 6 (all_amps), which is built locally on the
        AMPs.  The size of Spool 6 is estimated with low confidence to be
        80 rows (1,520 bytes).  The estimated time for this step is 0.01
        seconds.

Any other way to create SK is appraciatable. I

Thanks,

Dipti Prakash

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