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

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Wed, 21 Jul 1999 21:44:54 +0800
Message-ID: <3795CED6.1BFB@yahoo.com>

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:

1. you insert 1,000,000 into a table. Oracle sets the high water mark of the table to (say) 200,000 blocks.
2. you delete all the records from the table using 'delete'. (Oracle does NOT reset the high water mark)
3. 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 - 08:44:54 CDT