| restrict duplicate data through sql*loader [message #144909] |
Fri, 28 October 2005 14:51  |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Hi,
I wants to load the data from file and it contains only emails. Now table A has email as pk but table B has it's own id column which is generated by sequence and also email is FK column ref to Table A. Now I wants to load the data in Table A and Table B. but in Table B I have to add div, site and I have to check the combination of email, div, site and if it's exist then I don't want to load that records.
Table A
======
email varchar2(100) PK,
date date
Table B
=======
id varchar2(20), -- need to generate PK
email varchar2(100) FK ref to table A -- loading from file
div varchar2(2), -- need to put constant like divA
site varchar2(20) -- need to put constant like siteA
Now I need to check while loading data from file into Table B that records won't exist for (email, div, site)
So if records like email1, div1, site1 exist then I don't want to load because already there and id is the PK i ntable B so it will laod the new record as email, div and site is not composite pk.
next time if records like email2, div1, site1 then it will load.
Thanks
|
|
|
|
| Re: restrict duplicate data through sql*loader [message #144910 is a reply to message #144909] |
Fri, 28 October 2005 15:24  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
To avoid all the painful options we will be looking into,
load the data as-is into a set of staging tables and use sql methods to enforce your business.
Getting the external data into oracle table is what you are concerned here.
Please Look here. See whether it works.
http://www.orafaq.com/forum/t/25692/0/
If it is just one table with one Primary key, it may be done as above ( i used truncate, try append. Will it work? i have no idea. Will create a test case and see).
For a composite key column in another table, you can write an before insert trigger or something like that ( i have never tried them. I prefer to load data as-is and use sql methods).
If you want to load master-detail tables with a sequence, use rows=1 ( which ofcourse, will be slow).
[Updated on: Fri, 28 October 2005 15:27] Report message to a moderator |
|
|
|
| Re: restrict duplicate data through sql*loader [message #145020 is a reply to message #144910] |
Mon, 31 October 2005 06:16 |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Thanks much. So there is no direct sql*loader method that I can take care inside even if it's not a real composite key into table. I checked your link but it's load into exception table and I think here i am loking the records to load which in a child table based on a composite key ( eventhough it's not defined in a database).Because I don't want to generate 150K insert and run in a very big table and this table frequently used table. I thought there is a way I can handle it as I wants to use sql*loader or another fast method to load 150k records in a big table instead of running 150k inserts.Because my table data is like...
id1 email1 div1 site1
id2 email1 div1 site2
id3 email1 div1 dite3
id4 email1 div2 site1
now if i have to load the another records like
id5 email1 div1 site1 then it will be a kind of duplicate record.
because in my table this records will be there(except pk id)
|
|
|
|
| Re: restrict duplicate data through sql*loader [message #146585 is a reply to message #145020] |
Fri, 11 November 2005 05:02 |
ashuj20
Messages: 12
Registered: October 2005
Location: new delhi
|
Junior Member |
|
|
hi,
i guess there is no such facility available with SQL* Loader.
why don't you add a composite unique constraints to your table and then load the data into it with sql*loader.
try this
alter table table1 add constraints table1_unq unique(id,email,div,site);
then load your data
after that drop the constraint.
alter table tabl1 drop constraints table1_unq;
i think this will help you.
Ashish
|
|
|
|
| Re: restrict duplicate data through sql*loader [message #146725 is a reply to message #146585] |
Sun, 13 November 2005 02:22 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Be warned, if you use a constraint to "filter" duplicates in SQL*Loader, be prepared for a BIG drop in performance.
The reason (and this is just me guessing - I haven't had this confirmed) is because SQL*Loader performs array-inserts. ie. It fills up a buffer of rows and then inserts the lot in a single transaction. If one of them fails, then the lot get rejected. SQL*Loader then has to retry inserting the rows one at a time so that it can get the "good" transactions through and reject the duplicate. So you end up having to do everything twice, and still only get the same performance as individual INSERT-INTO statements in SQL*Plus.
Alternatively, if you use DIRECT PATH load, then the duplicates will still load, but your unique key will be disabled afterwards and you wont be able to enable it until you manually delete the offending rows.
_____________
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
