Forum

Discussions specific to version 8.1
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Reducing number of partitions when partition on date

by markc » Wed Mar 25, 2015 10:21 am

I have used the partitioned images feature in version 8 with a number of tables.

However, I now have an image that I want to partition by date, but I have more than 10,000 dates per RS. If I was partitioning on an integer field I would use "x / y" or "x mod y" to reduce the range of x, but I cannot do that with a date - how should I handle this?
Reply with quote Top
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Re: Reducing number of partitions when partition on date

by markc » Wed Mar 25, 2015 10:26 am

An example might help here. First of all, create a view with a date column with 1 billion rows over a range of 100 dates:

create ram only table parttest(a date, b bigint);

insert into parttest select date '1970-01-01' + cast(value / 1000000 as interval day(5)), value from values between 1 and 1000000000;

create view vparttest as select * from parttest;

Now if I partition by the date field, I can see the improvement in performance when I query with a date predicate (see how query 8 below takes 0.4 seconds, but query 9 is 0.0 seconds):

>create view image vparttest partition image by (a);
Query 7 Complete ---- 0:24.0 0:24.0 0:24.0
>select count(*) from vparttest;
COUNT(*)
1000000000
Query 8 1 row ---- 0:00.0 0:00.4 0:00.4
>select min(a), max(a) from vparttest;
MIN(A)| MAX(A)
1970-01-01|1972-09-27
Query 9 1 row ---- 0:00.0 0:00.8 0:00.8
>select count(*) from vparttest where a = '1970-01-01';
COUNT(*)
999999
Query 10 1 row ---- 0:00.0 0:00.0 0:00.0

I can also partition on an expression, so in this case I use the difference between the date field and a constant, and divide that by 10 to reduce the number of partitions from 100 to 10 (clearly you had a bigger range than 100 in your original example, but this shows the principle). See how the final query still comes back in 0.0 seconds, indicating the partitioning is used:

>drop view image vparttest;
Query 16 Complete ---- 0:00.1 0:00.1 0:00.1
>create view image vparttest partition image by ((a - date '1970-01-01') / 10);
Query 17 Complete ---- 0:24.8 0:24.8 0:24.8
>select count(*) from vparttest where a = '1970-01-01';
COUNT(*)
999999
Query 18 1 row ---- 0:00.0 0:00.0 0:00.0


Finally, we confirm that if the view has an unpartitioned image, the predicated query takes a lot longer (0.6 seconds in this case):

>drop view image vparttest;
Query 19 Complete ---- 0:00.1 0:00.1 0:00.1
>create view image vparttest;
Query 20 Complete ---- 0:14.3 0:14.3 0:14.3
>select count(*) from vparttest where a = '1970-01-01';
COUNT(*)
999999
Query 21 1 row ---- 0:00.0 0:00.6 0:00.6
>
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest

cron