Re: SQL Loader Direct Path & Constraints

CJ <djjr_at_ix.netcom.com> writes:

> I have a 7.3.3 Database and I'm trying to do a direct path load on a
> table with a primary key. I know that there is data in my file that
> violates the key. What appears to be happening is that the data
> violating the primary key is being loaded and that the key is not
> being enforced even though its status is enable in dba_constraints.
> The error that I get from SQL Loader is
> Index index_name was left in Direct Load State due to
> ORA-01452: cannot CREATE UNIQUE INDEX duplicate keys found
>
> I'd like to see those records that violate the primary key rejected
> and the primary key enabled and enforced.
>
> Anyone run into this before?
>
> TIA
Are you sure that you have a primary or unique 'constraint' enabled, rather than just a unique index. If it is a 'constraint' then you can stop reading because I can't help.. :)

As far as I understand it the reason the direct path load is so much faster than a conventional load is that it creates formatted data blocks and writes directly to disk. During a direct load, any indexes that exist on the table are set to the 'DIRECT PATH' state and not checked. During the load, a new index for the new records is maintained with no reference being made to the original index. After the load is complete the original index and the new index are merged and Oracle attempts to re-enable them. Obviously a unique index can only be re-enabled if no duplicates exist.

Constraints, however, are checked during a load. So if you have any NOT NULL, UNIQUE or PRIMARY KEY constraints on the table, any record that violates the key should be placed in the BAD file.

Regards Received on Thu Jun 10 1999 - 11:02:39 CDT