| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Speed up Truncate tables - rebuild index
Yes,
the point of my message was simply a quick reminder to rebuild the indexes on a
table that has been ALTER TABLE...MOVE moved.
<FONT face=Arial color=#0000ff
size=2>
I
havent tested this, but I believe Lisa is right, in part, if the table is
truncated, then moved, then why bother rebuilding the indexes? The table
is empty. There are no ROWIDS in the index to worry
about.
<FONT face=Arial color=#0000ff
size=2>
So
sorry, I wasnt following the thread, so again it was just a quick reply...glad
it's Friday....errr....never mind....where did I hide that darn
bottle?
<FONT face=Tahoma
size=2>-----Original Message-----From: Koivu, Lisa
[mailto:lisa.koivu_at_efairfield.com]Sent: Thursday, August 16, 2001
1:21 PMTo: Multiple recipients of list ORACLE-LSubject:
RE: Speed up Truncate tables - rebuild index
Ahhh... Thank you. Pre-coffee
over here.
LK
-----Original Message----- <FONT
face=Arial size=1>From: Igor
Neyman [SMTP:ineyman_at_perceptron.com] <FONT face=Arial
size=1>Sent: Thursday, August
16, 2001 12:58 PM <FONT face=Arial
size=1>To: <FONT face=Arial
size=1>Multiple recipients of list ORACLE-L <FONT face=Arial
size=1>Subject: <FONT
face=Arial size=1>Re: Speed up Truncate tables - rebuild index
Lisa,
-----Original
Message-----From:<FONT
face=Tahoma size=2> Mercadante, Thomas F [ <FONT
face=Tahoma color=#0000ff size=2><<A
href="mailto:NDATFM_at_labor.state.ny.us">mailto:NDATFM_at_labor.state.ny.us><FONT
face=Tahoma size=2>]<FONT face=Tahoma
size=2>Sent: Wednesday, August 15,
2001 4:35 PMTo:<FONT
face=Tahoma size=2> Multiple recipients of list
ORACLE-LSubject:<FONT
face=Tahoma size=2> RE: Speed up Truncate tables<FONT
face=Arial color=#0000ff size=2>Cool! I just tried it - works like
a charm!
Tom Mercadante<FONT face=Arial
size=2>Oracle Certified Professional
-----Original
Message-----<FONT face=Tahoma
size=2>From:
Riyaj_Shamsudeen_at_i2.com [ <FONT face=Tahoma
color=#0000ff size=2><<A
href="mailto:Riyaj_Shamsudeen_at_i2.com">mailto:Riyaj_Shamsudeen_at_i2.com><FONT
face=Tahoma size=2>]<FONT face=Tahoma
size=2>Sent: Wednesday, August 15,
2001 4:02 PMTo:<FONT
face=Tahoma size=2> Multiple recipients of list
ORACLE-L<FONT face=Tahoma
size=2>Subject: RE: Speed up
Truncate tables
Not true, at least in 8i. Using 'alter
table move ' you could change the initial extent
size..SQL>alter table
test move storage (initial 2M);<FONT face=sans-serif
size=2>Table altered.Only
catch here is that 'move' command doesn't apply to tables having long
,long raw, LOB etc..Initial extent can be changed for the remaining
tables..ThanksRiyaj
"Re-yas" ShamsudeenCertified Oracle DBAi2 technologies
www.i2.com
<FONT
face=Arial> <FONT
face=Arial> Christopher Spence
<cspence_at_FuelSpot.com><FONT face=sans-serif
size=1>Sent by: root_at_fatcity.com
08/15/01 02:22 PM<FONT
face=sans-serif size=1>Please respond to ORACLE-L
<FONT
face=sans-serif size=1> To:
Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
cc: <FONT
face=sans-serif size=1> Subject:
RE: Speed up Truncate tables<FONT
face=Arial>
You are correct, unless your using
9i, you cannot alter the initial extentwithout dropping the
table."Do not criticize someone until you walked a mile in
their shoes, that waywhen you criticize them, you are a mile a way
and have their shoes."Christopher R. SpenceOracle
DBAPhone: (978) 322-5744Fax: (707)
885-2275Fuelspot73 Princeton StreetNorth, Chelmsford
01863-----Original Message-----Sent:
Wednesday, August 15, 2001 3:07 PMTo: Multiple recipients of list
ORACLE-LThomas,are your sure you can change the
initial extent? My senior dba told me onceit is not
possible; you have to drop and recreate table if there is a needto
change Initial extent. I am going to play with it
today.-----Original Message-----Sent: Wednesday, August
15, 2001 1:27 PMTo: Multiple recipients of list
ORACLE-LChuan,Kevin is correct. If your
truncate table is taking a *long* time (and thetable is not locked
by another process), it's because your storage paramsare incorrect
for the amount of data you are holding.Look at initial and
next in comparison with the number of extents(DBA_EXTENTS view)
for the table in question, and modify them before youload the
data. You can modify the INITIAL extent by issuing an 'alter
tableallocate extent(size x)' command to grow the INITIAL extent.
You can also modify the NEXT extent by issuing an 'alter
table storage (nextx)' command to change the NEXT
extent.hope this helps.Tom MercadanteOracle
Certified Professional-----Original Message-----Sent:
Wednesday, August 15, 2001 11:02 AMTo: Multiple recipients of list
ORACLE-LI had the same problem when truncating a huge
table (24 Mill rows). Itturned out that the reason my table
was taking so long was the amount ofextents I had on it. I
could look at what was actually happening during atruncate and it
had to go and take each individual block and put them backin the
available lists. Well, after changing the settings on
the table to make larger extents (andtherefore fewer) the
truncates on that table went hundreds of times faster(we had real
bad settings on that table before).You might investigate your
storage parms and see just how many extents youdo have on that
table.-----Original Message-----Sent: Wednesday, August
15, 2001 3:10 AMTo: Multiple recipients of list
ORACLE-LHi All, Is there any way to
speed up the truncating a big table with 12
millionrows?Basically, I implemented truncating that big
table on Production, but itaffected the performance much, so I had
to stop it in the middle of way. Allthe rows were truncated but
the HWM was not shrunk at all. I want to do itagain to get the
space back. Is there any way to speed up this
process?Platform: Oracle EE8.0.6 and Solaris 2.7Thanks
a lot in advance.Chuan--Please see the official
ORACLE-L FAQ: <FONT face="Courier New" color=#0000ff
size=2><<A target=_blank
href="http://www.orafaq.com">http://www.orafaq.com><FONT
face="Courier New" size=2>--Author: Chuan Zhang INET:
chuan_at_asiaonline.netFat 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 in
themessage BODY, include a line containing: UNSUB ORACLE-L (or the
name ofmailing list you want to be removed from). You may
also send the HELPcommand for other information (like
subscribing).--Please see the official ORACLE-L FAQ:
<<A target=_blank
href="http://www.orafaq.com">http://www.orafaq.com><FONT
face="Courier New" size=2>--Author: Kevin Lange INET:
kgel_at_ppoone.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 in
themessage BODY, include a line containing: UNSUB ORACLE-L (or the
name ofmailing list you want to be removed from). You may
also send the HELPcommand for other information (like
subscribing).--Please see the official ORACLE-L FAQ:
<<A target=_blank
href="http://www.orafaq.com">http://www.orafaq.com><FONT
face="Courier New" size=2>--Author: Mercadante, Thomas
F INET: NDATFM_at_labor.state.ny.usFat 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 in
themessage BODY, include a line containing: UNSUB ORACLE-L (or the
name ofmailing list you want to be removed from). You may
also send the HELPcommand for other information (like
subscribing).--Please see the official ORACLE-L FAQ:
<<A target=_blank
href="http://www.orafaq.com">http://www.orafaq.com><FONT
face="Courier New" size=2>--<FONT face="Courier New"
size=2>Author: INET: lhoska_at_calibresys.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 in
themessage BODY, include a line containing: UNSUB ORACLE-L (or the
name ofmailing list you want to be removed from). You may
also send the HELPcommand for other information (like
subscribing).--Please see the official ORACLE-L FAQ:
<<A target=_blank
href="http://www.orafaq.com">http://www.orafaq.com><FONT
face="Courier New" size=2>--Author: Christopher
Spence INET: cspence_at_FuelSpot.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 - 13:04:20 CDT
 |
 |