Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: how many deleted records has a table
if you are deleting in sql*plus, make sure you have FEEDBACK set and you'll
get a message of the number or rows deleted
if you're doing this in PL/SQL, use the SQL%ROWCOUNT or mycursor%ROWCOUNT to check the number or rows deleted
similar functionality exists in other APIs
(Niall is correct that HIGH WATER MARK is the issue -- the count is likely scanning all blocks that have ever had rows in them)
"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
news:3fa0e126$0$9479$ed9e5944_at_reading.news.pipex.net...
> If you have a look at either the TRUNCATE table command or the phrase
'HIGH
> WATER MARK' in the documentation you will find the explanation for your
> performance issues here. I'd hope that atosorigin had sufficient guys in
> house who you could ask about this sort of thing.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission Uk
> "Ronald" <ronald.goeggel_at_atosorigin.com> wrote in message
> news:61b71612.0310300138.43acddf7_at_posting.google.com...
> > I had a table of 15 million records.
> >
> > After deleting all records (using the delete command) I have to wait
> > about 1 minute for a "select count(*)". The result was that it
> > contained 0 records.
> >
> > After recreating the table the problem was solved.
> >
> > Therefore I want to check on each table how many deleted records they
> > involve.
> >
> > I am using ORACLE 8.1.6.
> >
> > Ronald
>
>
Received on Thu Oct 30 2003 - 09:36:15 CST