So we have a table that various people in our .org use, its not a freqeuntly used table, but still multiple people can use it at the same time.
When I insert records into this Table, that takes about 0.03 seconds, but to delete the same said records takes 9.00 minutes, sometimes 40 minutes.
Someone said that I have to put some Lock on it, so I am looking for the exact SQL, please don't point me to some paper/URL, those are worthless, I have read many
and am still confused on the actual SQL to use.
So far I have this, please correct and help me with the right statement.
BT; LOCKING ROW WRITE delete from Table where id='V' and test='YYY'; ET;
I am glad that this Forum formatting has been finally fixed, welcome to the Year 2017!!! :-)
A single row delete should not take more time than an insert.
Could you provide the table definition, the delete statement and the explain for the delete statement? Perhaps there is an adjustment to the delete statement that would help with the performance.
If the current delete statement is indeed a single PI delete (as shown in the explain) then the optimizer will automatically place a ROW WRITE lock and the clause being added below would be unnecessary. If it is not a single PI delete then the optimizer will place a table level lock regardless of whether a row lock is requested.
It is implied the multiple records are being deleted. How are the multiple statements being submitted? Are they beinng submitted inside a single transaction or as separate statements (if separate statements then please also state the transaction mode - ANSI/Teradata - being used in the session).