asked on

finding duplicate rows using SQL loader (Bulk Load)

I want to load records into table from a file.
I want to find out the duplicate records in a table where in i have defined primary key.
now while loading the data into table it will reject duplicate records because of the constraint and
put all those duplicate recrods in .dsc file...

but if i use BULK Load option in SQL*Loader.. as i understand it will first disable table constraints and then load data..
because of this i cannot find out the duplcate rows...

i have tried out that using BULK load option performance is more than 60% higher than conventional loading method using SQL*Loader ..

now any body can please help me out finding duplicate rows with same performance what i am getting with bulk load??

it is very urgent..
thanks in advance

andrewst

By "BULK load" do you mean direct path load?

If so, UNIQUE and PRIMARY KEY constraints are NOT disabled during direct path load, only CHECK and FOREIGN KEY constraints are disabled - and they will be automatically re-enabled afterwards if you use the REENABLE clause.

So you can use direct path load and still find duplicates.

ASKER CERTIFIED SOLUTION

i014354

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Geerlings

Yes, the "direct path" load is much faster than a conventional data load (which does an insert for each row or set of rows) but there are some limitations with the "direct path" load. You may have to decide what is more important to you:
row-by-row processing
or
speed of the load.

Nags

ASKER

thanks for the solutions. i am worried about the speed of the load so i use "direct path".

i still have some problem..
ie. in the input file two fields are null and in the table
those fields are number not null ..

now whiell loading i want to translate null to 0..
for this i can use DECODE in control file.. but with "direct path". this will not be allowed..
how will i do this?????

SOLUTION

andrewst

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anand_2000v

No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Split: i014354 {http:#8121042} & andrewst {http:#8126624} & markgeer {http:#8136682}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

anand_2000v
EE Cleanup Volunteer