List or Range Partitioning

Posted on 2011-04-27
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

    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 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    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

    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 142

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Title # Comments Views Activity
    Query combine all charges 6 43
    oracle 10G 5 37
    CREATE TABLE syntax 4 30
    how to change the query from oracle to  mysql. 3 25
    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now