RE: Runtime for deleteing lots of records

I've also heard and observed that extreme database operations can cause
indexes to be automatically rebuilt.  A few years back we were running a
software package conversion that required tables to be dumped to a temporary
area then copied back using CPYF MBROPT(*REPLACE).  We accidentally ran it
without taking the option to drop all the logical files.  We saw that every
hour or so the i/o would freeze and the LF's would take a long hit.

If this is still true in the V5 world and if it applies to deletes you may
have rebuilt your logical files a few extra time during the process.

btw, this is one of the things that drives me crazy about Oracle.  If you're
performing a lot of deletes (like, for instance, if you happen to be running
ANY transaction-based BUSINESS on your database) the indexes will grow
inefficient and require frequent rebuilds.

-Jim

-----Original Message-----
From: Vern Hamberg [mailto:vhamberg@centerfieldtechnology.com]
Sent: Monday, February 10, 2003 8:48 AM
To: Midrange Systems Technical Discussion
Subject: RE: Runtime for deleteing lots of records


Even 4 or 5 logicals will be bad - every record deleted means processing 
all the indexes for each record deleted. The Database Programming manual 
says that the entry for a deleted record is removed from the index. If this 
is done in a simple manner, it may mean just changing a couple pointers. If 
the entry is compressed out, there's more work involved. Nonetheless, this 
is on a record-by-record basis. You're way ahead by turning off or delaying 
access path maintenance.

The other points, esp. about copying the records you want to keep and 
clearing the file, are right on, IMO, esp. when the number of deleted 
records will be most of the file. You can CPYF on a range, and an existing 
logical will help this, too. You deleted c.72% of the records. Copying the 
other 28% would mean, at worst, about 40% of the I/O, and could be better, 
if blocking can be used - deletes are not blocked.

In my experience, SQL is the worst tool for deleting records, esp. a lot of 
them, relatively speaking. It's awfully easy to program but often performs 
very badly. The usual trade-off is: Convenient for the programmer, bad for 
the user (or performance).

As far as using the index goes, the optimizer may still select a table 
scan, if it determines that lots of records will be returned. Default 
estimates are that greater-than and less-than each return 33% of the 
records. This is refined in various ways, and I don't know the effect of 
the AND. A between (which this is equivalent to) assumes 25% of records 
returned. But key-range estimates may broaden this, as it should in this 
case. If it finds the number returned to be high, it'll probaby go with a 
table scan, as this will take less time, since index key positioning 
requires more I/O. I/O is the killer in all this.

Regards

Vern

At 11:31 AM 2/10/03 +0100, you wrote:


>Hi,
>
> >- journaling (number of pending deleted records and multiples writes).
> >- Triggers.(number of calls and process in triggers programs)
> >- Numbers of logicals files.(update index)
> >- Referentials constraints.(multiples action processed).
>
>yeah, I guess the journal will have had a big impact.. We have an ASP2 for
>journalling,
>but it only has 2 6718 drives (mirrored).
>
>4 or 5 logicals shouldn't be too bad... No triggers or constraints,
>anyhow..
>
>I'll keep this in mind,
>
>Oliver

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.