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: hypothetical question:select count(*) hung?

Re: hypothetical question:select count(*) hung?

From: David Spaisman <davedba_at_intercall.net>
Date: Wed, 21 Jul 1999 20:55:27 -0700
Message-ID: <3796962F.21A0C80E@intercall.net>


Connor:

Thanks for the reply.

If the table is not empty, and there is significant delete activity over time or occassional deeltes, how do eset the HWM? create table ..select as , Truncate again and then rename?

How do I tell when the HWM is significantly high as opposed to actual space used in the table and theefore a drop/recreate is necessary?

David Spaisman
Connor McDonald wrote:

> David Spaisman wrote:
> >
> > Someone recently proposed a hypothetical question to me about the following
> > situation and I thought it was interesting.
> >
> > If there is an empty table in a database and a select count(*) is tried
> > aganst an empty table.
> > The connection hangs and no reply is returned. What could be the cause?
> >
> > Any infomration on how to resolve such a situation would be greatly
> > appreciated.
> >
> > Thanks.
> >
> > David Spaisman
>
> If the table once had lots of rows, this could cause it...
>
> Scenario:
>
> a. you insert 1,000,000 into a table. Oracle sets the high water mark of
> the table to (say) 200,000 blocks.
> b. you delete all the records from the table using 'delete'. (Oracle
> does NOT reset the high water mark)
> c. you so "select count(*)" - oracle reads up to the high water mark (ie
> 200,000 blocks) and thus takes a long time...
>
> solution: truncate table table_name;
>
> HTH
>
> --
> ===========================================
> Connor McDonald
> "These views mine, no-one elses etc etc"
> connor_mcdonald_at_yahoo.com
>
> "Some days you're the pigeon, and some days you're the statue."
Received on Wed Jul 21 1999 - 22:55:27 CDT

Original text of this message

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