Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Add or Remove Parition from Partitioned table

Posted on 2009-05-18
14
Medium Priority
?
620 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 672 total points
ID: 24411205
0
 
LVL 38

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 38

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 38

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 664 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 38

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 664 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 38

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 38

Author Closing Comment

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

705 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