Re: Performance decrease while deletin a table

Kevin A Lewis <KevinALewis_at_Hotmail.com> wrote in message news:rbWV2.3230$Ed5.167_at_news-reader.bt.net...
> Not sure quiet why such an operation should be needed, however --
>

I need replace whole data in the table. New data is taken from external file. If that file contains bad records - I need to return to old data.

> Is the select on an index ?

Yes.

> Does the explain plan suggest it uses that index?

Yes.

> IF not does the table in the EMPTY state have a high, High Water Mark? or
is
> it truncated giving a low High Water Mark and allowing a full table scan
to
> complete quickly.
>
> In the case of the IOs check the file IO positions before and after the
> query and see from the differences which files are being hit during the
> query.
>
> Some questions and some ideas for you.
>
> Regards
>
> --
> Kevin A Lewis (BOCM PAULS LTD - Animal Feed Manufacturer - Ipswich
England)
> <Kevin.Lewis_at_BOCMPAULS.co.uk>
>
> The views expressed herein by the author of this document
> are not necessarily those of BOCM PAULS Ltd.
> Grzegorz Gizinski <grzesiek_at_no.spam> wrote in message
> news:7g97ls$m28$1_at_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 - 06:03:43 CDT