Hi, mike, the answers :

> which is the purpose of your PK?
>OR
>Why do you need a PK if you check during the data load that your new records are unique?

well, the text files will be user´s data to be inserted directly in the data table (more specifically, phone call records) - 99.9% of the times the files will be OK, but WILL be some (** rare ** ) cases of dups. The idea here is : in this situation, I think that WILL be more performant to try the INSERT right on first (the PK will get the dups), rather than , for each one of the millions of lines, to try a SELECT againts the db table to check the data read, that´s it.

>>Before using an IOT I would realy test if there is any benefit of using an IOT. Personnaly I think that it is usefull only if you access a few rows in a query. So my question is what kind of queries are going to be executed against this table?

Once loaded, the queries against the db table will access tipically between 10% and 20% of the table - and the table will be partitioned (IOT or heap, don´t matter). The point here is : the table will be in the order of dozens of millions, so 10% of this huge volume WILL be of some concern, I think. I must test , test and test, this is teh only way to be sure about the exact degrade due to IOTs, afaik. What you think ?


>> In case all your columns are not null you may do somthing like:
INSERT ALL
WHEN t.date_column is null THEN
INTO target
WHEN t.date_column is not null THEN
INTO duplicates
SELECT s.*
FROM source s,
(
select * from target
where date_column >= (select min(date_column) from source)
) t
where s.date_column = t.date_column(+)
and s.col_2=t.col_3(+)
and s.col_3=t.col_3(+)
and s.col_4=t.col_4(+)
and s.col_5=t.col_5(+)
.....


hmm, I know (in PL/SQL) :

FORALL nnn
insert into table EXCEPTION INTO ..

not this "INSERT ALL" syntax - is it new with oracle 9 ? Is it in plain SQL or PL/SQL ?

regards.

Chiappa