Home » SQL & PL/SQL » SQL & PL/SQL » Delete operation and unable to use trigger (oracle 10g,win xp)
Delete operation and unable to use trigger [message #319012] Thu, 08 May 2008 12:58 Go to next message
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 #319013 is a reply to message #319012] Thu, 08 May 2008 13:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I would have two suggestions:

1) Can you provide more details on what your DBA means when he/she says "the deallocation of memory won't happen for a delete operation".

2) I would suggest you tell your DBA to give you an answer. This person operates like way too many DBAs I have worked with these days. They are keen to tell you what you can't do and why, but offer no help in telling you what you can do to get what you want. Kick them in the butt and tell them to stop wasting your time and make some useful suggestions for once. OK, but be nice about it.

Kevin
Re: Delete operation and unable to use trigger [message #319018 is a reply to message #319013] Thu, 08 May 2008 13:49 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
I had an discussion on this already with the DBA,he had told me that

Delete table is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow.

Hence i should avoid this
Re: Delete operation and unable to use trigger [message #319019 is a reply to message #319012] Thu, 08 May 2008 13:59 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Three things:

1) why avoid logging? It is there for a reason. Does your DBA know the value of logging? If so, please ask them to explain the value of logging to you and then explain why logging is so bad that Oracle was stupid enough to employ it in their database.

2) if this DBA is so keen to avoid logging then what alternative do they suggest? This goes back to my original point: Does your DBA have anything to offer that is constructive to getting your solution in place? If not, get yourself another DBA.

3) if your DBA should happen to make a suggestion as to how you should write code that removes rows without logging, what do they then suggest should be your recovery plan when your application fails, or someone makes a mistake? How will you get your data back? I admit there are alternatives to deletes. For example, your system may be special (yes every DBA thinks their systems are special, but once in a great while they are in fact correct and their system is special). If so then there are things like dropping or tuncating partitions after making a copy which can reduce logging. But I would suggest that in 9 or 10 cases where I have seen this done it was way more trouble that it was ever worth. The performance time being saved was very little compared to small deletes like you have described, and the headaches of recovery and reporting were way big. I can't believe your DBA is worried about the logging associated with 5000 rows / day. That is nothing.

Kevin
Re: Delete operation and unable to use trigger [message #319021 is a reply to message #319018] Thu, 08 May 2008 14:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Hence i should avoid this

Why?
Insert is a logged operation (most of the time)
Update is a logged operation
Delete is a logged operation
Logging is natural to dbms, it is the way it guarantees the data.

Logging is not a problem unless your DBA does not know how to handle it.

Space freed by deletes is reused later so it does not matter it does not appear to be released.

Regards
Michel

Re: Delete operation and unable to use trigger [message #319023 is a reply to message #319019] Thu, 08 May 2008 14:14 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
I really appreciate your comments but we as developers can't ask for replacing a DBA Shocked

and now about deletion of 5000 records,the thing is this deletion process will happen 2-3 times in a day at a minimum and will go on every day,so you can imagine how much rows can be deleted and again this logging thing comes on,although i am not sure on this

he suggested me something like this

to create a temp table like

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

But surely there might be better way than this, i need help
Re: Delete operation and unable to use trigger [message #319025 is a reply to message #319021] Thu, 08 May 2008 14:18 Go to previous messageGo to next message
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 #319026 is a reply to message #319025] Thu, 08 May 2008 14:20 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I have not clue what you means when you say "memory space" in this context. Please provide a more complete description here.

Kevin
Re: Delete operation and unable to use trigger [message #319027 is a reply to message #319026] Thu, 08 May 2008 14:27 Go to previous messageGo to next message
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 #319028 is a reply to message #319012] Thu, 08 May 2008 14:37 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
again, your DBA is not being forth comming with the appropriate level of help. If they were you would not be here asking us for an answer. Ah, but let me stop berrating them. Let me first confirm that we have the same basic understanding.

Your problem is you need to "remove" rows from two tables such that these rows are all tied to the same key value.

There are at the moment four ways to remove rows from a table that come to mind.

1) DELETE
2) TRUNCATE TABLE (PARTITION)
3) DROP TABLE (PARTITION)
4) UPDATE ... SET DELETE_FLAG = 'Y' (A LOGICAL DELETE, DOES NOT PHYSICALY REMOVE ROWS)


Which of these four methods does your DBA want you to use to remove these rows?

Kevin
Re: Delete operation and unable to use trigger [message #319029 is a reply to message #319025] Thu, 08 May 2008 14:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When you delete rows, the space freed is available for other rows in the same table.
I think if you delete 10000 ot 50000 rows 3 times per day then you insert new from time to time.
How many rows are there in your table?

By the way, deleting 50000 rows even once per row is a really tiny job that should not overload your database. I do more job on my laptop.

Regards
Michel
Re: Delete operation and unable to use trigger [message #319035 is a reply to message #319029] Thu, 08 May 2008 15:12 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
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.
Re: Delete operation and unable to use trigger [message #319040 is a reply to message #319035] Thu, 08 May 2008 15:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
Frank
Messages: 7880
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.
icon13.gif  Re: Delete operation and unable to use trigger [message #319345 is a reply to message #319012] Fri, 09 May 2008 23:21 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Thanks for all the responses.

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

When i wrote this i meant the followuing:

we need to remove the records from the table where the list id is say for ex 1234,hence i created the temp table wih all the records as were present in ACE2_LIST_OUTPUT except records corresponding to this list id.

I am sorry about the then drop table ACE2_LIST_OUTPUT_BKP
this step,may be this is not required.

The DBA suggested for some steps using create temp tables rather than delete,he did not suggest the exact thing so i was figuring it out.


But then i need to rename the TEMP table again to ACE2_LIST_OUTPUT so as to have all other records except that list id.


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)

Some details about the table :

ACE_LIST_OUTPUT table has only two nonunique indexes.
ONE ON ALO_LIST_ID AND ALO_SME_ID.

The table gets populated from various values passed from the application.
out of those 2.7 million List ids there are only 2345 distinct list ids.
This has no constraints
I hope i can explain it in more details but this is what i can provide you with,Hope i am clear.

Another thing i was asking,but did not get any solution

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?

Lastly any link or documents where i can find study stuff on partitioning and if at all(as suggested by DBA) the delete operation will have adverse effect as per him Shocked


Thanks





Re: Delete operation and unable to use trigger [message #319361 is a reply to message #319012] Sat, 10 May 2008 01:52 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous message
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.

Previous Topic: Getting all dates in a month
Next Topic: Grouping by Saturday and Sunday
Goto Forum:
  


Current Time: Fri Dec 02 20:22:03 CST 2016

Total time taken to generate the page: 0.09478 seconds