direct load

[fraze]

Hi.

Alot depends on how the DB is being used during this load. If you have got users on it then disabling the indexes is really going to p*ss them off.

Also, you skip unusable indexes at session level. What about the other sessions accessing the data?

You've not said what the source of the data is. Have you identified that it is the insert portion of the process that is definitely at fault, not the read.

I don't want to sound like I'm teaching your grandmother to suck eggs, but have you tried this out and measured the total time. I've worked on a few systems where the index rebuilds alone would take many hours. It may be that using your method the data would get in quicker, but you would be rebuilding indexes for many hours after...

Also, do you have any triggers on the tables? Can they be diabled and reenabled without affecting the integrity of the data? What happens if they are disabled and a user does somethig that would normally fire the trigger?

If possible I try to leave the schema alone during data loads. My approach is, if the system can't deal with the data loads using "normal" methods, I've probably got the system design wrong, but there are always exceptions ;-)

Cheers

Tim...

Thanks for the reply Tim,

We're planning to have the system "to ourselves" for this process so there should be no other sessions accessing the data (its a one off data conversion). I've yet to see the breakdown of timings of the last attempt (80 hrs!) but was just thinking of a high level process that we could benchmark against the current method. The data is initally loaded into staging tables then inserted into the application tables using direct insert.

Fraze