Rebuild table - drop index part before and after drop. why?

Toad for Oracle
1

Hi,

Toad 12.x.x. x64 (latest official) and 12.12.0.3 x64 (latest beta).

When you create rebuild table that has UNIQUE index and some non unique indexes, before inserting data in destination table, you drop PK and UQ on source starting table).

And later you drop all other non-unique indexes. I mean why not to drop all indexes before insert because it is FTS in 100% in case. I am aware of hint in select …but still do not understand why is that drop in two parts.

So question is:
Why don’t you drop all the indexes before you do insert?

Brg
Damir

2

We drop the PK constraints at the beginning incase they are needed for the creation of the new table (such as IOT, where PK must be created with the table). I don’t remember offhand why we do that with Uniques also but I think there was a similar reason.

We don’t drop the rest of the indexes until later because if there is some problem copying all the data, and the user wants to revert to the original table, it will be less work.

3

Hi John

In mine best practice rebuild script has two parts.

First part ends with create table commands

Second part start with "insert into".

Why?

if first part failed it can be repeated as many times you want with no error and the best of all non dropped index is preserving some space, which may be reused in a moments when space is on the edge.

So this is why I would suggest to drop all indexes at the beginning. I find it reasonable.

Please introduce at least an option for this way.

if there is some problem copying all the data, and the user wants to revert to the original table, it will be less work.

I do not find very likely that any serious user will not test the script before run in production env.

Brg,

Damir

Brg,**
________________________**

Damir Vadas
http://damir-vadas.blogspot.com

It does not matter what you have in your life, but who!

Razmislite o čuvanju okoliša prije nego odštampate ovaj e-mail
Please consider the environment before printing this email

On Mon, Aug 21, 2017 at 5:00 PM, John Dorlon bounce-jdorlon@toadworld.com wrote:

RE: Rebuild table - drop index part before and after drop. why?

Reply by John Dorlon
We drop the PK constraints at the beginning incase they are needed for the creation of the new table (such as IOT, where PK must be created with the table). I don't remember offhand why we do that with Uniques also but I think there was a similar reason.

We don't drop the rest of the indexes until later because if there is some problem copying all the data, and the user wants to revert to the original table, it will be less work.

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle Forum notifications altogether.
Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.