All Forums Database
MaximeV 19 posts Joined 11/13
16 Oct 2014
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:
You must sign in to leave a comment.