Archives of the TeradataForum

Message Posted: Thu, 04 Jun 2015 @ 22:21:32 GMT

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

HI Forum,

I have partitioned a table on Timestamp column and when I use the same column in a 1-1 view in Query by providing Date range, am getting the benefit of partitions and quite fast.

But, the requirement here is, We have a coalesce logic on partition column to deduce another column from PI columns with partition by clause inside the view.

When I use this view, the optimizer is going into FULL TABLE SCAN even though I provide the date range in the query.

My table is quite huge and the coalesce logic to deduce the column is business requirement. I cannot change that.

Can you please tell me how can I get the benefit of partitioning in this case? Or the Best possible options to handle this coalesce logic

Thanks very much!

FYI: Table partitioned on UPDATE_TIME:

     CREATE MULTISET TABLE DBA_DATA.TAB_500 ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT,
          DEFAULT MERGEBLOCKRATIO
          (
           JOB_ID INTEGER
           ...
           ...
           Instance_id VARCHAR(13) CHARACTER SET LATIN NOT CASESPECIFIC,
           Update_time TIMESTAMP(6) COMPRESS ,
           Maintagref VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC,
           Parenttagref VARCHAR(13) CHARACTER SET LATIN NOT CASESPECIFIC,
           VALID_FROM_Date_TS TIMESTAMP(6) NOT NULL,
           VALID_TO_Date_TS TIMESTAMP(6) NOT NULL)
           PRIMARY INDEX ( Instance_id ,Maintagref ,Parenttagref )
           PARTITION BY RANGE_N(Update_time
               BETWEEN TIMESTAMP '2000-01-01 00:00:00.000000+01:00' AND TIMESTAMP '2030-12-31 23:59:59.999999+01:00'
               EACH INTERVAL '1' MONTH , NO RANGE, UNKNOWN);

View: With Coalesce Logic:

     REPLACE VIEW DBA_DATA.TAB_500_COALESCE_LOGIC_V AS LOCKING ROW for access SELECT
        JOB_ID ,
        ..
        ..
        INSTANCE_ID,
        ,Maintagref
        ,Parenttagref
         ,UPDATE_TIME AS VALID_FROM_Date_TS
        ,COALESCE( MAX(UPDATE_TIME)
            OVER ( PARTITION BY Maintagref,Instance_id,Parenttagref
                   ORDER BY Update_time ASC ROWS
                   BETWEEN 1 FOLLOWING AND 1 FOLLOWING  )
        ,MAX(VALID_TO_Date_TS )
            OVER ( PARTITION BY INSTANCE_ID,MAINTAGREF,PARENTTAGREF
                   ORDER BY MSG_ID, VALID_TO_Date_TS)) AS VALID_TO_Date_TS
        FROM DBA_DATA.TAB_500 ;

View: Direct 1-1 View:

     REPLACE VIEW DBA_DATA.TAB_500_Direct_View_V AS LOCKING ROW for access SELECT
        JOB_ID ,
        ..
        ..
        INSTANCE_ID,
        ,Maintagref
        ,Parenttagref
        ,UPDATE_TIME AS VALID_FROM_Date_TS
        ,VALID_TO_Date_TS
     FROM DBA_DATA.TAB_500
     ;

Sample Query:

     SELECT
        FK_TASK_STATUS, PARENTTAGREF, VALID_TO_Date_TS, VALID_FROM_Date_TS
     FROM DBA_DATA.TAB_500_COALESCE_LOGIC_V

     -- from DBA_DATA.TAB_500_DIRECT_VIEW_V
           /*  Here, Its Taking the partition into consideration
               and running in 10seconds, Coz its a1-1 view*/

     WHERE FK_TASK_STATUS = '4'
           ...
           ...
           AND VALID_FROM_Date_TS between  DATE '2015-04-01' AND DATE '2015-05-31'
           AND VALID_TO_Date_TS = CAST('2999-12-31 23:59:59.999999' AS TIMESTAMP(6));

Thanks,

Sravan.

Sravan Kumar Bodla
TERADATA DBA
TERADATA 12 DBA Certified
ITIL V3 Foundation Certified

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