Re: Select are slow after delete command

A copy of this was sent to Doug Cowles <dcowles_at_bigfoot.com> (if that email address didn't require changing) On Sat, 07 Aug 1999 17:13:22 -0400, you wrote:

>Am I way off here or if you change your deletes to
>be followed up with a alter table XX deallocate unused - will that help?
>

no, that will only release extents that have been allocated to the table but never contained data (extents above the high water mark). Once an extent has had data in it, the only way to "free" that extent (release it from the table) is to truncate/drop the table.

>- Dc
>
>G R McHugh wrote:
>
>> Deleting the rows in a table does not actually reset the high water mark. So
>> when you are performing a full table scan, for example, the select statement
>> still needs to look at all of the blocks allocated to the table. There are
>> three ways to deal with this problem (I can think of):
>>
>> (1) truncate the table resets high water mark (and loses all records)
>>
>> (2) export the table with the deleted records
>> drop the table
>> import the previously exported table
>>
>> (3) create table temp
>> as select * from xyz
>> where <deletion_criteria>;
>> drop table xyz;
>> rename table temp to xyz;
>>
>> Hope that helps...
>>
>> Gabriel McHugh
>> DBA Consultant
>> gabe_riel_at_yahoo.com
>>
>> Břetislav Černík wrote in message <7ns7kt$ugi$1_at_ns.felk.cvut.cz>...
>> >Hello
>> >I found the folowing trouble.
>> >When I delete many rows in table, the select works as slow as there are all
>> >rows.
>> >Are there any way, how can I clean table without drop table?
>> >
>> >Bretislav Cernik
>> >xcernikb_at_hwlab.felk.cvut.cz
>> >
>> >
>
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Aug 08 1999 - 09:22:38 CDT