Title: RE: Speed up Truncate tables - rebuild index
Actually Chris if you truncate a table, the indexes on the table are truncated right along with it. You don't end up with a sparse index, like when you delete many rows. Try it. I did (8.1.6) and was very happy to see this behavior.
Lisa
Title: RE: Speed up Truncate tables - rebuild index
Lisa,
I think Chris referred to 'alter table ... move',
in which case you need to rebuild indexes (ROWIDs
changed).
Igor Neyman, OCP DBAPerceptron, Inc.(734)414-4627[EMAIL PROTECTED]
- Original Message -
From:
Koivu, Lisa
Title: RE: Speed up Truncate tables - rebuild index
Ahhh... Thank you. Pre-coffee over here.
LK
-Original Message-
From: Igor Neyman [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, August 16, 2001 12:58 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Speed up Truncate tables
: RE: Speed up Truncate tables
Don't forget to rebuild your indexes...
-Original Message-
From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 15, 2001 4:35 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Speed up
Title: 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.
I
havent tested this, but I believe Lisa is right, in part, if the table is
truncated, then moved, then why
Title: RE: Speed up Truncate tables
Chuan,
Are you sure you were using the TRUNCATE TABLE command rather than DELETE
It sounds as if you are running a delete, especially if the HWM was not shrunk
John
-Original Message-
From: Chuan Zhang [mailto:[EMAIL PROTECTED]]
Sent: 15 August
Check the extent sizes... truncate can take a VERY long time if there
are a very large number of extents. At one point we had a large table
with approximately 60,000 extents (accidentally created with
INITIAL/NEXT 80k MAXEXTENTS UNLIMITED), which took about 2.5 hours to
truncate. After
: Hallas John [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Speed up Truncate tables
Date: Wed, 15 Aug 2001 01:25:59 -0800
Chuan,
Are you sure you were using the TRUNCATE TABLE command rather than DELETE
It sounds as if you
Title: RE: Speed up Truncate tables
Chuan,
You can use the 'REUSE STORAGE' clause of truncate table. That's a heck of a lot faster if you have a whole load of extents allocated to the table. What it does is mark the table as empty and keep all extents.
However, I have a feeling
I had the same problem when truncating a huge table (24 Mill rows). It
turned out that the reason my table was taking so long was the amount of
extents I had on it. I could look at what was actually happening during a
truncate and it had to go and take each individual block and put them back
A truncate (reuse storage) should not do that -
it simply drops the HWM to zero and updates the
segment header. However, when a truncate
is issued, and dirty blocks in the buffer from
that object have to be written to disk before
the truncate takes place, so that might be
slowing things (a
Chuan,
Kevin is correct. If your truncate table is taking a *long* time (and the
table is not locked by another process), it's because your storage params
are incorrect for the amount of data you are holding.
Look at initial and next in comparison with the number of extents
(DBA_EXTENTS view)
You are correct, unless your using 9i, you cannot alter the initial extent
without dropping the table.
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)
Thomas,
are your sure you can change the initial extent? My senior dba told me once
it is not possible; you have to drop and recreate table if there is a need
to change Initial extent.
I am going to play with it today.
-Original Message-
Sent: Wednesday, August 15, 2001 1:27 PM
To:
No, you cannot change the size of the initial extent.
SQL select initial_extent/1024, next_extent/1024, min_extents, max_extents
2 from sys.dba_segments
3 where segment_name='T1';
INITIAL_EXTENT/1024 NEXT_EXTENT/1024 MIN_EXTENTS MAX_EXTENTS
--- ---
If your going to remove all of the records in the table and desire to free up the
extents, wouldn't it be faster to drop the table and recreate it?
ROR mª¿ªm
[EMAIL PROTECTED] 08/15/01 10:56AM
Chuan,
You can use the 'REUSE STORAGE' clause of truncate table. That's a heck of
a lot faster
In our case Johathan, we did not add the reuse storage clause.
-Original Message-
Sent: Wednesday, August 15, 2001 12:41 PM
To: Multiple recipients of list ORACLE-L
A truncate (reuse storage) should not do that -
it simply drops the HWM to zero and updates the
segment header.
By golly, you're right. I could of swore that I used this in the past, and
it grew the existing extent to the size indicated. But it only allocates a
new extent.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, August 15, 2001 3:16 PM
To: Multiple
:
Subject:RE: Speed up Truncate tables
You are correct, unless your using 9i, you cannot alter the initial extent
without dropping the table.
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
THAT depends on what all the dependencies and such are. If you have a lot
of referential integrity constraints around the table then it is sometimes a
REAL pain to drop and recreate versus keeping the table and the constraints
intact.
-Original Message-
Sent: Wednesday, August 15, 2001
to ORACLE-L
|
|
|To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
|cc:
|Subject:RE: Speed up Truncate tables
|
|
|You are correct, unless your using 9i, you cannot alter the initial
extent
|without dropping the table.
|
|Do not criticize someone until
Cool! I just tried it - works like a
charm!
Tom Mercadante Oracle Certified Professional
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, August 15, 2001
4:02 PMTo: Multiple recipients of list ORACLE-LSubject:
RE: Speed up Truncate
Also,if the problem is caused by a large number of extents my understanding
is that it will take just as long to DROP it as to TRUNCATE it.
Jay Miller
-Original Message-
Sent: Wednesday, August 15, 2001 4:11 PM
To: Multiple recipients of list ORACLE-L
THAT depends on what all the
One option is to rename the table, create a new table structure with the old
name and better extent sizes (if that was the problem), repoint any
integrity constraints to the new table, then drop the renamed table. You
can let the drop command run for however long it takes while you work on the
24 matches
Mail list logo
