Hi Fred,
Even though the delete affected no rows, it does begin a transaction. That
transaction takes up a slot in the SGA transaction table (X$KTCXB) but does not
yet use a slot in one of the rollback segment header block transaction tables.
V$TRANSACTION will not show you this transaction because the KTCXBFLG value is
1, but V$RESOURCE_LIMIT will show you that the slot is in use.
If you are not concerned about the retention of a slot in the SGA transaction
table, then the only impact of not terminating the transaction explicitly with a
COMMIT or ROLLBACK is that the snapshot SCN for consistent reads for subsequent
statements will reflect the point in time at which the delete operation was
attempted. If your next statement needs a new snapshot SCN for consistent reads
then the transaction should be explicitly terminated. There is no performance
difference between using COMMIT or ROLLBACK in this case. However, if your next
statement does not need a new snapshot SCN then you can get a very minor
performance saving by not terminating the transaction. All you are saving is the
cost of the call itself, and the cost of establishing a new transaction next
time. There is no impact of LGWR from null commits or rollbacks.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-----Original Message-----
Sent: Thursday, 15 February 2001 4:37
To: Multiple recipients of list ORACLE-L
I'm using transactions, with a delete followed by an insert.
If the delete affects 0 rows, there is nothing to do.
Do I need to rollback the transaction to abandon it, or is everything
the way it should after a delete that affected 0 rows?
Thanks!
-- Sincerely, Fred
Frederik Lindberg, CTO, CheetahMail
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steve Adams
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
