Performance decrease while deletin a table

From: Grzegorz Gizinski <grzesiek_at_no.spam>
Date: Thu, 29 Apr 1999 11:10:12 +0200
Message-ID: <7g97ls$m28$1@euler.softax.com.pl>

Hi,
I'm using Oracle 8.0.3 running on VMS 7.1-1H2. Can anybody explain me behaviour of Oracle while processing following statments in one transaction:
1. deleting whole table (about 5000 records)

2. selecting a record from the same table 3. when select has not returned any record - inserting new,

in other case - rollback (transaction ends). 4. commit

Steps 2 and 3 repeat about 5000 times.
When processing only steps 2, 3 and possibly 4 (on empty table) everything goes OK.
But with the first step - the second takes signifficient elapse of time. Explain says that fetch operation is producing tousands of IOs. Why? Does Oracle searching the rollback segment?

TIA
Grzesiek.

Grzegorz Gizinski
Reply for spam. Real e-mail:
Grzegorz.Gizinski_at_softax.com.pl Received on Thu Apr 29 1999 - 04:10:12 CDT