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
5:20 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Speed up Truncate tables
>
> Don't forget to rebuild your indexes...
>
> -Original Message-
> From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]]
> Sent: We
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
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
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 beh
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
ne
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 depen
Don't
forget to rebuild your indexes...
-Original Message-From: Mercadante, Thomas F
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, August 15, 2001
4:35 PMTo: Multiple recipients of list ORACLE-LSubject:
RE: Speed up Truncate tables
Cool! I just tried it - works l
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 tab
]>
|Sent by: [EMAIL PROTECTED]
|08/15/01 02:22 PM
|Please respond 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 th
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 2
ecipients 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 you walked a mile in their sh
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 recip
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. However
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 fas
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
--- ---
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: Multi
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) 32
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) f
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 little
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
in
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
>From: 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
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 recre
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
25 matches
Mail list logo