Skip to Main Content
  • Questions

  • does partition volume impact ad-hoc queries

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Michael Nosovsky.

Asked: July 23, 2016 - 11:01 pm UTC

Last updated: July 25, 2016 - 7:02 pm UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hi Tom,

We are upgrading from Oracle 8i which is running on a standalone Unix server to 11g release 2 to Exadata.
We are migrating Data Warehouse database with large partitioned tables (1000 million and more rows) partitioned monthly,quarterly and yearly depending on the number of rows. In term of size, for instance, the size of monthly partitions is between 30-40 gb, quarterly are larger, about 50-60gb.
My question is:
What is Oracle recommendation about partition's size, specifically on Exadata?
Does it any have any effect on the queries' (selects) performance, which are using partitioning key columns, say date?
Would it be advisable to keep the same approach about types of partitions currently used(monthly, quarterly, yearly) or it does not really matter on Exadata just because it is much stronger and improved system, so all monthly can be put together into quarterly or even yearly?
We thing it will reduce the maintenance cost if no impact on the performance.

Thank you very much. Your opinion is greatly appreciated.

Michael

and Connor said...

The number of partitions might impacts the volume of data read (if you are using partition pruning to get query benefit).

There should be almost no maintenance cost to partitions - we have interval partitions for automatic addition of partitions.

For me, I would typically lean to *smaller* partitions if possible, keeping in mind that I probably dont want thousands of partition for an object due to the dictionary overhead. Other than that, smaller partitions will probably give me more chances to eliminate data from consideration for queries that use the partitioning column within predicates.

For example, I've worked on sites where we had daily or weekly partitions for current year, and then merged older partitions into monthly or yearly partitions for older data (which is rarely queried). This way our active data is easily "sliced and diced" and the merging of older data keeps the partition count under control

Hope this helps.

Rating

  (2 ratings)

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

Comments

Partitions size on EXADATA

Michael Nosovsky, July 25, 2016 - 5:05 pm UTC

The answer has helpful information, but it does not answer the main question: if the partitioning strategy is different on EXADATA versus not EXADATA.
Chris Saxon
July 25, 2016 - 7:02 pm UTC

I would not think of it in terms of exadata vs non-exadata, but more in the general sense of server firepower.

If I can gigabytes per second, then I *might* lean toward larger partition sizes to keep parse times and dictionary size smaller because I know that the reduced granularity will be made up for by my I/O bandwidth.

(I'm assuming here that we're well above any sizes that might impact things like storage indexes etc...ie, even our 'smallest' parttion sizes will be measured in megabytes not kilobytes)

A reader, July 26, 2016 - 2:18 am UTC

Thank you!
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

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.