Solved

Add or Remove Parition from Partitioned table

Posted on 2009-05-18
14
610 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
  • 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 47

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
 
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 47

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 47

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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

744 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

12 Experts available now in Live!

Get 1:1 Help Now