| Implement Partitioning & Purging of Partitions [message #173820] |
Wed, 24 May 2006 11:30  |
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  |
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 #173894 is a reply to message #173866] |
Thu, 25 May 2006 02:53 |
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 #174347 is a reply to message #174137] |
Sun, 28 May 2006 05:47 |
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 #174809 is a reply to message #174656] |
Tue, 30 May 2006 11:47 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
