Re: Partition with check constraint with "like"

On Sat, 22 May 2021 at 04:38, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:
> I am trying to create partitions on the table which have around 2BIL records and users will always look for the "name", its not possible to create a partition with a list, so we are trying to create a partition-based first letter of the name column. name column has a combination of alpha numeric values.

Going by the description of your use case, I think HASH partitioning
might be a better option for you. It'll certainly be less painful to
initially set up and maintain.

Here's an example:

create table mytable (a text) partition by hash(a);
create table mytable0 partition of mytable for values with(modulus 10,
remainder 0);
create table mytable1 partition of mytable for values with(modulus 10,
remainder 1);
create table mytable2 partition of mytable for values with(modulus 10,
remainder 2); --etc

Change the modulus to the number of partitions you want and ensure you
create a partition for each modulus. In this case, it would be 0 to 9.

David