Home » RDBMS Server » Performance Tuning » Decide on Partitioning Column/Purge Approach (Oracle 11g Release 2)
Decide on Partitioning Column/Purge Approach [message #627638] Thu, 13 November 2014 10:11 Go to next message
azeem87
Messages: 116
Registered: September 2005
Location: dallas
Senior Member
Hi,
we have an existing databae and model, kind of OLTP Payments transactionl System,
15 tables are classified as transaction .. and that grows/will grow in comings days, with few tables currently at the rate 35 to 50K (maximum ) rows per day.

A Need for Data Archival/Purge arise, business criteria is to retain 65 days of data on transactional tables and rest can be purged permanently,
the where condition to purge from Parent Table would be
Pymt_txn.status in ('C','R') and 
TRUNC (pymt_txn.value_date)  < TRUNC (SYSDATE - 65)


we have to delete from all the 14 child tables associated with the pymt_Txn_id,

2 approach in mind for this.

1. Create a Table permanently on the system to store ID's everytime we are ready tp start purge process,
insert into this table all the txn_id to be deleted everytime we run the process.
delete from from all the 15 tables where txn_id in this new table.
Clean the New table.

2nd approach.. Parition the existing tables based on value_date,
but child tables doesn't have value_date
Any idea what should be the partitioning column to make deletes also easy by dropping the partitions from all the tables,
Adding value_date in all 14 columns is not possible as it's a major design/code change.

Please suggest on Partitioning approach and how to relate with txn_id/value_date from Parent table,
Or my First delete approach is good?

I have a sample table/column description below just few columns from each table.
TABLE NAME		PYMT_TXN
		
Pymt_txn_id	number 	Primary Key
user_id	number 	
value_date	date	
status	varchar2(1)	P'  Process , 'C' Closed,'R' Rejected
last_modified_date	date	
		
		
		
TABLE NAME		TXN_DTL
		
DTL_TXN_ID	Number	Primary Key
pymt_txn_id	number	Foreign Key from PYMT_TXN table
last_modified_date	date	
benefeciary_1	varchar2(35)	
benefeciary_2	varchar2(35)	
.......		
...........		
		
		
TABLE NAME		PYMT_APPROV
		
pymy_Apprv_id	number	Primary Key
pymt_txn_id	number	Foreign Key from PYMT_TXN table
approval_1	varchar2(35)	
approval_2	varchar2(35)	
approval_date	date	
		
		
		
TABLE NAME		Signature_Details
		
signature_detail_id	number 	Primary Key
signature_event_id	number 	foreignKey from Signature_event Table
pymt_txn_ID	NUMBER	Foreign Key from PYMT_TXN table
dtl_text	VARCHAR2 (64 Byte)	
dtl_digest_clob_data	CLOB	

Re: Decide on Partitioning Column/Purge Approach [message #627640 is a reply to message #627638] Thu, 13 November 2014 10:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Adding value_date in all 14 columns is not possible as it's a major design/code change.
Adding VALUE_DATE is possible, but you have arbitrarily decreed it to be not an option.
I disagree adding VALUE_DATE would result in major code change.
Since it VALUE_DATE does not exist now in some tables, adding VALUE_DATE does NOT require any code to change; since it is not used now.
Re: Decide on Partitioning Column/Purge Approach [message #627643 is a reply to message #627638] Thu, 13 November 2014 10:22 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Parition the existing tables based on value_date,
but child tables doesn't have value_date
So use reference partitioning. But your partitioning option can't work, because your selection criteria is on Pymt_txn.status as well as pymt_txn.value_date.

I would have thought a simple delete with cascade on the foreign key would do.
Re: Decide on Partitioning Column/Purge Approach [message #627646 is a reply to message #627640] Thu, 13 November 2014 10:44 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Thu, 13 November 2014 16:16

Since it VALUE_DATE does not exist now in some tables, adding VALUE_DATE does NOT require any code to change; since it is not used now.

You think it's going to magically inherit the value of it's parent row without a code change?

That said I agree with John - just go with a simple delete with cascade - If you've got decent hardware it'll be quick enough.
By my maths your largest tables will have 3.25 million rows - that's not a lot these days.
Re: Decide on Partitioning Column/Purge Approach [message #627660 is a reply to message #627646] Thu, 13 November 2014 14:54 Go to previous messageGo to next message
azeem87
Messages: 116
Registered: September 2005
Location: dallas
Senior Member
Thanks for inputs,
so as i mentioned my first approach with a delete statement, will try with that,
and # of rows are in fact more than 32 mill, in few tables they are upto 80 to 100 mil.. and 2 tables in the tlist of 15 has Clob and Blob.

will let you know how it goes with my first approach,


Thanks Again.
Re: Decide on Partitioning Column/Purge Approach [message #627661 is a reply to message #627660] Thu, 13 November 2014 15:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
issue COMMIT after every DELETE sttement
Re: Decide on Partitioning Column/Purge Approach [message #627684 is a reply to message #627661] Fri, 14 November 2014 06:52 Go to previous messageGo to next message
d_seng
Messages: 78
Registered: November 2011
Location: UK
Member
Here's something that I've tried in the past, where referential integrity constraints were not well defined: There is a table A whose child table is B, child table B has two further children C & D. The syntax was along the lines of:
DELETE FROM A WHERE <CRITERIA> RETURNING COL_A INTO TEMP_A;
DELETE FROM B WHERE COL_B IN (SELECT COL_A FROM TEMP_A) RETURNING COL_B INTO TEMP_B;
DELETE FROM C WHERE COL_C IN (SELECT COL_B FROM TEMP_B) RETURNING COL_C INTO TEMP_C;
DELETE FROM D WHERE COL_D IN (SELECT COL_B FROM TEMP_B) RETURNING COL_D INTO TEMP_D;
Hope you get the idea.
Re: Decide on Partitioning Column/Purge Approach [message #627688 is a reply to message #627684] Fri, 14 November 2014 07:43 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
If referential constraints aren't present I'd skip the array and just write a series of deletes that start with the lowest level child:
DELETE FROM D WHERE COL_D IN (SELECT COL_B FROM B THE COL_A IN (SELECT COL_A FROM A WHERE <CRITERIA>));
DELETE FROM C WHERE COL_C IN (SELECT COL_B FROM B THE COL_A IN (SELECT COL_A FROM A WHERE <CRITERIA>));
DELETE FROM B WHERE COL_B IN (SELECT COL_A FROM A WHERE <CRITERIA>);
DELETE FROM A WHERE <CRITERIA>;


It'd be a useful exercise to compare the two and see which is faster.
Re: Decide on Partitioning Column/Purge Approach [message #627689 is a reply to message #627688] Fri, 14 November 2014 08:22 Go to previous messageGo to next message
d_seng
Messages: 78
Registered: November 2011
Location: UK
Member
I had tried it, but deletes on the tables that went 10-12 levels deep ran like a dog with no legs, other than the mammoth SQL statement that made people dizzy!
Re: Decide on Partitioning Column/Purge Approach [message #627690 is a reply to message #627689] Fri, 14 November 2014 08:33 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
well the number of levels is bound to make a difference. I've got a routine like that, but it's only got 4 levels (and level 4 has tiny amounts of data) and it runs just fine. I can easily believe 12 is going to be trickier.
Previous Topic: Logging operation takes longer but no wait events
Next Topic: Index vs Full table scan on sysdate
Goto Forum:
  


Current Time: Thu Mar 28 09:40:56 CDT 2024