Add or Remove Parition from Partitioned table

The problem:
A table containing some old data 2000-2009 now contains 50mill rows

I would partition this table into year partitions.

Is it possible to remove a partition (every partition with data older than 2 years).
And then when needed add the partition again ?
So the partition should be detach and attachable from a script/batch.

Or is this not possible ?
LVL 39
Geert GOracle dbaAsked:
Who is Participating?
Geert GOracle dbaAuthor Commented:
That's what i have to do to partition of the table.
I was thinking about the steps afterwards ...

I wan't to remove the whole tablespace for 2000, 2001, 2002 up to 2007 data from the table
But I want to be able to add the tablespace for any particular year back into the table too.
Like switching on or off a tablespace.
ALTER TABLE DROP PARTITION drops a partition and its data. If you want to drop a partition but keep its data in the table, you must merge the partition into one of the adjacent partitions.

If you drop a partition and later insert a row that would have belonged to the dropped partition, the row will be stored in the next higher partition. However, if you drop the highest partition, the insert will fail because the range of values represented by the dropped partition is no longer valid for the table.

This statement also drops the corresponding partition in each local index defined on  table. The index partitions are dropped even if they are marked as unusable.

If there are global indexes defined on the  table, and the partition you want to drop is not empty, dropping the partition marks all the global, nonpartitioned indexes and all the partitions of global partitioned indexes as unusable.

When a table contains only one partition, you cannot drop the partition. You must drop the table.

The following example drops partition jan98:


Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

use EXCHANGE PARTITION to exchange a partition into a table
then you can drop the table or keep it and then you can exchange the table back to a partition again
Geert GOracle dbaAuthor Commented:
forgive me for not following completely.

after partitioning a table into tablespaces per year
i'm looking for this (in pseudo)

The indexes are defined local


Then when i want to find data for 2006, i would first do this:
I think you're getting a little confused between tablespaces and partitions.

Yes you can create a partitioned table with each partition in a different tablespace, however, you can't take "offline" those tablespaces as the partition will no longer be accessible and this will cause problems when trying to access the partitioned table.  First you need to "remove" the partition from the partitioned table so the remaning partitions can be used.

From you question, I gather you want to be able to "offline" older data, but bring it back "online" when a specific need arises.

This could be done using a combination of exchange partition statements and offlining/onlining the tablespaces.

The exchange partition statement is used to "remove" a partition from a table.  The tablespace in which the table exists could then be offlined.  Making the data available again would involve bringing the tablespace online and then exchanging the table into a partition so it belongs to the partitioned table again.
Alternative approach is to export the rows in the partition and deleting them.
When you need them - import ...
This is a long way but hopefully you will use it rare.

Anather alternative - put the rows in an additional table in a special dedicated tablespace, make the tablespace read-only, offline the tablespace and remove it.
Geert GOracle dbaAuthor Commented:
i'm still in the process of exploring the Oracle Database from a DBA point of view.
I'm familiar with the programmers view.
There are some very different things on this other side to see ... and i'm still learning.

The problem now is speed and finding records in the single non-partitioned table

I was wondering if it is enough to partition the table (per year) to make the queries go faster.
Or go the extra mile after partitioning and remove the data from table as well.

Or will this last step not improve the queries any more once the table has been partitioned ?
Of course partitioning (in combination with the SELECT statement and appropriate indexing and regular statistic collection  and more selective WHERE clause and placing tables and index on different spindels ... and ... and) will speed up the query.

But also removing (temporary or forever, with archiving) is an good alternative.
You have to predict how often the application will need the removed data
and the cost (many indexes!) of deletion.
Geert GOracle dbaAuthor Commented:
the indexes are defined as local
and they would access the historic data 1 or 2 maybe 3 times a year.
Calculate the number of rows affected and indexes involved.
The more indexes the more time delete works.
If you partition your table on year as you explained in your original question, then the only overhead of keeping old data (for older years) in your database is the space it takes up.  It will NOT affect the performance of queries that specifically use the partition key (year in this case) as these queries will not even attempt to access the older data.

There are one or two minor overheads, besides query performance, that need to be considered.  The first is global indexes - if you don't have any (and you don't mention them) then these will not be a problem.  When adding new partitions to a table with global indexes the index will need to be rebuilt.  The more data in the table, the longer this will take.  The other issue is global statistics.  As you add more partitions to the table for new years it will be necessary to gather global statistics to ensure they are kept up-to-date this will start to take longer as the table increases in size.  Oracle 11g does has a workaround to this - incremental global statistics - which calculates the statistics from additional information held at partition level rather than performing a full table scan.  Providing you minimise the number of times you re-calculate global statistics then this shouldn't be too much of a problem.
Geert GOracle dbaAuthor Commented:
the global statistics are calculated after midnight.
Actually you have confirmed my thoughts of the querie not using the old data.

when i look at the explain plans it first looks what partitions it needs to gather the data.
and based on this for a data for the past month it indeed doesn't look into the partitions containing older data.

so partitioning should be enough as the disk space is no problem (at the moment)
Geert GOracle dbaAuthor Commented:
thx for the intel
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.