select * from t1 where name = somename ? == equality match // if yes, hash partitioning may be helpful to a have reasonably balanced distribution
or
select * from t1 where name like 'some%'; ---- what would be the distribution of rows for such queries. i mean it can return 1 row or all rows or anything in between.
that may result in unbalanced partitioning.
then why partition at all ? 2B rows, if i go with 100KB size per row. that would be around 200GB.
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.
You can also use MINVALUE and MAXVALUE to mean unbounded at either end of the range.
But is there a particular need that you want to partition this way? It seems like it might be a bit painful to maintain, especially if you're not limiting yourself to ASCII or ANSI characters.
You might want to consider HASH partitioning if you're just looking for a way to keep your tables and indexes to a more manageable size. You've not really mentioned your use case here, so it's hard to give any advice.
