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

David

Occasional deletes are not worth worrying about. Significant delete activity is. The approaches you can take to reset the HWM are:

1. CTAS, followed by the truncate as you say, but rather than rename I'd suggest doing insert as select into the original. Reason for this is because the rename will need to be preceded by a drop original table command, and you thus lose all the indexes etc.
2. Export, drop table, import - may be worthwhile precreating the table with different storage before the import. Alternatively, truncate the table then do the import with ignore=y.
3. Spool to a file, truncate, SQL*Load back in. Can be done in direct mode and parallel for performance.

HTH. Pete

David Spaisman wrote:

> 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."

--
Regards

Pete

Received on Thu Jul 22 1999 - 11:15:21 CDT