Re: count(*) very very slow with few records ?

"linux" <linux_at_anonymous.to> wrote:
> Hello,
>
> Any idea for that :
> count(*) very very slow with few records ?!
> The table contains one CLOB column and have frequently "insert/delete"
> operations
>

I assume this is Oracle8. Most likely the high water mark. In a dynamic table (lots of inserts/deletes), the table can get very fragmented and develop lots of extents, and a resultant high water mark way out beyond the last row. My typical query is something like:

select count(*), sum(bytes) from user_extents where segment_name = '<table>';

If you have a high count, I would set the 'next' storage clause higher. Also, you can issue the 'ALTER TABLE <table> DEALLOCATE UNUSED;',which I have found works if the rows are down low in the storage area of the table, otherwise it has no effect.

If the ALTER TABLE doesn't buy you anything, and you want to clear up your present condition:
1) export the table - don't work with clobs so I don't know if clobs will export. Some serious limitations with export (like size of the table). If not, you possibly can use the old-style 'copy' command to recreate the table with correct storage clauses (Anyone used copy with clobs?). Don't expect that the 'create table as select...' will work. If you export, then

   2) truncate the table drop storage;
   3) import the values back in (IGNORE=Y)

HTH, Dwight

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue May 09 2000 - 00:00:00 CDT