Yes,
It has been known if you delete all records, use a truncate and not delete.
Otherwise it will still behave like it is full as it has to touch all the
extents.
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-----Original Message-----
Sent: Thursday, August 16, 2001 5:33 PM
To: Multiple recipients of list ORACLE-L
I have a user that deleted all of the rows in a table
(i.e. 100,000), waited for it to complete, and then
ran a SELECT COUNT(1) FROM <table>. It took a few
minutes for '0 rows' to be returned to the prompt. The
table has ~60 extents (128k ea.). Granted, the number
of extents is excessive but it's a development
instance and this table is an exception.
Is Oracle scanning through all of the blocks, since
the space wasn't released, and this is the cause of
the latency?
The curious thing is that I told this user to use
TRUNCATE instead and we talked about using the
drop/reuse storage clauses. He performed a
TRUNCATE...REUSE STORAGE and the same select and it
was night and day in terms of performance. If the
allocated space isn't being released in this case
also, why is there such a performance difference
between the two?
-w
__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Walter K
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
