HWM, oracle will search up to the high water mark when doing a count, like
you proposed, so if you're gonna delete all of the rows anyways, save time(now
and later) by doing the truncate.
joe
>>> alden14004_at_yahoo.com 08/16/01 05:32PM
>>>I have a user that deleted all of the rows in a table(i.e.
100,000), waited for it to complete, and thenran a SELECT COUNT(1) FROM
<table>. It took a fewminutes for '0 rows' to be returned to the
prompt. Thetable has ~60 extents (128k ea.). Granted, the numberof
extents is excessive but it's a developmentinstance and this table is an
exception.Is Oracle scanning through all of the blocks, sincethe
space wasn't released, and this is the cause ofthe latency?The
curious thing is that I told this user to useTRUNCATE instead and we talked
about using thedrop/reuse storage clauses. He performed
aTRUNCATE...REUSE STORAGE and the same select and itwas night and day in
terms of performance. If theallocated space isn't being released in this
casealso, why is there such a performance differencebetween the
two?-w__________________________________________________Do
You Yahoo!?Make international calls for as low as $.04/minute with Yahoo!
Messenger<A
href="
http://phonecard.yahoo.com/">
http://phonecard.yahoo.com/--
Please see the official ORACLE-L FAQ: <A
href="
http://www.orafaq.com">
http://www.orafaq.com-- Author: Walter
K INET: alden14004_at_yahoo.comFat City Network
Services -- (858) 538-5051 FAX: (858) 538-5051San
Diego, California -- Public Internet
access / Mailing
Lists--------------------------------------------------------------------To
REMOVE yourself from this mailing list, send an E-Mail messageto:
ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list
you want to be removed from). You mayalso send the HELP command for
other information (like subscribing).
Received on Thu Aug 16 2001 - 16:15:34 CDT
