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: March 26, 2021 - 7:00 am UTC

Version: 12c

Viewed 100+ 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 we 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

  (2 ratings)

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



More to Explore

Administration

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