perf. prob during load from sqlldr

I have a table which is partitioned by month. We keep last few months of records and delete others (drop partition...)

We are using sqlldr to load the data into table.

Intially this table had global index due to the perf. problem we changed it to local index.

Now during the load I want to disable or drop the local index for one of the partition which is going to use during the load so it can improve the perf during the load.

Any suggestion?

Thanks

do you use direct load? direct=y

Pando:

I tried with direct=true option and it hangs. Do I need to set up anything else?

Let me know.

Thanks.

has the table got special datatypes such as LOBs?

No special data type.

Any suggestion?

Thanks.

well I dont know why it hangs then, that has never happened to me. you sure your control file and parameters are correct

I would try dropping the index before the load and recreate it after the load. Then set the direct=true.

Sam

Controlfile and other parameters are set correctly. We ran before without adding DIRECT=TRUE and worked fine.

also, instead of dropping the index, if I disable the index, load the data with direct=true enable/rebuild the index, will it work?

Thanks.

Hi:

I was able to load the data with the direct=true and Performance was improved.

During the load direct=true, disabled all the indexes including PK index.

That allowed to insert the multiple rows with the same PK.

Now, problem with enabling the PK and Data integrity.

Now, I am thinking to disable all the indexex accept PK index.

I would like to know that how can I diable the locally partitioned index or any other suggestion.

Thank.

Hi:

Diff. between DISABLE and UNUSABLE?
Instead of using disable, can I use UNUSABLE?

Thanks.

what does the log file say ? did u check them for any errors ? I would also have a look at the parallel load.

Cheers

Disabling partitions of indexes using:

alter index .. modify partition ... unusable;

is a technique I've used reasonably successfully when loading partitions of large tables. You have to issue the command "alter session set skip_unusable_indexes=true" before you can insert into the table. I believe this can also be done from sqlloader - there is a control file parameter SKIP_UNUSABLE_INDEXES.

The only problem is that you load into a table which has had a primary or unique key index disabled. It only works for ordinary indexes.

If you're loading a partition from scratch, the better approach is generally to load a separate table with the same structure as the partitioned table and then swap the partition with the table.

[Edited by nealh on 09-26-2001 at 04:47 AM]