This content has been marked as final. Show 20 replies
Moreover, I couldn't use truncate as I need 28 million data's to be in table always and I am just deleting records based on where condition.
Would you mind explaining that?
Why would you need to just arbitrarly keep a minimum of 28 million rows in a table? Why don't you tell us exactly what you are trying to accomplish?
The only way you can maintain the same number of rows in a table is to either not add or delete any or to add and delete exactly the same number. So something now sounds a little unusual about some of your statements.
What does this data represent? Why do you need to delete data every day just because you add some data every day?
Why is it ok to delete just 1000 rows at a time from the child tables? That means that ANY query of that child data MUST BE incorrect since it will not include all of the data.
Sorry to not made it clear already.
Those are backend tables of ATG based retail's web application. All the online customer profile informations(3 million rows already) and information about customer who just visiting the website(approx 500k new rows daily) being stored in the master and child tables. Though refrential integrity constraints are enabled between those tables, "cascade delete option" are disabled from database level based on the specific requirement from front end application. (as if any data's get deleted from database end using cascade delete option then still application would remain uninformed about data's deleted and it would cause messup in application). So all should be manual delete if it happens from db end.
Now the requirement is to delete 45 days old unregistered user profiles (who just visting the website) on daily basis which is 500k records daily, approx.
Hence table would always maintains, 3million (registered user profiles) + 500k*45=23million unregistered profiles (45 days count of unregistered profiles) = 26million (total) will be available in table always. 500k should be purged on daily basis for the unregistered profiles which is 45 days old.
This condition will be first matched with master table as only this table has date column and take out the another column value (ID) from matched profiles.
I = select ID from master_table where (unregistered profile condition) and registered_date <= (sysdate) -45;
Further compare it with child tables ID column and delete the matched columns of child tables
Delete from child_table1 where child_table.ID = I.ID
Delete from child_table4 where child_table.ID = I.ID
Atlast delete the matched columns from master table.
Delet from master table where master_table.id= I.ID
But it should not delete registered profiles of 45 days old,
should not use huge undo
should not generate more redo and
should complete in less time as well.
Morever, I couldn't go for partition option as each table size is not even crossed 2GB. I have been breaking my head to prepare the procedure for this work, for couple of days. Any help on this would be really appreciated.
Hope you got the requirement I am having. Many Thanks for your help,
Edited by: 975654 on 12-Dec-2012 10:44
Edited by: ascheffer on Dec 12, 2012 9:26 PM
declare cursor t_c is select id from master_table where 1 = 1; -- your condition type tp_ids is table of master_table.id%type; t_ids tp_ids; begin open t_c; loop fetch t_c bulk collect into t_ids limit 22; -- change according to resources -- dbms_output.put_line( t_ids.count ); exit when t_ids.count = 0; -- forall i in t_ids.first .. t_ids.last delete from child_table where fk_id = t_ids( i ); -- forall i in t_ids.first .. t_ids.last delete from master_table where id = t_ids( i ); -- exit when t_c%notfound; end loop; close t_c; commit; end;
Just briefly reviewed the long panel discussion by Tom (Regular Delete vs. Bulk Delete).
One concern here is how many redo entries are generated from FORALL. Although it switches from PL/SQL engine to SQL engine only once for all the deletes, its impact on redo log growth still needs on check. Also, how many child records are deleted for each parent ID (inefficient for low cardinality)? For such reasons, regular delete would be more flexible to keep both redo and undo usage under control. But wait and see how test shows.
Below is by regular delete. Create a view first to ease the coding
create or replace view delete_id_list as select ID from master_table where (unregistered profile condition) and registered_date <= (sysdate) -45 ; --run pl/sql block now for delete declare i NUMBER :=1000 ; begin --delete child1 begin loop delete child_table1 where ID in (select id from delete_id_list) and rownum<i ; exit when sql%rowcount=0; commit ; end loop ; end ; --delete child2 --delete child3 --delete child4 loop delete delete_id_list where rownum<i ; exit when sql%rowcount=0; commit ; end loop ; exception when others then null; rollback ; end ; /
Morever, I couldn't go for partition option as each table size is not even crossed 2GB
Now there's another cryptic statement that appears to be based on the 'if table is greater than 2GB consider partitioning' myth. ;)
There is no such size condition or requirement for partitioning. You should partition based on the benefits you will derive. Those benefits are usually performance, partition maintenance or both.
For you use case partitioning is definitely indicated. You never did provide your 4 digit Oracle version (or maybe I missed it) but the 11g versions support interval partitioning where you partition by day and let Oracle create the partitions automatically.
When you want to get rid of old data you simply DROP the partition that it is in and that is the end of it. No muss, no fuss, no messing with redo/undo.
You should test using an interval partitioned table.
Here is simple code
That one partition definition ensures that all of the 'old' data has somewhere to go; use any date you want. For any new data (MY_DATE later than that partition boundary) Oracle will automatically create a partition for that one day.
DROP TABLE SCOTT.PARTITION_DAILY_TEST CASCADE CONSTRAINTS; CREATE TABLE SCOTT.PARTITION_DAILY_TEST ( ID NUMBER(10), MY_DATE DATE ) PARTITION BY RANGE (MY_DATE) INTERVAL( NUMTODSINTERVAL(1,'DAY')) ( PARTITION P_EXISTING_20120401 VALUES LESS THAN (TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) );
So every day you will get one new partition created automatically and eveyr day you can drop the oldest partition if you want. Or drop a week at a time.
Poof - it's gone. Since the drop is DDL you have no undo/redo. And partitions do NOT go into the recyclebin. Just drop the child table partitions first and then the parent.
ALTER TABLE PARTITION_DAILY_TEST DROP PARTITION FOR (TO_DATE('2012-04-05 00:00:00', 'SYYY-MM-DD HH24:MI:SS'));
You should always include the version... I did not see it, but could have overlooked it.
If you are using 11gR2, look at DBMS_PARALLEL_EXECUTE. It handles very large DML very easy.
I have used this in situations where I must update or delete very large volumes of data with not-so-easy selectivity. Search the docs for the procedure. TEST, TEST and TEST some more.
This guarantees, you will never see an ORA-01555
Edited by: onedbguru on Dec 12, 2012 3:45 PM