[Webinar] Streamline your web hosting managementRegister Today


List or Range Partitioning

Posted on 2011-04-27
Medium Priority
Last Modified: 2013-11-16
Hi Yall.

Every month a flatfile approx 500mb will be loaded into a Finance Oracle Datamart via Informatica.  The requirements are to keep 24 periods of data.  I would have said Range partitioning was the best Partitionign strategy but one could also have List as P1,P2,P3 etc.  Ive written a SP to accomodate both but wonder what would be the best strategy ?


Question by:BPMonk
LVL 22

Accepted Solution

Helena Marková earned 336 total points
ID: 35474255
In our DWH we have both RANGE and LIST partitioning.
In the fact tables,MVs where "date" column contains data such as '2009Q1','2009Q2' ....(column is VARCHAR2)  etc. we use LIST partitioning.
In the fact tables where "date" column is really DATE we use RANGE partitioning.

This is very useful documentation about partitioning:
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 332 total points
ID: 35474634
I agree with the above post.

You partition mainly for performance.  This is based on how the data will be retrieved.  The other main reason is manipulating large amounts of data quickly.  For example: dropping an entire partition is quicker than deleting all the rows.

What are your '24 periods'?  This should be the driving factor.  If they are 24 months and actually a date, I would probably go with date range.

Once you figure out the 'why' you are partitioning, the choice should be easy.
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 332 total points
ID: 35477258

On the date column, the best choice is range partitioning because queries will use the date column (such as date > ... or date between ... and ...).
If you have a column with the month identifier, then list partitioning will be used if the queries uses month=...

So that depends on the column and how it will be used in query predicates.

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36243513
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question