Partitioning - default partition ?

kamil

Hi,

Im curious if i can manage partitions like in other dbs (postgres,edb etc.)...
I cannot find proper answer in docs and other sources so i will need your help...

My table looks like this:

count | potential_id_key
-----------+------
958800406 | 9
169481205 | 8
13249261 | 7
6070862 | 11
932035 | 22
184952 | 32
120417 | 10
37177 | 3
4090 | 5
395 | 19
227 | 14
192 | 2
99 | 33
48 | -200
18 | 21
6 | 1
1 | 23

So i believe, that the best option will be to partition by potential_id_key...but

Is is possible to create only 3 partitions i.e.
- partition_key=9
- partition_key=8
- partition_key=7
because those 3 keys contains biggest number of elements and other values store in DEFAULT partition?

If something like DEFAULT partition exists in HP VERTICA?

 

Im thinking about using projections as a workaround, but im just wondering if there is a simplest way to do this...

 

Br,

Kamil

Adrian_Oprea_1

 Hi Kamil, 

What you can do is make use of a derived default value for your partition_key column.

 

See example:

 

CREATE TABLE
 abc
 (
 data INT,
 potential_id_key INT,
 partition_key VARCHAR(10) NOT NULL DEFAULT
 CASE
 WHEN (abc.potential_id_key = 9)
 THEN 9
 WHEN (abc.potential_id_key = 8)
 THEN 8
 WHEN (abc.potential_id_key = 7)
 THEN 7
 ELSE 1
 END
 )
 PARTITION BY
 (
 abc.partition_key
 );

 

 

  - let`s say that your table has 2 columns and the third one "partition_key" is created just for the sole purpose of marking the partition location for that row of data based on the value of potential_id_key column.

 

 - every time you do an insert in the table the partition_key will be populated.

 

Hope this helped ! 

 

kamil

Hi,

 

Thanks for a quick reply...

 

It sounds ok. It can be somekind of workaroud. I will have to check if this will have some influence on the performance...

 

One more question.

Do I need to change all projections order definitions? Im just wondering, because i remember (from official docs) that the order in projection should contains partition_key column if first place.