1

So I need to do multiple bulk inserts into a table with row level triggers. I thought it would be a good idea to gather the generated ids first, combine them with my data and then do a direct=true sql load. Normally this would work fine but the table is partitioned by reference so it cannot disable the foreign key constraint that would allow me to do the direct load.

Does anyone know of anyway around this? My first solution of bulk collecting into a varray and inserting every 100,000 went moderately fast but if I was able to do a direct load, that would be much faster.

ERROR: SQL*Loader-965: Error -1 disabling constraint client_fk on table my_table

HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
keithcurtin20
  • 11
  • 3

1 Answers1

0

The manual implies there is no way to have SQL*Loader use a direct path load but not disable the foreign keys.

But direct-path inserts can work on reference partitioned tables, even with the foreign keys enabled, as I demonstrated in this question and answer.

Convert the process from SQL*Loader to an external table INSERT statement. SQL*Loader and external tables use similar mechanisms so the conversion shouldn't be too difficult. External tables require a little more work - you have to write the INSERT with an append hint, and manually disable and re-enable triggers and perhaps other objects. But that extra control allows loading data quickly with direct-path inserts.

Community
  • 1
  • 1
Jon Heller
  • 34,999
  • 6
  • 74
  • 132