Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: how many deleted records has a table

Re: how many deleted records has a table

From: mcstock <mcstock_at_enquery.com>
Date: Thu, 30 Oct 2003 10:36:15 -0500
Message-ID: <kqqdnT5PWr-JrTyiRVn-gQ@comcast.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US