Re: On partitioning

On Tue, Dec 9, 2014 at 12:59 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Tue, Dec 9, 2014 at 8:08 AM, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
> wrote:
>> > From: Robert Haas [mailto:robertmhaas(at)gmail(dot)com]
>> > On Sat, Dec 6, 2014 at 2:59 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
>> > wrote:
>> > >> I guess you could list or hash partition on multiple columns, too.
>> > >
>> > > How would you distinguish values in list partition for multiple
>> > > columns? I mean for range partition, we are sure there will
>> > > be either one value for each column, but for list it could
>> > > be multiple and not fixed for each partition, so I think it will not
>> > > be easy to support the multicolumn partition key for list
>> > > partitions.
>> >
>> > I don't understand. If you want to range partition on columns (a, b),
>> > you say that, say, tuples with (a, b) values less than (100, 200) go
>> > here and the rest go elsewhere. For list partitioning, you say that,
>> > say, tuples with (a, b) values of EXACTLY (100, 200) go here and the
>> > rest go elsewhere. I'm not sure how useful that is but it's not
>> > illogical.
>> >
>>
>> In case of list partitioning, 100 and 200 would respectively be one of the
>> values in lists of allowed values for a and b. I thought his concern is
>> whether this "list of values for each column in partkey" is as convenient to
>> store and manipulate as range partvalues.
>>
>
> Yeah and also how would user specify the values, as an example
> assume that table is partitioned on monthly_salary, so partition
> definition would look:
>
> PARTITION BY LIST(monthly_salary)
> (
> PARTITION salary_less_than_thousand VALUES(300, 900),
> PARTITION salary_less_than_two_thousand VALUES (500,1000,1500),
> ...
> )
>
> Now if user wants to define multi-column Partition based on
> monthly_salary and annual_salary, how do we want him to
> specify the values. Basically how to distinguish which values
> belong to first column key and which one's belong to second
> column key.
>

Perhaps you are talking about "syntactic" difficulties that I totally missed in my other reply to this mail?

Can we represent the same data by rather using a subpartitioning scheme? ISTM, semantics would remain the same.

... PARTITION BY (monthly_salary) SUBPARTITION BY (annual_salary)?

Thanks,
Amit