Table partitioned on timestamp col

Database
Teradata Employee

Table partitioned on timestamp col

Posted this on latest Paulsinclair blog post (2012) but I guess I'll it a try here :

Hi, I have a question about partition elimination in 14.00(.06.09) with a table partitioned on a timestamp column.

I've prepared 3 cases studies but first of all we need a materialized calendar table :

CREATE SET TABLE CALN_TEST ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

CALN_DT DATE FORMAT 'YYYY-MM-DD',

DAY_OF_WEEK INTEGER,

DAY_OF_MONTH INTEGER,

DAY_OF_YEAR INTEGER,

DAY_OF_CALN INTEGER,

WEEKDAY_OF_MONTH INTEGER,

WEEK_OF_MONTH INTEGER,

WEEK_OF_QUARTER INTEGER,

WEEK_OF_YEAR INTEGER,

WEEK_OF_CALN INTEGER,

MONTH_OF_QUARTER INTEGER,

MONTH_OF_YEAR INTEGER,

MONTH_OF_CALN INTEGER,

QUARTER_OF_YEAR INTEGER,

QUARTER_OF_CALN INTEGER,

YEAR_OF_CALN INTEGER,

WEEKBEGIN_DT DATE FORMAT 'YYYY-MM-DD',

WEEKEND_DT DATE FORMAT 'YYYY-MM-DD',

MONTHBEGIN_DT DATE FORMAT 'YYYY-MM-DD',

MONTHEND_DT DATE FORMAT 'YYYY-MM-DD',

QUARTERBEGIN_DT DATE FORMAT 'YYYY-MM-DD',

QUARTEREND_DT DATE FORMAT 'YYYY-MM-DD',

YEARBEGIN_DT DATE FORMAT 'YYYY-MM-DD',

YEAREND_DT DATE FORMAT 'YYYY-MM-DD')

UNIQUE PRIMARY INDEX ( CALN_DT );

INSERT INTO CALN_TEST

SELECT

CALENDAR_DATE,

DAY_OF_WEEK,

DAY_OF_MONTH,

DAY_OF_YEAR,

DAY_OF_CALENDAR,

WEEKDAY_OF_MONTH,

WEEK_OF_MONTH ,

WEEK_OF_QUARTER,

WEEK_OF_YEAR,

WEEK_OF_CALENDAR,

MONTH_OF_QUARTER,

MONTH_OF_YEAR,

MONTH_OF_CALENDAR,

QUARTER_OF_YEAR,

QUARTER_OF_CALENDAR,

YEAR_OF_CALENDAR,

WEEKBEGIN,

WEEKEND,

MONTHBEGIN,

MONTHEND,

QUARTERBEGIN,

QUARTEREND,

YEARBEGIN,

YEAREND

FROM SYS_CALENDAR.BUSINESSCALENDAR

WHERE YEAR_OF_CALENDAR BETWEEN 1990 AND 2050;

COLLECT STATISTICS ON CALN_TEST INDEX ( CALN_DT );

next let's proceed with cases studies :

CASE 1:  table partitioned directly on timestamp column 1 day interval :

CREATE MULTISET TABLE ts1 (

Calling_Nbr VARCHAR(21) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Start_Time TIMESTAMP(0) WITH TIME ZONE NOT NULL,

DATA INTEGER )

PRIMARY INDEX (Calling_Nbr),

PARTITION BY RANGE_N(Start_Time BETWEEN TIMESTAMP '1990-01-01 00:00:00+00:00'

AND TIMESTAMP '2099-12-31 23:59:59+00:00'

EACH INTERVAL '1' DAY );

then let's try this query :

EXPLAIN SELECT *  FROM ts1 INNER JOIN CALN_TEST CAL ON ( CAST(ts1.start_time AS DATE) = CAL.CALN_DT)

WHERE cal.caln_dt='2014-08-25';

sadly, partition elimination does not occur.

CASE 2 : like in the blog entry, table partitioned directly on timestamp column casted as date still with 1 day interval :

CREATE MULTISET TABLE ts2 (

Calling_Nbr VARCHAR(21) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Start_Time TIMESTAMP(0) WITH TIME ZONE NOT NULL,

DATA INTEGER )

PRIMARY INDEX (Calling_Nbr),

PARTITION BY RANGE_N(CAST(Start_Time AS DATE AT LOCAL ) BETWEEN DATE '1990-01-01'

AND DATE '2099-12-31'

EACH INTERVAL '1' DAY );

let's try the same query again :

EXPLAIN SELECT *  FROM ts2 INNER JOIN CALN_TEST CAL ON ( CAST(ts2.start_time AS DATE) = CAL.CALN_DT)

WHERE cal.caln_dt='2014-08-25';

still no luck for me, partition elimination does not occur.

CASE 3 : data redundancy with date column added and table partitioned on this new column :

CREATE MULTISET TABLE ts3 (

Calling_Nbr VARCHAR(21) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Start_Time TIMESTAMP(0) WITH TIME ZONE NOT NULL,

Start_dt DATE NOT NULL,

DATA INTEGER )

PRIMARY INDEX (Calling_Nbr),

PARTITION BY RANGE_N(Start_dt BETWEEN DATE '1990-01-01'

AND DATE '2099-12-31'

EACH INTERVAL '1' DAY );

now the query is :

EXPLAIN SELECT *  FROM ts3 INNER JOIN CALN_TEST CAL ON ( ts3.start_dt  = CAL.CALN_DT)

WHERE cal.caln_dt='2014-08-25';

and now partition elimination occurs and data from a single partition is retrieved.

¤ Does anyone have an explanation about this behaviour ?

¤ Are there any improvements in latest releases ?

¤ Is it not recommanded to partition table on timestamp column ?

Thanks.

On a side note, i'll add that I know the query explained is pretty stupid ( but replace caln_dt filter by month or week restriction and this kind of query can happen a lot, especially on 3rd party bi tools )

Tags (1)