How to do the Partation of the Table

Posted on 2012-09-05
Medium Priority
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

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

Thanks !!

Expert Comment

ID: 38371168
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38371211
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.

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 35

Accepted Solution

Mark Geerlings earned 2000 total points
ID: 38372787
"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).

Expert Comment

by:Sanjeev Labh
ID: 38424701
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month17 days, 8 hours left to enroll

829 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