How to do the Partation of the Table

Posted on 2012-09-05
Last Modified: 2012-10-30

we are using oracle database 9i and database is in Unix server.
to acces oracle database we are Using SQLDEVELOPER:
In our database we have one Table which consits of 84million records:
To retervie the single query it is taking 20 min:
In that Table all columns are Indexed :
Please suggest!!!

Thanks in Advance.
Question by:nrajasekhar7

    Author Comment

    I want to do the Partion on the Timestamp cloumn ,
    Please Need urgent !!!

    Thanks !!
    LVL 9

    Expert Comment

    LVL 28

    Expert Comment

    This should also help you to understand what all partioning methods are available.

    Do you want to do range paritioning or list paritioning or hash paritioning is something you need to decide before you go ahead which depends on the data design/model/business needs.

    LVL 12

    Expert Comment

    LVL 34

    Accepted Solution

    "one Table which consists of 84 million records" and "all columns are Indexed".

    That does not look like a good idea to me!  Usually it is not an advantage to index all columns.

    Some of the links that others posted may give you the syntax you need to set up partitions of Oracle tables, but they usually don't give you specific advice or recommendations for specific business problems.

    I hope you already know that you cannot add partitions to a non-partitioned table.  Since you already have an existing table, if you want to partition that, you will need to rename it, then create a new, partitioned tble with the same name as the original table, then copy the data from the original table to the new table, then build any indexes that you need (and add any contraints, grants, triggers and default column values that your original table had).  This will likely require some application down time.

    How to partition your table will depend on how your users (or your application) query the data, and on how you manage older data in the table  (does it, or can it, get purged when it is older than some months or some years, or do you keep all of the data forever) etc.?

    I often partition large detail tables like this based on a date column with a value that doesn't change (typically a column with a name like "date_created").  These partitions can be one for each week, or one for each month, or one for each quarter, or year, etc. depending on how the data is queried and managed.  I also usually compress the historical paritions of partitioned tables like this, but leave the current partition (where new records are added daily) uncompressed until after the time period for this partition is closed.  Then I manually compress the partition for the time period that just ended.  This reduces the space used by large tables, plus usually gives better query performance than querying uncompressed data.

    I prefer to set up indexes on partitioned tables with partitions that match the partitions of the table.  These are called: "locally-partitioned indexes".  There are other indexing options, but this gives me the most flexibility, and it makes managing individual table partitions easier, because only the index partitions that match the table partition(s) that got moved, compressed or truncated need to be rebuilt, not the entire index(es).
    LVL 5

    Expert Comment

    by:Sanjeev Labh
    As markgeer has advised you would first need to decide upon the type of partition that you would be using. I would suggest since it is timestamp you should go for the range partition. Now to decide on the frequency of the partition i.e. yearly, monthly, weekly you need to check your data and query pattern. That is what generally when you query your data of what duration you do. Based on that your partition range must be decided. Once that is ready within the partition you can keep your local partition index i.e. that index would be for the particular partition only to provide faster access to the partition.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    729 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

    20 Experts available now in Live!

    Get 1:1 Help Now