Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jola.

Asked: March 18, 2021 - 5:34 pm UTC

Last updated: April 28, 2022 - 4:19 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi Tom,
I'm planning to partition a 4 G table (i.e. table A)
1) the first partition is by date interval 3 months
2) subpartition is by hash custumer_id (unique values)

I need to decide if the hash partition should be 8 or 16.
- if I use 8, each partition size is around 16 MB
- if I use 16, each partition size is around 8 MB

I'm also investigating other tables for partitioning (10G to 20G) (i.e. table B)
-first by list
then subpartition by hash
My understanding is that in order to take advantage of partition-wise joins the hash partitions should be equal in both tables.
Meaning:
if I use interval partition by date and hash partition 16 in table A
then I should use a list partition and hash partition 16 in table B.
Hash Partitions in table A and B = 16

Please advise
Best Regards
Jola










and Connor said...

if I use 8, each partition size is around 16 MB


which means each 3 month partition is around 128MB in size.

This sounds a pretty low size to be considering partitioning *based* on a volume basis.

If you've got extreme concurrency, then perhaps such a fine grain is justified by this partitioning.

Or, if you're trying to avoid indexing by using very small granules...

But if not either of those things, I would imagine you probably don't need sub-partitioning here.

(Your comments on partition-wise join are valid, ie, you need things to be nicely matched)

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Partitioning recommendation

Jola Soares, March 24, 2021 - 5:46 pm UTC

Thank you for your feedback . .
is there any recommendation for minimum partition size when considering partitions?
FYI . .the table will grow to a much higher volume, that is why I'm considering 16 partitions at this point and take advantage of the partition-wise joins on already high volume tables that are hash 16 partitioned.
Best Regards
Jola
Connor McDonald
March 26, 2021 - 7:00 am UTC

is there any recommendation for minimum partition size when considering partitions?

Only based on your business requirements, not a hard and fast rule.

For example, I might have a high transaction system that I want to minimize indexes on. To enable queries to run fast on a date range *without* a date index, I might choose to partition at a fine grained level.

Conversely, if in the same system I don't mind an additional index, I might partition at a much higher level (week/month/etc).

Partitioning is like *every* database feature - you utilise it to meet business objectives.

Partitioning recommendation

Jola Soares, March 29, 2021 - 1:43 pm UTC

Thank you!
I have an additional scenario that I would like your input on:
table: 130 M records, 25 G size
Composite PK: coustomer_id, order_date, product_id, transaction_code

queries are using "where order_date " . ..
joins are on where customer_id =customer_id or product_id= product_id

I'm considering
1) interval range partition on order_date (3 months)
2) Hash partition on all primary key columns: coustomer_id, order_date, product_id, transaction_code - hash 16
3) local unique index on PK columns

Please note: the table will be partition by order_date 2 times-
range partition then in hash partition - can this approach present any possible issues?

Please advise
Best Regards
Jola



Partitioning based on data volume

Narendra, April 26, 2022 - 11:06 am UTC

Hello Connor/Chris,

Is there a way to achieve LIST partitioning based on the volume of data in a table? For e.g. how can I LIST partition a table that stores transactions for accounts, based on number of transactions per account? Assume I want to have only 3 partitions for 3 categories of ACCOUNTS (SMALL, MEDIUM, LARGE) and the accounts will be stored/distributed in partitions based on the volume of transactions in the table. I do understand there will be challenges when accounts (and its transactions) move from one category to another but am wondering whether there is a use case for this or if I am thinking this in a wrong way.
I am happy to define the criteria for the volumes explicitly.

Thanks in advance
Connor McDonald
April 28, 2022 - 4:19 am UTC

Hash partitioning will generally evenly distribute data across partitions, but that is volume not rate, and you need something that is going to be a good hash input.

If you looking for something more explicit, ie, "Based on this set of rules, I want customer X to be in partition Y", then you'd need to implement that yourself. It might be something that initially puts people in a default partition but then you come along later and split that partition into its desired distribution.

(That is beginning to sound like overkill to me though)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database