Solved

Add or Remove Parition from Partitioned table

Posted on 2009-05-18
14
616 Views
Last Modified: 2013-12-18
The problem:
A table containing some old data 2000-2009 now contains 50mill rows

I would partition this table into year partitions.

Afterwards:
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 ?
0
Comment
Question by:Geert Gruwez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
14 Comments
 
LVL 6

Accepted Solution

by:
tangchunfeng earned 168 total points
ID: 24411205
0
 
LVL 37

Author Comment

by:Geert Gruwez
ID: 24411231
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.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24411433
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:

ALTER TABLE sales DROP PARTITION jan98;

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:tangchunfeng
ID: 24411541
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
0
 
LVL 37

Author Comment

by:Geert Gruwez
ID: 24411596
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

ALTER TABLE X DETACH TABLESPACE PARTITION2006;

Then when i want to find data for 2006, i would first do this:
ALTER TABLE X ATTACH TABLESPACE PARTITION2006;
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 24413673
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.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24414163
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.
0
 
LVL 37

Author Comment

by:Geert Gruwez
ID: 24416147
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 ?
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 166 total points
ID: 24418950
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.
0
 
LVL 37

Author Comment

by:Geert Gruwez
ID: 24419033
the indexes are defined as local
and they would access the historic data 1 or 2 maybe 3 times a year.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24419055
Calculate the number of rows affected and indexes involved.
The more indexes the more time delete works.
0
 
LVL 16

Assisted Solution

by:Milleniumaire
Milleniumaire earned 166 total points
ID: 24419498
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.
0
 
LVL 37

Author Comment

by:Geert Gruwez
ID: 24419515
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)
0
 
LVL 37

Author Closing Comment

by:Geert Gruwez
ID: 31582550
thx for the intel
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

732 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