Delete operation and unable to use trigger [message #319012] |
Thu, 08 May 2008 12:58  |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
Hi All
We have a requirement in production where there has to be a delete operation on a table based on an id
and corresponding to that id there are a minimum of 1000 and a maximum of 5000 records to be deleted.
But the DBA has adviced not to use the delete statement as this operation will be performed daily and hence the deallocation of memory won't happen for a delete operation
so we now need to perform this removal of records operation and insert the corresponding records into a backup table and the user details into an user audit able.
earlier i as doing this using an after delete trigger,but unfortunately this has also been discarded by the DBA because of performance issues.
How can this be done
the table details are as below:
Table 1: ACE2_LIST_OUTPUT(table on which the delete operation will be performed)
here there are 2 non-unique indexes on ALO_LIS_ID and ALO_SME_ID
(this is the column on which the delete was earlier performed in the where condition)
ALO_LIST_ID VARCHAR2(20)
ALO_TITLE VARCHAR2(15)
ALO_FNAME VARCHAR2(15)
ALO_LASTNAME VARCHAR2 (30)
ALO_CONTACT_NAME VARCHAR2(62)
ALO_TELEPHONE VARCHAR2(16)
ALO_KDM N VARCHAR2(1)
ALO_ADDR_1 VARCHAR2(500)
ALO_ADDR_2 VARCHAR2(500)
ALO_ADDR_3 VARCHAR2(500)
ALO_ADDR_4 VARCHAR2(500)
ALO_POSTCODE VARCHAR2(50)
ALO_SALUTATION VARCHAR2(100)
ALO_VALEDICTION VARCHAR2(30)
ALO_FILE_NAME VARCHAR2(100)
ALO_BUSINESS_NAME VARCHAR2(100)
ALO_CAMPAIGN_CODE VARCHAR2(12)
ALO_CAMPAIGN_ID VARCHAR2(20)
ALO_CONK VARCHAR2(30)
ALO_CUG VARCHAR2(30)
ALO_DISTRICT_CODE VARCHAR2(50)
ALO_EMAIL_ADDRESS VARCHAR2(60)
ALO_EMAIL_CONSENT VARCHAR2(1)
ALO_EVOLVE_ROW_ID VARCHAR2(20)
ALO_JOB_FUNCTION VARCHAR2(40)
ALO_JOB_TITLE VARCHAR2(40)
ALO_LEGAL_ENTITY_CODE VARCHAR2(20)
ALO_MAIN_TEL_NO VARCHAR2(16)
ALO_SITE_NO VARCHAR2(50)
ALO_URN VARCHAR2(10)
ALO_CCAT_SUBSECTOR_CODE VARCHAR2(6)
ALO_REF_NO VARCHAR2(5)
ALO_SME_ID VARCHAR2(10)
ALO_SUPPRESS VARCHAR2(1)
ALO_CAS_SEGMENT VARCHAR2(1)
ALO_SM VARCHAR2(90)
ALO_AM VARCHAR2(90)
ALO_MAJOR_SEGMENT_CODE VARCHAR2(3)
ALO_NOTES VARCHAR2(2000)
ALO_SAC VARCHAR2(6)
ALO_FILE_NAME_2 VARCHAR2(100)
ALO_DECISION_MAKER_CONTACT VARCHAR2(100)
ALO_DFU_CAMPAIGN_CODE VARCHAR2(1)
ALO_CAMPAIGN_SUBSEGMENT_CODE VARCHAR2(2)
ALO_BT_BROADBAND VARCHAR2(1)
ALO_LAST_YEAR_REV VARCHAR2(9)
ALO_THIS_YEAR_REV VARCHAR2(9)
ALO_PRIORITY VARCHAR2(14)
ALO_CAMPAIGN_TITLE VARCHAR2(9)
ALO_KDM_FLAG VARCHAR2(100)
ALO_TELEPHONE_CONSENT VARCHAR2(1)
ALO_ADDRESS_CONSENT VARCHAR2(1)
ALO_TPS_CONSENT VARCHAR2(1)
Table 2:We had the replica of the above table as ACE2_LIST_OUTPUT_BKP
for storing the deleted records and inserting here from the trigger,but this method can't be applied now but the functionality needs to be the same.
Table 3: ACE2_USER_AUDIT
Username varchar2(30)
List_id varchar2(20) (same as the ALO_LIST_ID)
delete_date date
So how to proceed now.
Also i would like to ask that the ACE2_LIST_OUTPUT table has no partitions hence can some kind of partition be but into place and can any partition exchange method be applied inorder to remove the records based on the list id.
I am only guessing.
Please advice?
Thanks
|
|
|
|
|
|
|
|
Re: Delete operation and unable to use trigger [message #319025 is a reply to message #319021] |
Thu, 08 May 2008 14:18   |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
Just to confirm
for my knowledge when we fire a delete statement does it removes the memory space as well and it can be re used by other objects
is there any article on DML operations and memory usuage in oracle
so that i can go through.
|
|
|
|
Re: Delete operation and unable to use trigger [message #319027 is a reply to message #319026] |
Thu, 08 May 2008 14:27   |
swas_fly
Messages: 220 Registered: March 2008 Location: Bhubaneswar
|
Senior Member |
|
|
sorry for the cnfusion but i meant that when we have a table in the tablespace it is occupyng certain bytes and when we are deleting the records does that fres these bytes as well?
i hope you saw the solution that was suggested by the dba?
but is there a better way out?
and if at all this can be done
then without a trigger now how can i get the user details
into the user_audit table
|
|
|
|
|
|
Re: Delete operation and unable to use trigger [message #319040 is a reply to message #319035] |
Thu, 08 May 2008 15:34   |
 |
Michel Cadot
Messages: 68734 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
joy_division wrote on Thu, 08 May 2008 22:12 | Michel Cadot wrote on Thu, 08 May 2008 15:41 |
By the way, deleting 50000 rows even once per row is a really tiny
|
You probably meant once per hour, but OP is talking about even less rows. He said 5000, not 50000.
|
Yes, this was once per hour.
Yes, I misread, 5000 is so tiny tiny that I can't even imagine this should be reported as a problem!
Regards
Michel
|
|
|
Re: Delete operation and unable to use trigger [message #319126 is a reply to message #319023] |
Fri, 09 May 2008 02:15   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Quote: |
create table ACE2_LIST_OUTPUT_TEMP
as select * from ACE2_LIST_OUTPUT where list_id<>1234(for example)
this id will be passed on from the front end.
create table ACE2_LIST_OUTPUT_BKP
as
select * from ACE2_LIST_OUTPUT where list_id=1234
then drop table ACE2_LIST_OUTPUT_BKP
and rename ACE2_LIST_OUTPUT_TEMP to ACE2_LIST_OUTPUT
|
I cannot believe this is what your DBA has suggested you to do it to overcome the delete problem. First and foremost
create table ACE2_LIST_OUTPUT_BKP
as
select * from ACE2_LIST_OUTPUT where list_id=1234
then drop table ACE2_LIST_OUTPUT_BKP
I really cannot understand why you need the above two steps.
Secondly if you are going to drop the object and then re-create it then remember all the dependencies on this object will be marked invalid and you have to redo all your grant, indexes, synonyms etc.
Hope that helps
Regards
Raj
|
|
|
Re: Delete operation and unable to use trigger [message #319129 is a reply to message #319126] |
Fri, 09 May 2008 02:18   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I'm with Michel on this - 5000 rows is so few as to be not worth considering.
Is this Logging that your DBA is complaining about some internal, application specific piece of Auditing, or is he talking about the Redo logs?
If it's the Redo logs, then you need to point out to him that deleteing the rows will generate LESS redo than creating a new table containing all but the rows you want to get rid if
|
|
|
Re: Delete operation and unable to use trigger [message #319149 is a reply to message #319012] |
Fri, 09 May 2008 03:04   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
> then drop table ACE2_LIST_OUTPUT_BKP
I suppose it is typo and it should be ACE2_LIST_OUTPUT (to rename one new table to ACE2_LIST_OUTPUT in the last step successfully).
If you have fast disks, do not mind that access of ACE2_LIST_OUTPUT during this operation will fail, and do not mind that in the end it may be slower than simple delete, then do as he suggests (but even I do not suppose it is reasonable).
> How many rows are there in your table?
|
|
|
Re: Delete operation and unable to use trigger [message #319158 is a reply to message #319149] |
Fri, 09 May 2008 03:43   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
flyboy wrote on Fri, 09 May 2008 10:04 | > then drop table ACE2_LIST_OUTPUT_BKP
I suppose it is typo and it should be ACE2_LIST_OUTPUT (to rename one new table to ACE2_LIST_OUTPUT in the last step successfully).
If you have fast disks, do not mind that access of ACE2_LIST_OUTPUT during this operation will fail, and do not mind that in the end it may be slower than simple delete, then do as he suggests (but even I do not suppose it is reasonable).
|
The original question was | We have a requirement in production where there has to be a delete operation on a table based on an id
|
Apart from the objections flyboy gave, it looks like you totally misunderstood your dba. The series of actions he gave you deletes everything BUT the rows for the given id.
|
|
|
|
Re: Delete operation and unable to use trigger [message #319361 is a reply to message #319012] |
Sat, 10 May 2008 01:52   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
> I am sorry about the then drop table ACE2_LIST_OUTPUT_BKP
> this step,may be this is not required.
But it is required to drop ACE2_LIST_OUTPUT (the original table), as you would not be able to rename one of the new tables to its name.
> The table contains around 2.7 million records.
Not very big.
> ACE_LIST_OUTPUT table has only two nonunique indexes.
> ONE ON ALO_LIST_ID AND ALO_SME_ID.
As the new tables are created without them, the indexes have to created extra...
> but can anything be done with partitioning here,any kind of partition exchange(tentitive)
> there are only 2345 distinct list ids.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/partconc.htm#sthref2570
Partitioning on ALO_LIST_ID does not seem reasonable here - 2000+ partitions will reliably kill all queries not based on this column. And other type/column would not help in this "delete" process. You can put some (say about 500) list ids into one partition, but then you would have to do the same steps on the involved partition.
> The table gets populated from various values passed from the application.
So your users do not mind that they will not be able to work with ACE_LIST_OUTPUT table during this "delete" operation.
> What about the audit table now?
> Where we need to store the user details(mentioned in first post of mine),Without the after trigger how can this be possible?
Create a procedure deleting the rows and in the end insert into audit table. If you choose this way of "deleting", you have no other choice anyway.
Just to remind you: all CREATE TABLE scripts are DDL, so they do COMMIT (end current transaction, do their work and start another). So it is NOT POSSIBLE to rollback it in case of any error. It may happen that ACE2_LIST_OUTPUT will not be accessible after its failure. The script shall at least cope with this possibility and correctly recover (if possible).
However I think your DBA shall rather cure his "waste memory" and "logging" phobia.
Enjoy.
|
|
|
Re: Delete operation and unable to use trigger [message #319640 is a reply to message #319345] |
Mon, 12 May 2008 07:52  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote: | The table contains around 2.7 million records.
The delete operation will happen for around 5000 records but this step will be carried out 2-3 times a day and gets repeated daily.
I am not that familiar with partitioning,but can anything be done with partitioning here,any kind of partition exchange(tentitive)
|
You really are making a mountain out of a molehill here.
This table is small, and you are deleting a trivial number of records from it.
Why not just issue a DELETE statement. The load that this will put on your database will be trivial.
There can't be any foreign key dependencies to the table, because otherwise your Truncate wouldn't work.
|
|
|