Home » RDBMS Server » Performance Tuning » Implement Partitioning & Purging of Partitions
Implement Partitioning & Purging of Partitions [message #173820] Wed, 24 May 2006 11:30 Go to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Hello All

Oracel Version 9i Rel 2.

We want to implement partition on 5 of our huge tables having 20-40 million rows.
The business requirement is to have 10 yrs of data,
i have this create table statement for implementing Partitioning,
I have also decided to have a local partitioned index on date column,

CREATE TABLE clreared_transaction_partition_table
PARTITION BY RANGE (trans_dt)
(PARTITION P1
 VALUES LESS THAN (to_date('20060601','YYYYMMDD'))
 table space ts1,
PARTItION P2
VALUES LESS THAN (to_date('20060501','YYYYMMDD')),
PARTItION P3
VALUES LESS THAN (to_date('20060401','YYYYMMDD')),
PARTItION P3
VALUES LESS THAN (to_date('20060301','YYYYMMDD')),
PARTItION P4
VALUES LESS THAN (to_date('20060201','YYYYMMDD')),
PARTItION P5
VALUES LESS THAN (to_date('20060101','YYYYMMDD')),
PARTItION P6
VALUES LESS THAN (to_date('200501201','YYYYMMDD')),
PARTItION P7
VALUES LESS THAN (to_date('20051101','YYYYMMDD')),
PARTItION P8
VALUES LESS THAN (to_date('20051001','YYYYMMDD')),
....
---

PARTItION P120
VALUES LESS THAN (to_date('19960501','YYYYMMDD'))
table space ts10,

)
AS
SELECT * FROM cleared_transaction;

create unique index on clreared_transaction_partition_table(trn_id,trans_dt);

Since the requirement is to hold 10 yrs of data at a time,
once July 2006 comes the last old partition should be dropped and
the july 2006 will be the first partition, How can i make so that it should clear of every month end and year end so always to have 120 partitions for 10 years data.
Do i have to do it every month end dropping and creating manually or can i make it a script Any steps and help please
the same with index also since it is a local partitioned index.

Any suggestion here:- i am keeping 1 year data in each table space i.e 10 table spaces, each table space will have 12 monthly logical partitions.


Thanks

[Updated on: Wed, 24 May 2006 11:49]

Report message to a moderator

Re: Implement Partitioning & Purging of Partitions [message #173860 is a reply to message #173820] Wed, 24 May 2006 22:26 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You can get the detail you need from USER_TAB_PARTITIONS, USER_PART_TABLES, USER_IND_PARTITIONS, and USER_PART_INDEXES.

If you have some kind of naming standard for the partitions and key columns, then it will help, otherwise you can work it out from first principles using USER_PART_KEY_COLUMNS.

You will also need predictable tablespace names.

Then you just go ahead and write your own PL/SQL routines, and schedule them with DBMS_JOB or DBMS_SCHEDULER.

Oracle does not provide a pre-canned partition-archiving tool, although I wouldn't be surprise if someone has already written one. Try Googling.

Ross Leishman
Re: Implement Partitioning & Purging of Partitions [message #173866 is a reply to message #173820] Wed, 24 May 2006 23:27 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi,
I have a similar problem. As I am new into this project and don’t have much of DBA experience so just want to clarify things. Our system has a partitioned table, in which every day a partition is created. The partitioned was created until 3rd Mar’2006 and after that no new partition is created and the data after that got into the default partition. Each partition stores data around 3,50,000 records (approx), so the last partition is having around 24 million records due to which the purpose of partitioned table is lost. Now I want to know if I create new partitions specifying the date range for previous months will the data be automatically moved into those partition or what can be other way of doing it. Also is there any limit on number of partitions created on a table. Currently our table is having 760 partitions.

Thanks in advance.

Regards
Himanshu
Re: Implement Partitioning & Purging of Partitions [message #173894 is a reply to message #173866] Thu, 25 May 2006 02:53 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You dont add partitions in this case, you SPLIT the existing one. Lookup the ALTER TABLE command in the Oracle doco.

I know of no limit to the number of partitions, but if you have a daily-partitioned table, you really should be seriously considering archiving partitions rather than letting them accumulate indefinitely.
With more partitions, indexed WHERE clauses that do not include the partition key will get slower and slower (if the index is LOCALLY partitioned), because it will have to repeat the scan in 760+ separate index partitions. ie. I/O for such queries could (at worst) be 760 times slower than a GLOBAL non-partitioned index.

Ross Leishman
Re: Implement Partitioning & Purging of Partitions [message #173904 is a reply to message #173820] Thu, 25 May 2006 03:44 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi,
Thanks for the response. Yeah I checked the Oracle doc and came to know that we have to split the partition. Yes the performance is really bad on this table, although the indexes are not created locally, they are global.

Thanks for the update. Will have a word with our DBA and get it fixed.

Regards
Himanshu
Re: Implement Partitioning & Purging of Partitions [message #174073 is a reply to message #173904] Thu, 25 May 2006 15:08 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member

Well i am also looking for similar script for purging and adding a partition on daily or monthly or Yearly basis, which i will submitting as a DBMS_JOB, If any body is having such script please share .


Thanks

[Updated on: Thu, 25 May 2006 15:28]

Report message to a moderator

Re: Implement Partitioning & Purging of Partitions [message #174137 is a reply to message #173820] Fri, 26 May 2006 03:24 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi,
Is there any better way other than splitting as we need to create around 100 new partition on an existing table for the data which is already inserted into default partition. As this is production database, so can we create a new table with required 100 partitions and export and import the data into new table and then move or swap those partition into the existing table??

Please let me know how can I achieve this?

Thanks in advance.

Regards
Himanshu
Re: Implement Partitioning & Purging of Partitions [message #174347 is a reply to message #174137] Sun, 28 May 2006 05:47 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
- Drop indexes and constraints
- Create an empty table that looks like your partitioned table.
- ALTER TABLE EXCHANGE PARTITION the default partition with the empty table. Now you have your data out of the table.
- Drop the DEFAULT partition.
- Create all of the additional partitions you need.
- ALTER TABLE part_table NOLOGGING
- INSERT /*+append*/ INTO part_table SELECT * from temp_table
- ALTER TABLE part_table LOGGING
- Create indexes and constraints.

You could also do it without dropping the indexes and constraints by creating many temp tables and using them to EXCHANGE PARTITION back into the partitioned table.

Ross Leishman
Re: Implement Partitioning & Purging of Partitions [message #174464 is a reply to message #173820] Mon, 29 May 2006 05:21 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Thanks Ross for the response. Actually this is Production environment and we want to achieve the results with minimum downtime. The method you have suggested will have a downtime where the data won't be available once we do the exchange.
I just want to know if we go for splitting, then will the table be unavailable during the splitting of partition is going on?

Regards
Himanshu
Re: Implement Partitioning & Purging of Partitions [message #174656 is a reply to message #174464] Tue, 30 May 2006 03:22 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try it in a Test environment and find out.
Re: Implement Partitioning & Purging of Partitions [message #174809 is a reply to message #174656] Tue, 30 May 2006 11:47 Go to previous messageGo to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Hello

i am not able to complete this task of creating a partition table and always keeping 10 yrs of data with monthly partition i.e 120 partitions at a time.

This is what i was trying but incomplete,

CREATE TABLE part_tab 
PARTITION BY RANGE (col1)
Partition P1 VALUES LESS THAN (to_date('20060501','YYYYMMDD') ,
         Partition P2 VALUES LESS THAN (to_date('20060401','YYYYMMDD') ,
                                ............
                                .........
                               Partition P120 VALUES LESS THAN (to_date('20050501','YYYYMMDD')--- Oldest partition.



Once new month comes the oldest partition P120 should drop along with data and a new partition with current month should come in, same at year end also.
For this a DBMS_JOB should be there i am thinking, but not able to complete this, how to write a procedure for this.
If any has some thing to say on this please share it.
like how the oldest partition should go and new one should be there ,and the name of new partition should be like what the same as the dropped one or how ?


Thanks

[Updated on: Tue, 30 May 2006 13:52]

Report message to a moderator

Re: Implement Partitioning & Purging of Partitions [message #175197 is a reply to message #174809] Thu, 01 June 2006 04:31 Go to previous message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
@Feroze


I am not sure, how are you planning to manage this:

Quote:

how the oldest partition should go and new one should be there ,and the name of new partition should be like what the same as the dropped one or how ?



What I can think of doing here is to name my partitions logically.. something like P<YYYY><MM>.. where YYYY is year & MM is month with format 99 i.e. 01 for Jan, 02 for Feb ... 12 for DEC.

Now using function to figure out my new partition name & name for partition to be deleted (which is the one that is 10 years old)....
I can execute add partition & drop partition statements.

The following article may help on this:
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10739/partiti.htm#i1006508

--Girish
Previous Topic: Urgent Help Needed:Oracle 10g
Next Topic: Application Turns Slow
Goto Forum:
  


Current Time: Thu Mar 28 15:26:16 CDT 2024